[Java] 在JDBC使用SQL IN

這篇文章Batching Select Statements in JDBC提供四種解法

擷錄前言：


In networking, one of the most expensive things you can do is make a server roundtrip. In JDBC, this corresponds to a database call. If you are doing inserts, updates or deletes, you can use the executeBatch() signature to cut down the number of trips to the server. Unfortunately, there is no such built-in mechanism for select queries.

Suppose you want to get the names for a given list of ids. Logically, we would want to do something like:

PreparedStatement stmt = conn.prepareStatement(
 "select id, name from users where id in (?)");
stmt.setString("1,2,3");

However, this will not work. JDBC only allows you to substitute a single literal value in the "?". You can't substitute things that are meant to become part of the SQL statement. This is necessary because if the SQL itself can change, the driver can't precompile the statement. It also has the nice side effect of preventing SQL injection attacks. Instead, you have four options for the implementation:

1. Run the query separately for each id
2. Build one query to do everything
3. Use a stored procedure
4. Select batching

最後選擇效能最快的一種。但尚未測試時間差多少倍。

StringBuilder inClause = new StringBuilder();
boolean firstValue = true;
for (int i=0; i < batchSize; i++) {
  inClause.append('?');
  if ( firstValue ) {
    firstValue = false;
  } else {
    inClause.append(',');
  }
}
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (" + inClause.toString() + ')');


for (int i=0; i < batchSize; i++) {
  stmt.setInt(i);  // or whatever values you are trying to query by
}