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 operating and persisting the object graph, JPA is a good fit.
And sometimes, it's best to combine both