Experiences with jOOQ
jOOQ is a compelling alternative to the usual JPA (Hibernate, EclipseLink etc.) or Spring JDBC templates for data access in Java applications. We’ve been using jOOQ in development and production for a few months now and I thought I’d share our experiences with it so far.
How it works
jOOQ is a lightweight framework that implements the ActiveRecord pattern which means you’ll be dealing with generated Record classes in the data access layer instead of e.g. your own annotated model classes in JPA.
To get started with jOOQ you need to point it at an instance of your database schema so that it can generate Java classes to represent your schema. Each table gets it’s own Record class generated and other table meta data such as primary keys and foreign keys gets stored into table classes. The generated classes are then used in your application to execute CRUD operations and building queries using the jOOQ DSL.
Basic persistence operations (e.g. Create, Read, Update and Delete) are quite straightforward with jOOQ. When storing data you either fetch an existing Record or create a new one, modify it’s state like any other POJO with setters and then call store() on it. Similarly when fetching data the jOOQ API will return Records for you to use.
Record to Model mapping
You’ll probably choose to maintain a separate domain model in addition to the Record classes generated by jOOQ since you can’t really easily attach any custom business logic to the generated Records. This means that you’ll need some code that translates a database Record to a Model object and vice versa. You can either write this tedious getValue/setValue boilerplate yourself or use something like ModelMapper. This can be argued to be either a pro or a con of the ActiveRecord pattern. On one hand it keeps your domain model clean of framework specific annotations and it won’t tempt you to wrangle your domain model to please your persistence framework like JPA might, on the other hand you will have to come up with a good way to deal with the Model to Record conversion.
Querying with the jOOQ DSL
Querying your schema is also quite straightforward with the jOOQ DSL, for example to fetch all customers with a specific last name sorted by first name one could write:
dsl.selectFrom(CUSTOMER).
where(CUSTOMER.LASTNAME.likeIgnoreCase(lastName)).
orderBy(CUSTOMER.FIRSTNAME.asc()).
fetch();
It is worth noting how the API manages to read almost like plain SQL while still being type safe. If you make breaking changes to your schema your code will simply stop compiling until you fix it which is great compared to trying to find and fix issues in raw SQL, JPQL or HQL query strings which won’t fail until at runtime. The DSL is quite powerful and we haven’t had any issues yet with writing more complex queries. The jOOQ DSL also supports joining tables (more about this later). Since the DSL is so close to SQL syntax the jOOQ guys also haven’t shied away from supporting more advanced or rarely used SQL features such as MERGE or array columns.
The bad
Our main complaint with jOOQ would be the little help it offers with handling object associations (e.g. one-to-many, many-to-many etc.) and how they usually map to JOINs.
For example let’s say I have a Customer class holding a list of Order objects that are stored into customer and order tables respectively,
along with a customer_id foreign key connecting an order row to a customer row.
If I want to load a Customer with the orders populated I can query CUSTOMER.leftOuterJoin(ORDER) easy enough
but I need to do manual processing of the resulting rows to create a valid customer.
For example if a customer has three orders the join query will return:
customer, order1
customer, order2
customer, order3
You’ll end up writing boilerplate code that loops through the result Records to build up the actual Customer object while filtering out duplicate information. Also you’ll probably encounter the gotcha that if a customer doesn’t have any orders you’ll get a bunch of NULLs joined in instead.
Things only get worse if we assume that Order holds a list of OrderItems containing e.g. a quantity and a product id. Now if we want to return a Customer object with fully populated Orders our query will return something like:
customer, order1, orderitem11
customer, order2, orderitem21
customer, order2, orderitem22
customer, order3, orderitem31
customer, order3, orderitem32
customer, order3, orderitem33
Now you’ll need to write even more boilerplate code to filter out the additional duplicate information to ensure that the above Records only result in one Customer with three Orders.
For starters it would be nice if we could somehow give jOOQ some hints about these associations
so that it would generate a List<OrderRecord> field on CustomerRecord and so on.
As it is now there is no association between the generated OrderRecord and CustomerRecord and I need to implement
redundant datatypes (e.g. CustomerRecordWrapper) to hold a CustomerRecord and all related OrderRecords.
On the querying side something like Hibernates ResultTransformer.DISTINCT_ROOT_ENTITY would be nice to avoid writing the same de-duplication boilerplate yourself.
The good
The jOOQ documentation is quite good and it’s quite fast to get started with it. There are also decent guides out there for integrating jOOQ with other common technologies such as Spring and Guice.
The jOOQ DSL provides an easy and type safe way to query your data. This gives jOOQ an advantage over other low level approaches such as (god forbid) raw JDBC or Spring JDBC templates.
Another advantage of jOOQ being close to the metal is that jOOQ supports many advanced or rarely used SQL features. With JPA these are rarely supported out of the box if at all. The jOOQ guys have even implemented a smarter way of implementing result paging.
Most of the time jOOQ “just works” and even when encountering issues since jOOQ operates on a level quite close to SQL it’s quite easy to understand what is going on compared to e.g. debugging a screen full of autogenerated SQL from JPA.
jOOQs runtime schema mapping is also an interesting feature for SaaS applications where it can be used to implement multi-tenancy.
Conclusions
In conclusion I would recommend you give jOOQ a try if you are looking for a lightweight Java persistence framework and you can live with the “JOIN de-duplication boilerplate” overhead issue I mentioned earlier. If your application relies heavily on several levels of eagerly populated object associations you might want to consider something like JPA instead, though you’ll probably encounter performance issues instead.