A Beginner’s Guide to Using Java EE with jOOQ

Posted on by lukaseder
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: gavin-king-on-hibernate

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… 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:

Published by lukaseder

I made jOOQ

6 thoughts on “A Beginner’s Guide to Using Java EE with jOOQ”

  1. 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.

    It makes no sense to say that any given piece of business logic is “really” record oriented and not set oriented. Relational algebra is a general way of reasoning about the world, like the predicate logic it is equivalent to. Records are just a form of representation. You can *choose* to reason about the world (i.e. some business problem) in terms of records, or even be pushed into by the language or framework you are working in, or be trapped in thinking that way due to mental habits or limitations, but there is nothing inherently better about reasoning about anything in terms of records. Sets are a logical abstraction. Records are a physical implementation, and an abstraction based on them is one mired in and limited by implementation thinking. That was Codd’s whole point. To create a higher level of abstraction over the implementation-mired file and record based database programming of his day.

    SQL and relational algebra is about values (tuples), JPA is about identity and state (of individual records)

    This is another false dichotomy. Those tuples are statements about the world, which may include statements about state or identity.

    Reply

    1. Thanks for your thoughtful comment, Eric. You’re right, of course. Among all the things you listed, I will choose “mental habits” as the main driver for false dichotomies. Which makes them a bit less false, perhaps, as they become de-facto “truths”.

      What if not implementation drives our every day work? The abstraction was phrased out in theory at some point of time. But it is not tangible, as is the implementation. For instance, we could argue if SQL is a good-enough implementation for relational algebra. Or: What aspects of reasoning about today’s databases is really mired in by “SQL thinking”, as opposed to a potentially purer approach that is based on the more fundamental theory?

      Coincidentally, I’m having a very similar discussion with Luca Veronese on a different article written here:

      http://www.vertabelo.com/blog/notes-from-the-lab/why-sql-is-neither-legacy-nor-low-level-but-simply-awesome#comment-2298320852

      Reply

      1. What if not implementation drives our every day work? The abstraction as phrased out in theory at some point of time

        It should not be. You should always continue to think and design in terms of the abstraction first, and implementation second. We should always strive to not let the implementation bend the abstraction, but change the implementation when the abstraction changes as needs change, are refined, or clarified.

        I would think you or any OO programmer would agree with this, where API or code interface is the abstraction. The principle is the same when the relational model is the abstraction.

        I will choose “mental habits” as the main driver for false dichotomies. Which makes them a bit less false, perhaps, as they become de-facto “truths”.

        I agree but the danger is that those defacto “truths” sometimes lead to mental errors. As someone who spends more of his working hours in OO land than in relational land, I have to make an effort not let OO twist my thinking. The most insidious thing OO does is encourage you to think of your constructs as representing things in the real world (or business domain) rather than as information about the real world. This is another physical thinking trap, as it encourages you to think about your constructs as being constrained the way things in the physical worlds are (e.g. forcing things into hierarchies because physical things can only be in one place at a time rather than classifying things in several orthogonal ways). And worrying too much about “identity”.

        Reply

        1. It should not be. You should always continue to think and design in terms of the abstraction first, and implementation second.

          Well, I agree that this should be the tendency – But I can also live with the fact that we’re working with technologies like Java and SQL. If I was looking for abstraction first all the time, I’d go nuts :)

          Here, we’re talking about using jOOQ (implentation of SQL on top of implementation of an “OO language”) in Java EE (the best example of an implementation that pretends to be the abstraction)… How come we even started discussing theory in this context?

          The most insidious thing OO does is encourage you to think of your constructs as representing things in the real world […]

          That’s an interesting thought! Would be worth a blog post of its own!

          I might not have been spoiled enough by OO – I’ve always liked data driven languages like SQL or XSLT. And I totally agree that “identity” is the source of all evil (in forms of overengineering) in the Java ecosystem. There’s actually a blog post about that coming up soon. About the legacy of java.lang.Object, and the entangled, distributed equals() and hashCode() contract, and about “why would you even need to put a non-value type in a Set or a HashMap?”

          Reply

Leave a Reply