How to Execute SQL Batches With JDBC and jOOQ
If you're running a database that supports batching statements in a single command, make use of it to enhance your efficiency and productivity using JDBC and jOOQ.
Join the DZone community and get the full member experience.
Join For FreeSome 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 aboolean
, which istrue
when the first statement in the batch produced aResultSet
andfalse
otherwise.Statement.getMoreResults()
: This method returns the same kind ofboolean
value as the previousStatement.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
(theboolean
wastrue
), then we’ll obtain thatResultSet
throughStatement.getResultSet()
(we can obviously no longer call the usualStatement.executeQuery()
to obtain theResultSet
). - If the current result is not a
ResultSet
(theboolean
wastrue
), then we’ll check the update count value throughStatement.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.
Published at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments