1. Introduction

1.介绍

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.

在SQL语句中，我们可以使用IN操作符来测试一个表达式是否与一个列表中的任何值相匹配。因此，我们可以使用IN运算符来代替多个OR条件。

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

在本教程中，我们将学习如何在Spring JDBC模板查询的IN子句中传递一个值列表。

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

**2.将List参数传递给IN子句**

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:

IN操作符允许我们在一个WHERE子句中指定多个值。例如，我们可以用它来查找所有id在指定id列表中的雇员。

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.

通常情况下，IN子句里面的数值总数是可变的。因此，我们需要创建一个能够支持动态值列表的占位符。

2.1. With JdbcTemplate

2.1.使用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:

通过JdbcTemplate，我们可以使用’? ‘字符作为数值列表的占位符。’？’字符的数量将与列表的大小相同。

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:

在这个方法中，我们首先生成一个占位符字符串，其中包含ids.size() ‘? ‘字符，用逗号分隔。然后我们把这个字符串放到我们的SQL语句的IN子句中。例如，如果我们在ids列表中有三个数字，那么SQL语句就是。

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.

在query方法中，我们将ids列表作为参数传递，以匹配IN子句里面的占位符。这样，我们就可以根据输入的值列表来执行一个动态的SQL语句。

2.2. With NamedParameterJdbcTemplate

2.2.使用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:

另一种处理动态值列表的方法是使用NamedParameterJdbcTemplate。例如，我们可以直接为输入列表创建一个命名参数。

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.

在这个方法中，我们首先构造一个MapSqlParameterSource对象，其中包含输入的id列表。然后我们只用一个命名的参数来表示动态的值列表。

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

在引擎盖下，NamedParameterJdbcTemplate将命名参数替换为’?’占位符，并使用JdbcTemplate来执行查询。

3. Handling a Large List

3.处理大的清单

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

当我们在一个列表中有大量的值时，我们应该考虑用其他方式将它们传入JdbcTemplate查询。

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

例如，Oracle数据库不支持IN子句中超过1000个字。

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.

一种方法是为列表创建一个临时表。然而，不同的数据库可以有不同的方式来创建临时表。例如，我们可以使用CREATE GLOBAL TEMPORARY TABLE语句来在Oracle数据库中创建一个临时表。

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

让我们为H2数据库创建一个临时表。

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.

在我们产生的SQL语句中，IN子句中的值来自临时表，我们避免用大量的占位符构建IN子句。

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

最后，在我们完成查询后，我们可以清理临时表以备将来使用。

4. Conclusion

4.结论

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.

在这篇文章中，我们演示了如何使用JdbcTemplate和NamedParameterJdbcTemplate来为SQL查询的IN子句传递一个列表值。我们还提供了另一种方法，通过使用一个临时表来处理大量的列表值。

As always, the source code for the article is available over on GitHub.

一如既往，该文章的源代码可在GitHub上获得。