A Beginner’s Guide to Using Java EE with jOOQ

Lukas EderOctober 12th, 2015Last Updated: October 12th, 2015

0 45 3 minutes read

Java EE ships with its own persistence API: JPA. JPA is most powerful when you want to map your RDBMS entities (tables / relations) to Java entities (classes), mostly following a 1:1 mapping strategy. The idea behind this is that often, business logic isn’t really set-oriented as relational algebra or SQL, but record-oriented, meaning that business rules and business logic is applied to individual records.

In other words, when SQL and relational algebra is about values (tuples), JPA is about identity and state (of individual records). And this is where JPA shines, because:

Life is too short to write CRUD with SQL

But as Gavin King always said:

RDBMS are not just about CRUD

Gavin King was well aware of the OLAP hype that was going on at the time he started working on Hibernate, the most popular JPA implementation. Business intelligence, or data science as it is called nowadays, relies on much more advanced functionality than simple CRUD – functionality that has never been targeted by the JPA specification, or by its implementations.

In fact, you don’t necessarily have to do OLAP to benefit from native SQL, simpler use-cases in more ordinary OLTP environments can appear as well, such as

Reporting
Batch and bulk data processing
Query with complex business rules

While JPA offers JPQL and Criteria API, which will help you express some amount of complexity in your queries, you will eventually be limited by the features offered in those languages and APIs, as Michael Simons has recently documented in an interesting Criteria API to jOOQ comparison.

For this reason, all JPA implementations offer a way to query the database using “native SQL”. In a previous blog post, we’ve shown how you can leverage jOOQ’s type safe DSL API to run SQL queries via JPA’s native query API, and then fetch results…

… as managed entities
… as DTOs mapped using SqlResultSetMapping

In the above cases, jOOQ is used only as a SQL query builder, while query execution is left to JPA.

Do all database querying with jOOQ, in Java EE

Remember jOOQ’s philosophy:

jOOQ is essentially type safe JDBC. Nothing more.

Even if you can use JPA to execute native SQL, you don’t have to. You can operate directly on a JDBC level, something that is often required with JPA, e.g. when working…

… with vendor-specific data types
… with non-trivial stored procedures
… with statement batches
… with updatable cursors

When you run your application on an application server, you can pick the features that you want and need, and use proprietary APIs (such as jOOQ, which runs on top of JDBC) for the rest. For instance, you can use:

EJB for session and scope management
CDI for dependency injection
jOOQ for your database interaction

(you could also add JTA to the stack – for simplicity reasons, we’ll skip that for now)

The procedure is simple: Just inject a javax.sql.DataSource into your session bean using CDI:

@Stateless
public class LibraryEJB {

    @Resource(lookup="java:data-source-configuration")
    private DataSource ds;
}

… and start working with it using JDBC:

public List<Author> fetchAuthors() 
throws SQLException {
    List<Author> result = new ArrayList<>();

    // Get a Connection from the injected DataSource
    try(Connection con = ds.getConnection();
        PreparedStatement stmt = con.prepareStatement(
            "SELECT * FROM AUTHOR ORDER BY ID");
        ResultSet rs = stmt.executeQuery()
    ) {
        result.add(new Author(
            rs.getInt("ID"),
            rs.getString("FIRST_NAME"),
            rs.getString("LAST_NAME")
        ));
    }

    return result;
}

… or using jOOQ:

public Result<AuthorRecord> fetchAuthors() {

    // Pass the injected DataSource to jOOQ
    return DSL.using(ds, H2)
              .selectFrom(AUTHOR)
              .orderBy(AUTHOR.ID)
              .fetch();
}

Notice how jOOQ – by default – fetches all results eagerly into memory, closing resources like the JDBC Connection, PreparedStatement, and ResultSet eagerly, such that you’re not required to deal with the hassle of resource management yourself.

Again:

jOOQ is essentially type safe JDBC. Nothing more.

JDBC has always been an important part of Java EE applications, for all sorts of reasons, including access to vendor-specific features. jOOQ adds compile-time type safety on top of JDBC. Nothing more. Whatever works with JDBC will work with jOOQ.

In particular, jOOQ will never interfere with your transaction or session model, regardless of the choice you make. All that is needed by jOOQ is a JDBC Connection or DataSource.

Running an example in JBoss WildFly

The above example can be checked out from GitHub and run directly in WildFly, for example – or with only little adaptations in any other Java EE application server: https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-javaee-example

The example was created for WildFly in the context of a Webinar with Arun Gupta. The webinar answers the following questions:

What is jOOQ ?
Why JOOQ when there is JDBC and JPA ?
How does it fit with Java EE apps ? Does it uses underlying JPA persistence provider or some other connection ?
Pros/cons over JPA ? Pure Hibernate ?
How well does it scale ?
Show code sample in a Java EE application
jOOQ for CRUD-based or domain-rich application ?
How can eventually all the work in jOOQ be integrated in JPA and be standardized ? Or would it be more of JDBC ?

The full webinar can be seen on YouTube, here: