Spring JdbcTemplate – In Clause with List of Values

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 :

1. Spring JdbcTemplate – Stored Procedure
2. Spring JdbcTemplate – Query
3. Spring JdbcTemplate – Pagination
4. Spring JdbcTemplate – Batch Update
5. Spring JdbcTemplate – Auto-generated Keys
6. Spring Boot – Loading Initial Data

5. References

1. Spring Document
2. MySql Temporary Tables
3. On Stackoverflow
4. Spring JdbcTemplate Query