Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Reusing a Query's PreparedStatement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
As previously discussed in the chapter about differences between jOOQ and JDBC, reusing PreparedStatements is handled a bit differently in jOOQ from how it is handled in JDBC
Keeping open PreparedStatements with JDBC
With JDBC, you can easily reuse a java.sql.PreparedStatement by not closing it between subsequent executions. An example is given here:
// Execute the statement
try (PreparedStatement stmt = connection.prepareStatement("SELECT 1 FROM DUAL")) {
// Fetch a first ResultSet
try (ResultSet rs1 = stmt.executeQuery()) { ... }
// Without closing the statement, execute it again to fetch another ResultSet
try (ResultSet rs2 = stmt.executeQuery()) { ... }
}
The above technique can be quite useful when you want to reuse expensive database resources. This can be the case when your statement is executed very frequently and your database would take non-negligible time to soft-parse the prepared statement and generate a new statement / cursor resource.
Keeping open PreparedStatements with jOOQ
This is also modeled in jOOQ. However, the difference to JDBC is that closing a statement is the default action, whereas keeping it open has to be configured explicitly. This is better than JDBC, because the default action should be the one that is used most often. Keeping open statements is rarely done in average applications. Here's an example of how to keep open PreparedStatements with jOOQ:
// Create a query which is configured to keep its underlying PreparedStatement open
try (ResultQuery<Record> query = create.selectOne().keepStatement(true)) {
Result<Record> result1 = query.fetch(); // This will lazily create a new PreparedStatement
Result<Record> result2 = query.fetch(); // This will reuse the previous PreparedStatement
}
The above example shows how a query can be executed twice against the same underlying java.sql.PreparedStatement. Notice how the Query must now be treated like a resource, i.e. it must be managed in a try-with-resources statement, or Query.close() must be called explicitly.
Feedback
Do you have any feedback about this page? We'd love to hear it!