How to Execute SQL Batches With JDBC and jOOQ

Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a “batch” of statements in a single statement. For instance, in SQL Server, you can do something like this:

DECLARE  @  table  AS  TABLE  (id INT  );
INSERT  INTO  @  table  VALUES  (1),(2),(3);

Executing a Batch With JDBC

Unfortunately, the term “batch” has several meanings, and in this case, I don’t mean the JDBC Statement.addBatch() method, which is actually a bit clumsy as it doesn’t allow for fetching mixed update counts and result sets.

Instead, what I’ll be doing is this:

    + "\n  DECLARE @table AS TABLE (id INT);            "
    + "\n  SELECT * FROM @table;                        "
    + "\n  INSERT INTO @table VALUES (1),(2),(3);       "
    + "\n  SELECT * FROM @table;                        "  ;
try  (PreparedStatement s = c.prepareStatement(sql)) {
      for  (  int  i = 0  , updateCount = 0  ;; i++) {
          boolean  result = (i == 0  )
              try  (ResultSet rs = s.getResultSet()) {
                  System.out.println(  "\nResult:"  );
                      System.out.println(  "  "  + rs.getInt(  1  ));
          else  if  ((updateCount = s.getUpdateCount()) != -  1  )
              System.out.println(  "\nUpdate Count: "  + updateCount);

The output of the above program being:

Result:

Update Count: 3

Result:
  1
  2
  3

The above API usage is a somewhat “hidden” – or at least not everyday usage of the JDBC API. Mostly, you’ll be using Statement.executeQuery() when you’re expecting a ResultSet, or Statement.executeUpdate() otherwise.

But in our case, we don’t really know what’s happening. We’re going to discover the result types on the fly when executing the statement. Here are the main JDBC API features that we’re using, along with an explanation:

• Statement.execute(): This method should be used if we don’t know the result type. The method returns a boolean, which is true when the first statement in the batch produced a ResultSet and false otherwise.
• Statement.getMoreResults(): This method returns the same kind of boolean value as the previous Statement.execute() method, but it does so for the next statement in the batch (i.e. for every statement except the first).
• If the current result is a ResultSet (the boolean was true), then we’ll obtain that ResultSet through Statement.getResultSet() (we can obviously no longer call the usual Statement.executeQuery() to obtain the ResultSet).
• If the current result is not a ResultSet (the boolean was true), then we’ll check the update count value through Statement.getUpdateCount().
• If the update count is -1, then we’ve reached the end of the batch.

What a nice state machine!

The nice thing about this is that a batch may be completely non-deterministic. For example, there may be triggers, T-SQL blocks (e.g. an IF statement), stored procedures, and many other things that contribute result sets and/or update counts. In some cases, we simply don’t know what we’ll get.

Executing a Batch With jOOQ

It’s great that the JDBC API designers have thought of this exotic API usage on a rather low level. This makes JDBC extremely powerful. But who remembers the exact algorithm all the time? After all, the above minimalistic version required around 20 lines of code for something as simple as that.

Compare this to the following jOOQ API usage:

DSL.using(c).fetchMany(sql)

The result being:

Result set:
+----+
|  id|
+----+
Update count: 3
Result set:
+----+
|  id|
+----+
|   1|
|   2|
|   3|
+----+

Huh! Couldn’t get much simpler than that! Let’s walk through what happens:

The DSLContext.fetchMany() method is intended for use when users know there will be many result sets and/or update counts. Unlike JDBC which reuses ordinary JDBC API, jOOQ has a different API here to clearly distinguish between behaviours. The method then eagerly fetches all the results and update counts in one go (lazy fetching is on the roadmap with issue #4503).

The resulting type is org.jooq.Results, a type that extends List<Result>, which allows for iterating over the results only, for convenience. If a mix of results or update counts need to be consumed, the Results.resultsOrRows() method can be used.