jOOQ and JPA
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Just because you're using jOOQ doesn't mean you have to use it for everything!
When introducing jOOQ into an existing application that uses JPA, the common question is always: "Should we replace JPA by jOOQ?" and "How do we proceed doing that?"
Beware that jOOQ is not a replacement for JPA. Think of jOOQ as a complement. JPA (and ORMs in general) try to solve the object graph persistence problem. In short, this problem is about
- Loading an entity graph into client memory from a database
- Manipulating that graph in the client
- Storing the modification back to the database
As the above graph gets more complex, a lot of tricky questions arise like:
- What's the optimal order of SQL DML operations for loading and storing entities?
- How can we batch the commands more efficiently?
- How can we keep the transaction footprint as low as possible without compromising on ACID?
- How can we implement optimistic locking?
SQL is the preferred language of database interaction, when any of the following are given:
- You run reports and analytics on large data sets directly in the database
- You import / export data using ETL
- You run complex business logic as SQL queries
Whenever SQL is a good fit, jOOQ is a good fit. Whenever you're persisting an object graph, JPA is a good fit. Though note that starting with jOOQ 3.15 you can also load trees with the MULTISET_AGG function and the MULTISET value constructor very easily.
And sometimes, it's best to combine both