26

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.

dimo414
  • 47,227
  • 18
  • 148
  • 244
kevinarpe
  • 20,319
  • 26
  • 127
  • 154
  • 1
    Could you elaborate on why you'd prefer to use batched statements over an `IN` clause? If we weren't using JDBC, we'd write an `IN` query, no? What's the benefit of having a different pattern for JDBC? – dimo414 Nov 24 '13 at 22:06
  • This old one from JavaRanch offers a few options: [Batching Select Statements in JDBC](http://www.javaranch.com/journal/200510/Journal200510.jsp#a2) – F.J Oct 26 '11 at 07:16

4 Answers4

53

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.

Community
  • 1
  • 1
user207421
  • 305,947
  • 44
  • 307
  • 483
  • 4
    @a_horse_with_no_name I would rather have linked to reference material actually, there's nothing authoritative about a tutorial, and some of them contain amazing blunders. But I couldn't find a decent statement of the issue in the 45 seconds I allocated to this task. – user207421 Oct 26 '11 at 22:37
  • @jmunsch Works for me. – user207421 Jul 31 '17 at 10:27
  • @Laurel I didn't write 'the documentation', and my comments here had already made it clear for some years that this *isn't* the documentation, just a tutorial. I don't appreciate having words put into my mouth. – user207421 Feb 16 '19 at 07:05
12

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.

dimo414
  • 47,227
  • 18
  • 148
  • 244
  • 3
    I'm not a huge fan of guava normally because while in theory it all seems useful, I've found it has very limited uses in practice..but that said, I do like the way you used it here. – searchengine27 Jun 30 '18 at 01:21
  • You're missing out; Guava enables a *ton* of really useful patterns. A number of techniques it introduced were incorporated into the JDK in Java 8, but there's still plenty of other neat tools provided by Guava. I'd encourage you to take another look. – dimo414 Jun 30 '18 at 05:42
3

As pointed out in other answers the JDBC Batch Updates does not work for SELECT queries or is at least not meant to be used for that.

Since the question however mentions DB2 as the RDBMS, I think it is worth pointing out here (albeit a bit late now...), that the official driver for this particular database actually does offer this feature using DB2PreparedStatement#executeDB2QueryBatch() (see documentation for details). I am not aware of any other JDBC driver with this feature.

knutwannheden
  • 680
  • 4
  • 7
3

AddBatch() is for 'delete'/'insert'/' update' statements, and not 'select' statements.