This article is part of our Academy Course titled jOOQ – Type safe DB querying.
jOOQ is a good choice in a Java application where SQL and the specific relational database are important. It is an alternative when JPA / Hibernate abstract too much, JDBC too little. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java.
In this course, we’ll see how we can efficiently query databases using jOOQ. Check it out here!
Table Of Contents
1. Introduction
Examples displayed in this section are also available from the org.jooq.academy.section3 package.
Most Java developers have a solid understanding of what JDBC is and how it works. If you haven’t already, please check out the official JDBC tutorials from Oracle to learn more about JDBC.
JDBC has often been criticised for being verbose. JDBC is also criticised for having chosen the wrong “defaults”, e.g. the default to lazy materialisation of result sets. We’ll see how jOOQ improves on these criticisms:
2. Checked exceptions
Java’s checked exceptions have been considered a failure, which is also why Java 8’s new Streams API and all relevant functional interfaces no longer support checked exceptions.
All of jOOQ’s API will throw RuntimeExceptions that are derived from jOOQ’s org.jooq.exception.DataAccessException, which you don’t need to catch in most cases, letting it fall through to abort your currently running transaction. An example comparing the two:
2.1. JDBC
// These two calls can throw a SQLException
try (PreparedStatement stmt = connection.prepareStatement("SELECT FIRST_NAME FROM AUTHOR");
ResultSet rs = stmt.executeQuery()) {
// This can throw a SQLException
while (rs.next()) {
// This can throw a SQLException
System.out.println(rs.getString(1));
}
} 2.2. jOOQ
DSL.using(connection) .select(AUTHOR.FIRST_NAME) .from(AUTHOR) .fetch() .forEach(record -> System.out.println(record.getValue(AUTHOR.FIRST_NAME)));
3. Result sets
JDBC’s ResultSet is a very stateful object that doesn’t interoperate well with the Java collections API. For instance, it doesn’t implement Iterator, because it also has to accommodate scrolling backwards through the underlying database cursor – a feature that hardly anyone needs.
jOOQ integrates SQL result sets via the org.jooq.Result type in a much better way that accommodates 95% of all use-cases:
- jOOQ’s
Resultimplementsjava.util.List, and thus inherits all ofList‘s features, including its capability to be transformed into a Java 8 Stream. - jOOQ’s
Resultis fully materialised into Java memory, instead of being lazy per default. This allows for freeing resources early. - jOOQ’s
Resultknows its ownRecordtype, which allows for typesafe access to record attributes via column references, rather than via column index.
Note that the above are defaults. If you have large result sets that you do not want to materialise record by record, you can always use jOOQ’s lazy fetching feature. This can be seen in the following examples:
3.1. You can use jOOQ Results in foreach loops
for (Record record : DSL.using(connection)
.select()
.from(AUTHOR)
.fetch()) {
System.out.println(record);
} 3.2. You can use jOOQ Results with Java 8 Streams
DSL.using(connection) .select() .from(AUTHOR) .fetch() .stream() .flatMap(record -> Arrays.stream(record.intoArray())) .forEach(System.out::println);
4. Prepared statements
Curiously, JDBC distinguishes between static java.sql.Statement types, and java.sql.PreparedStatement types. This practice will save you from performing a round-trip to the database to prepare the statement prior to execution – but 95% of all queries are best executed using prepared statements anyway, so why bother?
jOOQ doesn’t distinguish between these two execution modes via separate statement types. Instead, you can use a settings flag to indicate that static statements should be executed, when really needed. An example:
4.1. JDBC
// Static statement
try (Statement stmt = connection.createStatement()) {
// Remember to pass the SQL string here!
stmt.executeUpdate("ALTER TABLE ...");
}
// Prepared statement
try (PreparedStatement stmt = connection.prepareStatement("SELECT * FROM ... ")) {
// Remember not to pass the SQL string here!
stmt.executeUpdate();
// ... although, from an API perspective, this would be possible too
stmt.executeUpdate("Some SQL here");
} 4.2. jOOQ
// Static statement
DSL.using(connection, new Settings().withStatementType(StatementType.STATIC_STATEMENT))
.fetch("SELECT * FROM AUTHOR")
// Prepared statement
DSL.using(connection)
.fetch("SELECT * FROM AUTHOR") 5. Statements with result sets
On the other hand, it is impossible to deduce from the JDBC statement type whether a statement is in fact a query returning result set, or whether it will return a number of updated rows, or nothing at all. If you don’t know you’ll have to run the following tedious piece of JDBC code:
5.1. JDBC
try (PreparedStatement stmt = connection.prepareStatement("SELECT FIRST_NAME FROM AUTHOR")) {
// Use the little-known execute() method
boolean moreResults = stmt.execute();
// Use the rarely-used do {} while (...) loop
do {
// Check first, if there is any ResultSet available
if (moreResults) {
try (ResultSet rs = stmt.getResultSet()) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
else {
System.out.println(stmt.getUpdateCount());
}
}
// Repeat until there are neither any more result sets or update counts
while ((moreResults = stmt.getMoreResults()) || stmt.getUpdateCount() != -1);
} 5.2. jOOQ
With jOOQ, you distinguish the two types of statements simply by type:
org.jooq.Queryis a statement with update count and no resultsorg.jooq.ResultQueryis a statement with results
Only ResultQuery has the various fetch() methods:
Query q1 = dsl.query("ALTER TABLE ...");
int rows = q1.execute();
ResultQuery<?> q2 = dsl.resultQuery("SELECT * FROM AUTHOR");
Result<?> result = q2.fetch(); 
