Alternative query tooling for Java

Many lines have been written about JPA, Hibernate, pros and cons. For some, the sheer mention of such tools sounds as if someone was invoking the raise of hell itself. For others, they are just tools that simplify a few things, make others worse and require tons of tuning.

There are compelling reasons in both sides. But I would say I belong to the second group. JPA/Hibernate can make you save tons of time and they even make the code look clean and concise… but you’ve got to know what you are doing when you need to ship production code. What may look good in your tests may not scale in production… and that’s when you choose belong to the first group.

Having said that, i’ll be showing 2 alternative libraries that allow you to control the queries you run against your DB with a nice modern DSL fashion.

It is worth mentioning that running queries against a DB has many sides. Two of them are: the query itself -its cost- and mapping the results to objects. Depending on how complex you entity graph is, the mapping can be very tedious, time consuming and error prone.

For this examples, i’ll be using the following model

The main idea is that the product exists independently and the items just belong to the shipment and point to one product.

You can find the complete source code here.

JOOQ

JOOQ is an open source/commercial library, so you may need look at the agreement if you were to use it on highly regulated environment.

It has a big community and its main developer Lukas Eder is very active in twitter, github and stackoverflow.

It offers a few different flavors: Create your tables using code, reverse engineer a DB to generate clases with metadata or generate those classes with JPA.

It is a super complete library that interacts with almost any database engine and offers DDL and DML features as well as tons of configuration for the code generation.

No code generation

With this approach, you’ll notice that on one side it does abstract you from writing plain SQL with a nice DSL fashion using the a dialectunder the hood. However, it does tend to be tedious to write the queries and then having to map it's result to objects.

Based on a simple spring config, the dslContext what allows you interact with the DB, wether it is to run DDL or DML. It contains the datasource and the dialect that it’s needed.

Here’s an example on how to create the Items table. This a small table but there tons of other options.

Now, what about queries?

The following is an example of getting the Shipment by id.

As you can see, the DSL is beautiful but the mapping is super painful.

The cardinality between Shipment and Item is 1..n and, thus, the result set contains as many rows as Items present in the shipment. For that reason, you need to iterate over the result appending new items along the way.

Yes, I know i’m setting the same data in the shipment again and again in every step of theforeach but I wanted to keep everything contained in the same lambda.

You may have noticed that I have used id as primary key name in every table. This ended up being a problem to map as it made the code even harder to read. However, looking at the bright side, it gave me the chance of using the as SQL keyword through the DSL.

Reverse engineer code generation

When it comes to code generation, JOOQ generates classes that contain the schema information such as table names, columns names, types column types and so forth. Then, with those clases and through DSLContext you perform your queries in the DB.

This first approach reverse engineers the database tables to in order to obtain the required metadata. To achieve this, you first need to configure the generator in the pom file with the database connection settings and then run mvn generate-sources which will connect and generate the code.

This is what a simple query looks like:

You will notice the fetchInto(Product.class) this provides some sort of out-of-the-box mapping which works well when your query does not include a join.

Now, when it comes to a more complicated query, you will need to do the mapping yourself but at least you do not need to hardcode the table names or column names. Also, you won’t need the aliases as JOOQ does everything internally… but hold your horses, there’s something neat after this.

As you can see there’s almost no difference between this mapping and the previous one, apart from the generate tables metadata.

However, JOOQ has a really neat feature: JSON/XML mapping documented here: https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/.

By using that feature, the mapping now looks like this:

This is a really useful feature. Under the hood, it delegates the mapping to either gson, Jackson, JAXB -whichever is present in the classpath.

The are 2 key parts here: The json object key names which must match with your POJOs attribute names and the groupBy clause. In my case, there is only one group: the shipment… but there can be many levels of grouping. It is still not a walk in park but you avoid nested looping and the code looks much cleaner and simpler.

JPA codegen

Wasn’t JOOQ an alternative to JPA? 🤔 … Yes, it is. Remember the 2 big parts of querying: The query and the mapping. With JPA you resolve the mapping instantly whilst still controlling the query to be executed.

This approch “does not need” a DB connection. It reverse engineers the JPA annotations such as @Table and @Column to generate the metadata classes. Effetively, what JOOQ does is to use an in-memory DB, creates the schema there and then reverse engineers that schema under the hood.

The integration with JPA is not automatic but for it needs some extra code to be added to you code base. What JOOQ does here is to translate the query that you build through the DSL into a native query and send it to the entityManager. This is the implementation of the code as-is, then you can potentially play around with it.

Some caveats here:

• We are dealing with 2 tools (JOOQ and JPA) that generate “things”: JOOQ generates code based on the DB and JPA generates SQL based on the annotations. They need to be able to speak between themselves at some point.
• I ran into many issues with case-sensitivity: On one side, I could not make JOOQ generator to create everything in lower-case so I had to add a naming to hibernate to generate the table names and all in upper-case and run the queries in upper-case as well. With this, I was able to maintain compatibility between MySQL and H2.

So this is how you query with JOOQ and JPA mapping:

As you can see, you still write the query with the JOOQ DSL and but you also provide a class to be used by JPA for mapping.

QueryDSL

QuerySQL is a really straight forward library: It only focuses on DML and it has no intention of being big library that handles every possible interaction with the DB.

It integrates out-of-the-box with JPA (and the JPA websites highlight is here) and provides code generation capabilities to generate metadata classes to interact with the DB based on the annotations.

There’s no need to say that all the mapping is taken care of by JPA.

Simple query, not bit issues:

A more complex query:

In this case we run into the same issue that we have with JPA: in this case, the items collection of the shipment is LAZY and we need to perform a fetchJoin which may not be cheap if the graph is complex. But at least you need to specifically write the join and the fetch so the effect is not unexpected.

There is SQL based QueryDSL that I am not showing here because it seems to be the same deal than with JOOQ. You can find it here.

Final thoughts

On side, we have JOOQ which is a really big library with a really amazing DSL and completely abstracted from JPA. It allows you to control everything that happens in your DB.

On the other side there is QueryDSL which is a smaller library and quite to the point: write queries using JPA.

One might say: “Why use QueryDSL if it still uses JPA which is what I do not want to use?”. True, my answer is: mapping. Yes, it uses JPA but you need to intentionally write the query that you want and JPA does the mapping which in my opinion is one of the hardest thing to maintain and test when the domain object is big.

With JOOQ you may use the JSON/XML mapping feature, RecordMappers or use a third-party library like ModelMapper or Orika suggested in the docs.

JOOQ gave me too many problems with the generation based on JPA. I tried all the things that I found online, including this one from its author. Besides that, the integration with JPA does seem like a hack rather than an integration.

My opinion is that if you are using spring with JPA already or if mapping is really too complex to be done manually (or by a library), give QueryDSL a shot.

If you are fine with the mapping code, then go with JOOQ which seems to be most generic one and it really feels like thinking/writing SQL. It’s fair to say that there are few good libraries to handle the mapping if this is an issue for you.

For the schema creation/evolution: QueryDSL doesn’t offer DDL features and even though JOOQ has them, I would handle if with a tool like Liquibase to have a repeatable set of changes, history of them and mostly portability.