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();
  }
}

Output

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