Comparison between jOOQ and JDBC

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

Similarities with JDBC

Even if there are two general types of Query, there are a lot of similarities between JDBC and jOOQ. Just to name a few:

• Both APIs return the number of affected records in non-result queries. JDBC: Statement.executeUpdate(), jOOQ: Query.execute()
• Both APIs return a scrollable result set type from result queries. JDBC: java.sql.ResultSet, jOOQ: org.jooq.Result

Differences to JDBC

Some of the most important differences between JDBC and jOOQ are listed here:

• Query vs. ResultQuery: JDBC does not formally distinguish between queries that can return results, and queries that cannot. The same API is used for both. This greatly reduces the possibility for fetching convenience methods
• Exception handling: While SQL uses the checked java.sql.SQLException, jOOQ wraps all exceptions in an unchecked org.jooq.exception.DataAccessException
• org.jooq.Result: Unlike its JDBC counter-part, this type implements java.util.List and is fully loaded into Java memory, freeing resources as early as possible. Just like statements, this means that users don't have to deal with a "weird" internal result set state.
• org.jooq.Cursor: If you want more fine-grained control over how many records are fetched into memory at once, you can still do that using jOOQ's lazy fetching feature
• Statement type: jOOQ does not formally distinguish between static statements and prepared statements. By default, all statements are prepared statements in jOOQ, internally. Executing a statement as a static statement can be done simply using a custom settings flag
• Closing Statements: JDBC keeps open resources even if they are already consumed. With JDBC, there is a lot of verbosity around safely closing resources. In jOOQ, resources are closed after consumption, by default. If you want to keep them open after consumption, you have to explicitly say so.
• JDBC flags: JDBC execution flags and modes are not modified. They can be set fluently on a Query
• Zero-based vs one-based APIs: JDBC is a one-based API, jOOQ is a zero-based API. While this makes sense intuitively (JDBC is the less intuitive API from a Java perspective), it can lead to confusion in certain cases.