In this example, we’ll see how to pass the list of object into SQL IN clause and also how to save the records effectively using NamedParameterJdbcTemplate.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>spring-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-jdbc</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>Person.java — It’s a model class.
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Person {
private Long id;
private String firstName;
private String lastName;
private String phone;
private String email;
private String address;
}PersonDao.java
import com.example.springjdbc.model.Person;
import java.util.List;
public interface PersonDao {
List<Person> findByEmailIn(List<String> emails);
void savePersons(List<Person> people);
}PersonDaoImpl.java
import com.example.springjdbc.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Service
public class PersonDaoImpl implements PersonDao {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
private DataSource dataSource;
private Person toPerson(ResultSet resultSet) throws SQLException {
Person person = new Person();
person.setId(resultSet.getLong("id"));
person.setFirstName(resultSet.getString("first_name"));
person.setLastName(resultSet.getString("last_name"));
person.setPhone(resultSet.getString("phone"));
person.setEmail(resultSet.getString("email"));
person.setAddress(resultSet.getString("address"));
return person;
}
@Override
public List<Person> findByEmailIn(List<String> emails) {
String sql = "select * from person where email in (:emails)";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("emails", emails);
return namedParameterJdbcTemplate.query(sql, parameters, new RowMapper<Person>() {
@Override
public Person mapRow(ResultSet resultSet, int i) throws SQLException {
return toPerson(resultSet);
}
});
}
@Override
public void savePersons(List<Person> people) {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
.withTableName("person")
.usingGeneratedKeyColumns("id");
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(people.toArray());
int[] ints = simpleJdbcInsert.executeBatch(batch);
System.out.println("No. of records inserted are : " + ints.length);
}
}MainApp.java
import com.example.springjdbc.dao.PersonDao;
import com.example.springjdbc.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class SpringJdbcApplication implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(SpringJdbcApplication.class, args);
}
@Autowired
private PersonDao personDao;
@Override
public void run(String... args) throws Exception {
System.out.println("------- Find By Emails -------");
List<String> emails = Arrays.asList("ThomasJMaggard@rhyta.com",
"AllanCGlass@teleworm.us",
"KatherineRBenefield@armyspy.com",
"RachalRWood@jourrapide.com");
List<Person> persons = personDao.findByEmailIn(emails);
System.out.println("Persons Details = " + persons);
System.out.println("----- Save All Persons ----");
List<Person> personList = Arrays.asList(
Person.builder().firstName("Matt").lastName("Wixson").email("matt.wixson@gmail.com").phone("111-222-3333").address("Achalpur").build(),
Person.builder().firstName("Shruti").lastName("Nandanwar").email("Shruti.nandanwar@gmail.com").phone("222-444-3333").address("Achalpur").build(),
Person.builder().firstName("Gayatri").lastName("Dekate").email("gayatri.dekate@gmail.com").phone("888-777-3333").address("Achalpur").build()
);
personDao.savePersons(personList);
}
}insert.sql
CREATE TABLE `person` (
`id` bigint NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;insert-data.sql
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(1, 'Thomas', 'Maggard', '910-984-4490', 'ThomasJMaggard@rhyta.com', 'Orange Circle');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(2, 'Allan', 'Glass', '701-886-8262', 'AllanCGlass@teleworm.us', '177 Findley Avenue Neche, ND 58265');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(3, 'Katherine', 'Benefield', '323-589-9055', 'KatherineRBenefield@armyspy.com
', '2905 Evergreen Lane Huntington Park, CA 90255
');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(4, 'Rachal', 'Wood', '732-993-1707', 'RachalRWood@jourrapide.com', '4823 Mahlon Street New Brunswick, NJ 08901');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(5, 'Ben', 'Ramos', '781-216-9080', 'BenMRamos@jourrapide.com', '410 Wescam Court Boston, MA 02110');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(6, 'Neha', 'Parate', '111-345-0980', 'neha.parate@gmail.com', '1149 George Avenue Evergreen, AL 36401');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(7, 'Parag', 'Rane', '309-690-7156', 'parag.rane@gmail.com', '2241 Garfield Road Peoria, IL 61614');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(8, 'Aravind', 'Ashtikar', '404-836-0110', 'aravind.ashtikar@springone.com', '50 Stroop Hill Road Atlanta, GA 30303');application.yml
spring:
datasource:
username: root
password: Password
url: jdbc:mysql://localhost:3306/test
driver-class-name: com.mysql.cj.jdbc.Driver
jpa:
show-sql: true
hibernate:
ddl-auto: update
properties.hibernate.dialect: org.hibernate.dialect.MySQL5DialectDB Output
