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":
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:
- With query execution being delayed, the JDBC
PreparedStatement#executeUpdate()calls cannot produce the correct row count value yet. They always produce0, instead, as no rows have (yet) been affected. - If a query produces results (e.g. ordinary SELECT statements, or INSERT .. RETURNING statements), batching is prevented.
To track effectively batched statements, turn on DEBUG logging.
For more known limitations of this functionality, please refer to #10692.
Feedback
Do you have any feedback about this page? We'd love to hear it!