在本文中，我们探讨使用 JDBC PreparedStatement创建 IN 子句查询的不同方法。最终，所有方法都提供相同的结果，但使用 Stream API 是干净、直接且独立于数据库的。
查询数据库时的一个常见用例是根据输入值列表查找列的匹配项。有多种方法可以做到这一点。IN 子句是为给定列提供多个值以供比较的方法之一。
在本教程中，我们将研究如何在JDBC PreparedStatement中使用 IN 子句。
设置
让我们创建一个客户表并添加一些条目，以便我们可以使用 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);
}
});
}
|
PreparedStatement
PreparedStatement表示一条 SQL 语句，它已经预编译，并且可以通过不同的参数集有效地多次使用。
让我们看一下在PreparedStatement中使用 IN 子句的不同方法。
带有StringBuilder 的IN 子句
构建动态查询的一种简单方法是手动为列表中的每个值添加占位符。StringBuilder有助于有效地连接字符串，而无需创建其他对象：
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 的客户。带有流的 IN 子句
构造 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();
}
|
我们可以通过执行类似的测试来验证上述逻辑，其中我们传递客户 ID 列表并返回预期结果：@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);
}
|
带有setArray() 的IN 子句
最后我们看一下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());
preparedStatement.setArray(1, array);
return preparedStatement.executeQuery();
}
|
在这个方法中，我们改变了查询的结构。我们提供了一个子查询，而不是直接在 IN 子句后添加占位符。这个子查询从数组中读取所有条目作为第一个占位符的值，然后将它们作为 IN 子句的值。另一个重要的区别是我们需要通过指定其保存的值的类型将列表转换为数组。
现在，我们通过一个简单的测试用例来验证实现：
@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);
}
|