Can PreparedStatement.addBatch() be used for SELECT queries?

Question

Imagine that I have 100 SELECT queries that differ by one input. A PreparedStatement can be used for the value.

All the documentation I see on the Web is for batch insert/update/delete. I have never seen batches used for select statements.

Can this be done? If so, please help me when the below sample code.

I suppose this can be done using an "IN" clause, but I would prefer to use batched select statements.

Sample code:

public void run(Connection db_conn, List value_list) {
    String sql = "SELECT * FROM DATA_TABLE WHERE ATTR = ?";
    PreparedStatement pstmt = db_conn.prepareStatement(sql);
    for (String value: value_list) {
        pstmt.clearParameters();
        pstmt.setObject(1, value);
        pstmt.addBatch();
    }
    // What do I call here?
    int[] result_array = pstmt.executeBatch()
    while (pstmt.getMoreResults()) {
        ResultSet result_set = pstmt.getResultSet();
        // do work here
    }
}

I suppose this may also be driver-dependent behaviour. I am writing queries against IBM AS/400 DB2 database using their JDBC driver.

Answer 1

See the Java Tutorial:

This list may contain statements for updating, inserting, or deleting a row; and it may also contain DDL statements such as CREATE TABLE and DROP TABLE. It cannot, however, contain a statement that would produce a ResultSet object, such as a SELECT statement. In other words, the list can contain only statements that produce an update count.

The list, which is associated with a Statement object at its creation, is initially empty. You can add SQL commands to this list with the method addBatch.

Answer 2

JDBC doesn't allow creating batched SELECT queries, which in my opinion is a frustrating limitation, particularly since prepared statements don't allow you to specify a variable number of arguments, like an IN (...) clause.

The JavaRanch article F.J links to suggests simulating batching by creating a series of fixed-size queries and joining their results, which seems like a cumbersome and suboptimal fix to me; you have to manually construct and process multiple queries now, and hit the database multiple times. If the numbers chosen for the manually defined batches are poor, you could end up hitting the database quite a few times just to answer a simple query.

Instead, I've taken to dynamically constructing PreparedStatement objects with the number of fields I need. This does mean potentially we create a larger number of PreparedStatements than we would with the manual batching, but we limit how often we hit the database and simplify our implementation, both of which I view as a more important issues.

/**
 * Use this method to create batch-able queries, e.g:
 * "SELECT * FROM t WHERE x IN (?, ?, ?, ?)"
 * Can be built as:
 * "SELECT * FROM t where x IN ("+getLineOfQs(4)+")"
 */
public static String getLineOfQs(int num) {
  // Joiner and Iterables from the Guava library
  return Joiner.on(", ").join(Iterables.limit(Iterables.cycle("?"), num));
}

/**
 * Gets the set of IDs associated with a given list of words
 */
public Set<Integer> find(Connection conn, List<String> words)
    throws SQLException {
  Set<Integer> result = new HashSet<>();
  try(PreparedStatement ps = conn.prepareStatement(
      "SELECT id FROM my_table WHERE word IN ("+
      getLineOfQs(words.size())+")")) {
    for(int i = 0; i < words.size(); i++) {
      ps.setString(i+1, words.get(i));
    }

    try (ResultSet rs = ps.executeQuery()) {
      while(rs.next()) {
        result.add(rs.getInt("id"));
      }
    }
  }
  return result;
}

This isn't too painful to code, affords you the safety of using PreparedStatement, and avoids unnecessary database hits.