/**
* Issues a query with a potentially large number of keys in batches. For example, you might have
* 10,000 ids that you wish to use in an "in" clause, but don't trust the database to be smart
* about optimizing that many keys, so instead you use batchQuery like so:
*
* <pre>
* Collection<Integer> keys = ...;
* String query = "select NAME from USERS where USER_ID in (#KEYS)";
* JDBCUtil.BatchProcessor proc = new JDBCUtil.BatchProcessor() {
* public void process (ResultSet row) {
* String name = rs.getString(1);
* // do whatever with name
* }
* };
* JDBCUtil.batchQuery(conn, query, keys, false, 500, proc);
* </pre>
*
* @param query the SQL query to run for each batch with the string <code>#KEYS#</code> in the
* place where the batch of keys should be substituted.
* @param escapeKeys if true, {@link #escape} will be called on each key to escape any dangerous
* characters and wrap the key in quotes.
* @param batchSize the number of keys at a time to substitute in for <code>#KEYS#</code>.
*/
public static void batchQuery(
Connection conn,
String query,
Collection<?> keys,
boolean escapeKeys,
int batchSize,
BatchProcessor processor)
throws SQLException {
Statement stmt = conn.createStatement();
try {
Iterator<?> itr = keys.iterator();
while (itr.hasNext()) {
// group one batch of keys together
StringBuilder buf = new StringBuilder();
for (int ii = 0; ii < batchSize && itr.hasNext(); ii++) {
if (ii > 0) {
buf.append(",");
}
String key = String.valueOf(itr.next());
buf.append(escapeKeys ? escape(key) : key);
}
// issue the query with that batch
String squery = query.replace("#KEYS#", buf.toString());
ResultSet rs = stmt.executeQuery(squery);
while (rs.next()) {
processor.process(rs);
}
}
} finally {
close(stmt);
}
}
