Spring Jdbc — Passing list of object to IN clause

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.MySQL5Dialect

DB Output

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
PA

PA

Java Developer and enthusiast