Using a List of Values in a JdbcTemplate IN Clause

Last updated: May 11, 2024

Written by: Gang Wu

Reviewed by: Eric Martin

Spring Data

JDBC
SQL

Azure Container Apps is a fully managed serverless container service that enables you to build and deploy modern, cloud-native Java applications and microservices at scale. It offers a simplified developer experience while providing the flexibility and portability of containers.

Of course, Azure Container Apps has really solid support for our ecosystem, from a number of build options, managed Java components, native metrics, dynamic logger, and quite a bit more.

To learn more about Java features on Azure Container Apps, visit the documentation page.

You can also ask questions and leave feedback on the Azure Container Apps GitHub page.

Of course, Azure Container Apps has really solid support for our ecosystem, from a number of build options, managed Java components, native metrics, dynamic logger, and quite a bit more.

To learn more about Java features on Azure Container Apps, you can get started over on the documentation page.

And, you can also ask questions and leave feedback on the Azure Container Apps GitHub page.

Modern software architecture is often broken. Slow delivery leads to missed opportunities, innovation is stalled due to architectural complexities, and engineering resources are exceedingly expensive.

Orkes is the leading workflow orchestration platform built to enable teams to transform the way they develop, connect, and deploy applications, microservices, AI agents, and more.

With Orkes Conductor managed through Orkes Cloud, developers can focus on building mission critical applications without worrying about infrastructure maintenance to meet goals and, simply put, taking new products live faster and reducing total cost of ownership.

Try a 14-Day Free Trial of Orkes Conductor today.

Orkes is the leading workflow orchestration platform built to enable teams to transform the way they develop, connect, and deploy applications, microservices, AI agents, and more.

Try a 14-Day Free Trial of Orkes Conductor today.

End-to-end testing is a very useful method to make sure that your application works as intended. This highlights issues in the overall functionality of the software, that the unit and integration test stages may miss.

Playwright is an easy-to-use, but powerful tool that automates end-to-end testing, and supports all modern browsers and platforms.

When coupled with LambdaTest (an AI-powered cloud-based test execution platform) it can be further scaled to run the Playwright scripts in parallel across 3000+ browser and device combinations:

>> Automated End-to-End Testing With Playwright

1. Introduction

In a SQL statement, we can use the IN operator to test whether an expression matches any value in a list. As such, we can use the IN operator instead of multiple OR conditions.

In this tutorial, we’ll learn how to pass a list of values into the IN clause of a Spring JDBC template query.

**2. Passing a List Parameter to IN Clause**

The IN operator allows us to specify multiple values in a WHERE clause. For example, we can use it to find all employees whose id is in a specified id list:

SELECT * FROM EMPLOYEE WHERE id IN (1, 2, 3)

Typically, the total number of values inside the IN clause is variable. Therefore, we need to create a placeholder that can support a dynamic list of values.

2.1. With JdbcTemplate

With JdbcTemplate, we can use ‘?’ characters as placeholders for the list of values. The number of ‘?’ characters will be the same as the size of the list:

List<Employee> getEmployeesFromIdList(List<Integer> ids) {
    String inSql = String.join(",", Collections.nCopies(ids.size(), "?"));
 
    List<Employee> employees = jdbcTemplate.query(
      String.format("SELECT * FROM EMPLOYEE WHERE id IN (%s)", inSql), 
      ids.toArray(), 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"), 
        rs.getString("last_name")));

    return employees;
}

In this method, we first generate a placeholder string that contains ids.size() ‘?’ characters separated with commas. Then we put this string into the IN clause of our SQL statement. For example, if we have three numbers in the ids list, the SQL statement is:

SELECT * FROM EMPLOYEE WHERE id IN (?,?,?)

In the query method, we pass the ids list as a parameter to match the placeholders inside the IN clause. This way, we can execute a dynamic SQL statement based on the input list of values.

2.2. With NamedParameterJdbcTemplate

Another way to handle the dynamic list of values is to use NamedParameterJdbcTemplate. For example, we can directly create a named parameter for the input list:

List<Employee> getEmployeesFromIdListNamed(List<Integer> ids) {
    SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);
 
    List<Employee> employees = namedJdbcTemplate.query(
      "SELECT * FROM EMPLOYEE WHERE id IN (:ids)", 
      parameters, 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"),
        rs.getString("last_name")));

    return employees;
}

In this method, we first construct a MapSqlParameterSource object that contains the input id list. Then we only use one named parameter to represent the dynamic list of values.

Under the hood, NamedParameterJdbcTemplate substitutes the named parameters for the ‘?’ placeholders, and uses JdbcTemplate to execute the query.

3. Handling a Large List

When we have a large number of values in a list, we should consider alternate ways to pass them into the JdbcTemplate query.

For example, the Oracle database doesn’t support more than 1,000 literals in an IN clause.

One way to do this is to create a temporary table for the list. However, different databases can have different ways to create temporary tables. For instance, we can use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table in the Oracle database.

Let’s create a temporary table for the H2 database:

List<Employee> getEmployeesFromLargeIdList(List<Integer> ids) {
    jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS employee_tmp (id INT NOT NULL)");

    List<Object[]> employeeIds = new ArrayList<>();
    for (Integer id : ids) {
        employeeIds.add(new Object[] { id });
    }
    jdbcTemplate.batchUpdate("INSERT INTO employee_tmp VALUES(?)", employeeIds);

    List<Employee> employees = jdbcTemplate.query(
      "SELECT * FROM EMPLOYEE WHERE id IN (SELECT id FROM employee_tmp)", 
      (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"),
      rs.getString("last_name")));

    jdbcTemplate.update("DELETE FROM employee_tmp");
 
    return employees;
}

Here, we first create a temporary table to hold all the values of the input list. Then we insert the input list’s values into the table.

In our resulting SQL statement, the values in the IN clause are from the temporary table, and we avoid constructing an IN clause with a large number of placeholders.

Finally, after we finish the query, we can clean up the temporary table for future use.

4. Conclusion

In this article, we demonstrated how to use JdbcTemplate and NamedParameterJdbcTemplate to pass a list of values for the IN clause of a SQL query. We also provided an alternate way to handle a large number of list values by using a temporary table.

The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.

Of course, Azure Container Apps has really solid support for our ecosystem, from a number of build options, managed Java components, native metrics, dynamic logger, and quite a bit more.

To learn more about Java features on Azure Container Apps, visit the documentation page.

You can also ask questions and leave feedback on the Azure Container Apps GitHub page.

Orkes is the leading workflow orchestration platform built to enable teams to transform the way they develop, connect, and deploy applications, microservices, AI agents, and more.

Try a 14-Day Free Trial of Orkes Conductor today.

Orkes is the leading workflow orchestration platform built to enable teams to transform the way they develop, connect, and deploy applications, microservices, AI agents, and more.

Try a 14-Day Free Trial of Orkes Conductor today.