The IN operator allows to specify multiple values in a WHERE
clause. IN clause used to avoid multiple OR
conditions. In this guide you will see several examples on how to pass values to the SQL IN clause when you are working with Spring JdbcTemplate query.
1. Passing parameters as List to IN clause
We can provide java Collection List as static parameters OR dynamic parameters to IN clause.
1.2. JdbcTemplate Example 1
Providing List as static parameters to the IN clause avoids type conversions which may improve performance for large queries.
SQL Statement :
SELECT * FROM USER WHERE ID IN (1,2,3,4,5)
@Test @DisplayName("Jdbctemplate-IN-Clause-Static-Query-Test") void jdbcTemplateInClauseStaticTest() { List<Integer> ids = List.of(1,2,3,4,5); String inParams = String.join(",", ids.stream().map(id -> id+"").collect(Collectors.toList())); List<User> users = jdbcTemplate.query( String.format("SELECT * FROM USER WHERE ID IN (%s)",inParams), new UserMapper()); assertTrue(users.size() == 5); }
1.2. JdbcTemplate Example 2
Providing List as dynamic parameters to the IN clause avoids type conversions which may improve performance for large queries.
SQL Statement :
SELECT * FROM USER WHERE ID IN (?,?,?,?,?)
@Test @DisplayName("Jdbctemplate-IN-Clause-Dynamic-Query-Test") void jdbcTemplateInClauseDynamicTest() { List<Integer> ids = List.of(1,2,3,4,5); String inParams = String.join(",", ids.stream().map(id -> "?").collect(Collectors.toList())); List<User> users = jdbcTemplate.query( String.format("SELECT * FROM USER WHERE ID IN (%s)", inParams), ids.toArray(), new UserMapper()); assertTrue(users.size() == 5); }
1.3. Using NamedParameterJdbcTemplate
NamedParameterJdbcTemplate allows you to provide List of parameters directly to SqlParameterSource
or MapSqlParameterSource
. Just provide parameter name in IN operator.
SQL Statement :
SELECT * FROM USER WHERE USERNAME IN (:names)
@Test @DisplayName("NamedParameterJdbctemplate-IN-Clause-Dynamic-Query-Test") void namedParameterJdbcTemplateInClauseDynamicTest() { List<String> names = List.of("PeterM", "Mike", "KingPeter", "PeterH", "Kelvin"); MapSqlParameterSource inQueryParams = new MapSqlParameterSource(); inQueryParams.addValue("names", names); List<User> users = namedJdbcTemplate.query( "SELECT * FROM USER WHERE USERNAME IN (:names)", inQueryParams, new UserMapper()); assertTrue(users.size() == 5); }
2. Multiple columns in IN clause
In addition to the primitive value in the list, you can create a java.util.List
of object arrays. This list would support multiple expressions defined for the in
clause. For example :
SQL Statement :
SELECT * FROM jpa_jbd.user WHERE (ID, USERTYPE) IN ((1,'STUDENT'), (2,'EMPLOYEE'));
@Test @DisplayName("Multiple-IN-Clause-Dynamic-Query-Test") void multipleInCluseJdbcTemplateInClauseDynamicTest() { List<Object[]> id_types = List.of(new Object[]{1, "STUDENT"}, new Object[]{2, "EMPLOYEE"}); MapSqlParameterSource inQueryParams = new MapSqlParameterSource(); inQueryParams.addValue("idAndTypes", id_types); List<User> users = namedJdbcTemplate.query( "SELECT * FROM USER WHERE (ID, USERTYPE) IN (:idAndTypes)", inQueryParams, new UserMapper()); assertTrue(users.size() == 2); }
Note: Your database must support for multiple IN clause expressions.
3. How to work with large number of values in IN Clause
When passing in many values to IN clause, various databases exceed this number, but they usually have a hard limit for how many values are allowed. Oracle’s limit is 1000. One way to work with passing large number of values to IN operator is creating temporary table and use subquery in IN clause to select values. Following is the example creating on Mysql.
SQL Statement :
SELECT * FROM USER WHERE ID IN (SELECT ID FROM USER_ID_TMP)
@Test @DisplayName("Jdbctemplate-IN-Clause-LargeValues-Test") void jdbcTemplateLargeInClauseParamsTest() { List<Integer> ids = List.of(1, 2, 3, 4, 5); jdbcTemplate.execute("CREATE temporary TABLE IF NOT EXISTS USER_ID_TMP (ID bigint NOT NULL)"); List<Object[]> idList = new ArrayList<Object[]>(); ids.forEach(id -> idList.add(new Object[] {id})); jdbcTemplate.batchUpdate("INSERT INTO USER_ID_TMP (ID) VALUES (?)",idList); List<User> users = jdbcTemplate.query("SELECT * FROM USER WHERE ID IN (SELECT ID FROM USER_ID_TMP)", new UserMapper()); assertTrue(users.size() == 5); jdbcTemplate.update("TRUNCATE FROM USER_ID_TMP"); }
4. Conclusion
In this guide we have covered how to pass Collection List of values to the IN clause when working with JdbcTemplate and NamedParameterJdbcTemplate.
Checkout source code at GitHub.
Other Spring JDBC Examples :
- Spring JdbcTemplate – Stored Procedure
- Spring JdbcTemplate – Query
- Spring JdbcTemplate – Pagination
- Spring JdbcTemplate – Batch Update
- Spring JdbcTemplate – Auto-generated Keys
- Spring Boot – Loading Initial Data