JOOQ Facts: From JPA Annotations to JOOQ Table Mappings

JOOQ is a neat framework, and it addresses a long-time issue I’ve had with advanced dynamic filtered queries. While Hibernate and JPA come with a useful Criteria API, which I’ve been using for quite some time, there are understandable limits to what you can do with those. For instance, you cannot go beyond simple SQL operations (e.g JOINS, NESTED SLECTS, AGGREGATION) and do something like: window functions, user-defined functions or easy sequencing to name a few.

JOOQ doesn’t feel like competing with Hibernate, but instead, I feel like it completes it. I’ve been using Hibernate mostly for the WRITE part of my Data Layer, hence its name or the “Persisting” part in JPA. For simple to medium complex queries, Hibernate does its best, but I don’t have to solely rely on it for all my queries, am I? There is also a drawback to querying properties and that’s because you sometimes have to add an association to your Domain Model just for the sake of querying it for a small number of use cases.

So, since I’m not afraid of writing native queries, I could, therefore, do it in a DSL fashion and vendor-independent way.

While you can go with string-based column naming, JOOQ offers a better approach through the use of a type-safe Metadata, so the first thing we need to do is to generate our table mapping for our database schema.

Since I already have a JPA Model, I can generate a database schema DDL from it, and for that, we can use the hibernate tool ant task.

This will generate a “create_db.sql” database DDL script, which we’ll use to populate a temporary file-based HSQLDB, using “maven.sql.plugin”. I would have preferred the in-memory HSQLDB but unfortunately, it didn’t save the state between plugin executions.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

This is the first time I used JOOQ, and it didn’t take me too much time to browse the documentation and to have everything set up in my Hibernate Facts coding example. The JOOQ query building feels natural, it’s like writing native SQL code, so I don’t have to actually learn the API to know how to use it. I will proudly add it to my Java Data Toolbox.

This coding example generates the JOOQ mappings into the test-classes folder and so you can’t use them from within the main/java source files. This can be solved, but it requires refactoring the existing solution by moving the model classes into a separate Maven module. You could generate the JOOQ schema in this separate module, where before packaging you would move the schema classes from test-classes to the classes folder. Then you would have to include this new module, where you would normally make use of the JOOQ schema.