Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

Batched Connection

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ supports JDBC batching through dedicated API to explicitly batch some statements, for improved performance. If performance is an "after thought" in some areas of your application, or batching doesn't work well for all environments (or even makes things worse, depending on the dialect), the org.jooq.tools.jdbc.BatchedConnection can be used to transparently buffer all jOOQ (and other JDBC) statements and execute them lazily as batches.

Imagine you might have two services, which produce the following INSERT statements behind the scenes:

// "Regular code":
// ---------------
module1.insertSomething(configuration);
module2.insertSomethingElse(configuration);

// The above might generate... (each line is a statement)
// INSERT INTO something (A, B) VALUES (?, ?)
// INSERT INTO something (A, B) VALUES (?, ?)
// INSERT INTO something (A, B) VALUES (?, ?)
// INSERT INTO something (A, B, C) VALUES (?, ?, ?)
// INSERT INTO something (A, B, C) VALUES (?, ?, ?)
// INSERT INTO something (A, B) VALUES (?, ?)
// INSERT INTO something_else (X, Y) VALUES (?, ?)
// INSERT INTO something_else (X, Y) VALUES (?, ?)
// INSERT INTO something_else (X, Y) VALUES (?, ?)

The business logic is complex, and you don't want to touch it again, just to improve performance. You can now either wrap your own JDBC connection in a org.jooq.tools.jdbc.BatchedConnection, or let jOOQ do that for you by calling DSLContext.batched():

// "Batch-collecting code".
// Alternatively, use DSLContext.batchedResult() to return a result from the lambda.
DSL.using(configuration).batched(c -> {
    module1.insertSomething(c);
    module2.insertSomethingElse(c);
});

// The above might now generate... (each line is a batch statement)
// INSERT INTO something (A, B) VALUES (?, ?)       -- With 3 calls to PreparedStatement.addBatch()
// INSERT INTO something (A, B, C) VALUES (?, ?, ?) -- With 2 calls to PreparedStatement.addBatch()
// INSERT INTO something (A, B) VALUES (?, ?)       -- With 1 calls to PreparedStatement.addBatch()
// INSERT INTO something_else (X, Y) VALUES (?, ?)  -- With 2 calls to PreparedStatement.addBatch()

Without changing the SQL strings, or the execution sequence, this way, it is now possible to buffer consecutive identical SQL strings and collect their bind values in a single batch.

As soon as any of the following events happens, the buffered batch is executed and a new batch is created:

  • The SQL string changes (including "irrelevant" whitespace changes).
  • A query producing results is executed.
  • A static statement (as opposed to a prepared statement) is created.
  • The connection is closed, or the transaction committed, or any other such interaction with JDBC is invoked.
  • The batch size threshold is reached.

Limitations

While this approach to batching is transparent to most use-cases, there are some limitations:

To track effectively batched statements, turn on DEBUG logging.

For more known limitations of this functionality, please refer to #10692.

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo