Spring - Passing Java Collection to IN SQL clause

This example shows how to use IN SQL clause to pass multiple values as Java Collection of primitive values in a WHERE clause.

Example

@Component
public class EmployeeClientBean {

    @Autowired
    private DataSource dataSource;

    private NamedParameterJdbcTemplate jdbcTemplate;

    @PostConstruct
    private void postConstruct() {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        saveEmployees();
    }

    public void run() {
        List<String> departments = Arrays.asList("Sale", "Account", "Admin");

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("deptParamName", departments);

        List<Employee> list = jdbcTemplate.query("SELECT * FROM Employee WHERE Dept IN (:deptParamName)",
                parameters, new RowMapper<Employee>() {
                    @Override
                    public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
                        return toEmployee(resultSet);
                    }
                });

        list.stream().forEach(System.out::println);
    }

    private void saveEmployees() {
        List<Employee> list = Arrays.asList(
                Employee.create("Jim", "IT"),
                Employee.create("Sara", "Sale"),
                Employee.create("Tom", "Admin"),
                Employee.create("Diana", "IT"),
                Employee.create("Tina", "Sale"),
                Employee.create("Joe", "Account"),
                Employee.create("Lara", "IT"),
                Employee.create("Charlie", "Sale")
        );

        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("EMPLOYEE")
                .usingGeneratedKeyColumns("id");
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
        int[] ints = simpleJdbcInsert.executeBatch(batch);
    }

    private Employee toEmployee(ResultSet resultSet) throws SQLException {
        Employee employee = new Employee();
        employee.setId(resultSet.getLong("ID"));
        employee.setName(resultSet.getString("NAME"));
        employee.setDept(resultSet.getString("DEPT"));
        return employee;
    }
}

public class Employee {
    private long id;
    private String name;
    private String dept;
    .............
}

src/main/resources/createEmployeeTable.sql

CREATE TABLE EMPLOYEE
(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255),
DEPT VARCHAR(255)
);

Java Config and Main Class

@Configuration
@ComponentScan
public class AppConfig {

    @Bean
    public DataSource h2DataSource() {
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript("createEmployeeTable.sql")//script to create person table
                .build();
    }

    public static void main(String[] args) {
        AnnotationConfigApplicationContext context =
                new AnnotationConfigApplicationContext(AppConfig.class);
        context.getBean(EmployeeClientBean.class).run();
    }
}

Employee{id=2, name='Sara', dept='Sale'}
Employee{id=3, name='Tom', dept='Admin'}
Employee{id=5, name='Tina', dept='Sale'}
Employee{id=6, name='Joe', dept='Account'}
Employee{id=8, name='Charlie', dept='Sale'}

Example Project

Dependencies and Technologies Used:

• spring-context 5.0.0.RELEASE: Spring Context.
• spring-jdbc 5.0.0.RELEASE: Spring JDBC.
• h2 1.4.196: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9