在 JDBC PreparedStatement 中使用 IN 语句-工具盒子

1、简介 {#1简介}

在数据库 SQL 查询中，我们经常使用 IN 语句来一次性检索匹配多个值的记录，本文将带你了解如何在 JDBC PreparedStatement 中使用 IN 子句。

2、设置 {#2设置}

创建一个 CUSTOMER 表，并添加一些记录，以便使用 IN 子句进行查询：

void populateDB() throws SQLException {
    String createTable = "CREATE TABLE CUSTOMER (id INT, first_name VARCHAR(50), last_name VARCHAR(50))";
    connection.createStatement().execute(createTable);

    String load = "INSERT INTO CUSTOMER (id, first_name, last_name) VALUES(?,?,?)";
    IntStream.rangeClosed(1, 100)
      .forEach(i -> {
          PreparedStatement preparedStatement1 = null;
          try {
              preparedStatement1 = connection.prepareStatement(load);
              preparedStatement1.setInt(1, i);
              preparedStatement1.setString(2, "firstname" + i);
              preparedStatement1.setString(3, "lastname" + i);
              preparedStatement1.execute();
          } catch (SQLException e) {
              throw new RuntimeException(e);
          }
      });
}

3、PreparedStatement {#3preparedstatement}

PreparedStatement 表示一个已经预编译的 SQL 语句，可以高效地多次使用，每次使用可以填充不同的参数。

3.1、使用 StringBuilder {#31使用-stringbuilder}

构建动态查询的一种简单方法是为列表中的每个参数值手动添加占位符。这可以通过 StringBuilder 拼接 SQL 字符串来实现：

ResultSet populateParamsWithStringBuilder(Connection connection, List<Integer> ids) 
  throws SQLException {
    StringBuilder stringBuilder = new StringBuilder();

    for (int i = 0; i < ids.size(); i++) {
        stringBuilder.append("?,");
    }
    String placeHolders = stringBuilder.deleteCharAt(stringBuilder.length() - 1)
      .toString();
    
    String sql = "select * from customer where id in (" + placeHolders + ")";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 1; i <= ids.size(); i++) {
        preparedStatement.setInt(i, ids.get(i - 1));
    }
    return preparedStatement.executeQuery();
}

在此方法中，我们通过拼接用逗号（,）分隔的占位符（?）来创建占位符字符串。接着，将占位符字符串与查询字符串拼接起来，以创建最终由 PreparedStatement 使用的 SQL 语句。

测试用例如下：

@Test
void whenPopulatingINClauseWithStringBuilder_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithStringBuilder(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}

如你所见，我们成功地使用 IN 子句检索到了所有指定 ID 的客户。

3.2、使用 Stream {#32使用-stream}

另一种构造 IN 子句的方法是使用 Stream API，将所有值映射为占位符（?），然后将它们作为参数提供给 String 类的 format() 方法：

ResultSet populateParamsWithStream(Connection connection, List<Integer> ids) throws SQLException {
    var sql = String.format("select * from customer where id IN (%s)", ids.stream()
      .map(v -> "?")        // 映射为 ?
      .collect(Collectors.joining(", "))); // 使用 , 拼接
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 1; i <= ids.size(); i++) {
        preparedStatement.setInt(i, ids.get(i - 1));
    }
    return preparedStatement.executeQuery();
}

测试如下：

@Test
void whenPopulatingINClauseWithStream_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithStream(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}

3.3、使用 setArray() {#33使用-setarray}

最后，来看看 PreparedStatement 类的 setArray() 方法：

ResultSet populateParamsWithArray(Connection connection, List<Integer> ids) throws SQLException {
    String sql = "SELECT * FROM customer where id IN (select * from table(x int = ?))";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    Array array = preparedStatement.getConnection()
      .createArrayOf("int", ids.toArray()); // 创建 Array，值的类型是 int
    preparedStatement.setArray(1, array);
    return preparedStatement.executeQuery();
}

在这个方法中，我们改变了查询的结构。我们提供了一个子查询，而不是直接在 IN 子句后面添加占位符。这个子查询读取作为第一个占位符值提供的数组中的所有条目，然后将它们作为 IN 子句的值提供。

另一个重要的区别是，我们需要将 List 转换为数组（java.sql.Array），并指定它所包含的值的类型。

测试如下：

@Test
void whenPopulatingINClauseWithArray_thenIsSuccess() throws SQLException {
    ResultSet resultSet = PreparedStatementInClause
      .populateParamsWithArray(connection, List.of(1, 2, 3, 4, 55));
    Assertions.assertNotNull(resultSet);
    resultSet.last();
    int size = resultSet.getRow();
    Assertions.assertEquals(5, size);
}