在JdbcTemplate IN子句中使用值列表

2023/05/18

1. 简介

在 SQL 语句中,我们可以使用 IN 运算符来测试表达式是否匹配列表中的任何值。因此,我们可以使用 IN 运算符而不是多个 OR 条件。

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

2. 将列表参数传递给IN子句

IN 运算符允许我们在 WHERE 子句中指定多个值。例如,我们可以使用它来查找 id 在指定 id 列表中的所有员工:

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

通常,IN 子句中值的总数是可变的。因此,我们需要创建一个可以支持动态值列表的占位符。

2.1. 使用JdbcTemplate

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

在这个方法中,我们首先生成一个占位符字符串,其中包含ids.size() ’?’ 以逗号分隔的字符。然后我们将这个字符串放入 SQL 语句的 IN 子句中。例如,如果我们在ids列表中有三个数字,则 SQL 语句为:

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

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

2.2. 带命名参数的JdbcTemplate

处理动态值列表的另一种方法是使用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;
}

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

在幕后,NamedParameterJdbcTemplate将命名参数替换为“?” 占位符,并使用JdbcTemplate来执行查询。

3.处理大列表

当列表中有大量值时,我们应该考虑将它们传递到JdbcTemplate查询的替代方法。

例如,Oracle 数据库不支持 IN 子句中超过 1,000 个文字。

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

让我们为 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;
}

在这里,我们首先创建一个临时表来保存输入列表的所有值。然后我们将输入列表的值插入表中。

在我们生成的 SQL 语句中,IN 子句中的值来自临时表,我们避免构造带有大量占位符的 IN 子句。

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

4. 总结

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

与往常一样,本教程的完整源代码可在GitHub上获得。

Show Disqus Comments

Post Directory

扫码关注公众号:Taketoday
发送 290992
即可立即永久解锁本站全部文章