在 JdbcTemplate IN 子句中使用List动态参数

我们将学习如何将List传递到 Spring JDBC 模板查询的 IN 子句中。

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

 IN子句里是固定长度的不在今天的讨论范围。通常情况下IN子句里的值数量是可变的，因此我们需要创建一个可以支持动态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;
}

首先我们先生成一个List长度的用逗号分隔的“?”占位符字符串，然后拼接到IN子句里。比如我们ids的List的长度为3，生成的SQL声明如下：

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

 我们用ids List做为参数。通过这种方式，我们能够基于输入的List执行动态SQL。

第二种方式：用NamedParameterJdbcTemplate

另一种执行不固定长度的List做为IN子句条件的方法是用 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;
}

超大List做IN子句

当List中有大量值时，我们应该考虑将List传递给 JdbcTemplate 查询的替代方法。

例如，IN语句中可放的最大参数个数是1000个。mysql中，IN语句中参数个数是不限制的。不过对整段sql语句的长度有了限制（max_allowed_packet）。

​

遇到这种情况 ，可以为List创建一个临时表。不过需要注意的是，不同数据库有不同的方法来创建临时表。比如，我们能用“CREATE GLOBAL TEMPORARY TABLE ”语句在Oracle数据库里创建临时表。

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

我们首先创建一个临时表，然后将List值insert到临时表。

在IN语句里的值从临时表里查询获取，通过这种方式我们可以避免IN语句里有太多参数。

最后我们完成查询后，删除掉临时表以便以后在用。