Enterprise Java

Comparing jOOQ with JDBC

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!

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 Result implements java.util.List, and thus inherits all of List‘s features, including its capability to be transformed into a Java 8 Stream.
  • jOOQ’s Result is fully materialised into Java memory, instead of being lazy per default. This allows for freeing resources early.
  • jOOQ’s Result knows its own Record type, 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:

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(); 

Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button