DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > How to Execute SQL Batches With JDBC and jOOQ

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.

Lukas Eder user avatar by
Lukas Eder
·
Feb. 07, 17 · Database Zone · Tutorial
Like (1)
Save
Tweet
6.89K Views

Join the DZone community and get the full member experience.

Join For Free

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);


This is a batch of four statements, and it can be executed as a single statement both with JDBC and with jOOQ. Let’s see how:

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.

MySQL API

Published at DZone with permission of Lukas Eder. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Spelling “Equality” With IT: Addressing the Gender Gap in the Tech Industry
  • Chopping the Monolith: The Demo
  • Java: Why Core-to-Core Latency Matters
  • How Template Literal Types Work in TypeScript

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo