JDBC 準備語句 SQL IN 子句
一、簡介
查詢資料庫時的常見用例是根據輸入值清單尋找列的匹配項。有多種方法可以做到這一點。 IN 子句是為給定列提供多個值進行比較的方法之一。
在本教程中，我們將了解如何將 IN 子句與PreparedStatement結合使用。
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
PreparedStatement代表一條SQL語句，它已經預先編譯，並且可以透過不同的參數集有效地多次使用。
讓我們來看看在PreparedStatement.
3.1. IN 子句與StringBuilder
建構動態查詢的簡單方法是為清單中的每個值手動附加佔位符。 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 的客戶。
3.2.有流的 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);
}3.3. IN 子句與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());
preparedStatement.setArray(1, array);
return preparedStatement.executeQuery();
}在此方法中，我們更改了查詢的結構。我們提供了一個子查詢，而不是直接在 IN 子句後面加入佔位符。此子查詢從陣列中讀取作為第一個佔位符的值提供的所有項目，然後將這些條目作為 IN 子句的值提供。
另一個重要的區別是，我們需要透過指定它所保存的值的類型來將List轉換為[Array](https://docs.oracle.com/en/java/javase/22/docs/api/java.sql/java/sql/Array.html) 。
現在，讓我們用一個簡單的測試案例來驗證實作：
@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);
}4. 結論
在本文中，我們探討了使用 JDBCPreparedStatement 建立 IN 子句查詢的不同方法PreparedStatement.最終，所有方法都提供相同的結果，但是使用 Stream API 是乾淨、簡單且獨立於資料庫的。
與往常一樣，所有程式碼範例都可以在 GitHub 上找到。