Technical difference between Spring Boot with JOOQ and Spring Data JPA

Here's an official explanation when JOOQ fits:

https://www.jooq.org/doc/latest/manual/getting-started/jooq-and-jpa/

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? jOOQ only has some of the answers. While jOOQ does offer updatable records that help running simple CRUD, a batch API, optimistic locking capabilities, jOOQ mainly focuses on executing actual SQL statements.

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

There is no easy answer to your question. I have given a couple of talks on that topic. Sometimes there are good reasons to have both in a project.

Edit: IMHO Abstraction over the database in regards of dialects and datatypes is not the main point here!! jOOQ does a pretty good job to generate SQL for a given target dialect - and so does JPA / Hibernate. I would even say that jOOQ goes an extra mile to emulate functions for databases that don't have all the bells and whistles like Postgres or Oracle. The question here is "Do I want to be able to express a query myself with everything SQL has to offer or am I happy with what JPA can express?"

Here's an example to run both together. I have a Spring Data JPA provided repository here with a custom extension (interface and implementation are necessary). I let the Spring context inject both the JPA EntityManager as well as the jOOQ context. I then use jOOQ to create queries and run them through JPA. Why? Because expressing the query in question is not possible with JPA ("Give me the thing I listened the most" which is not the one having the highest number of count, but could be several).

The reason I run the query through JPA is simple: A downstream use case might require me to pass JPA entities to it. jOOQ can of course run this query itself and you could work on records or map the stuff anyway u like. But as you specifically asked about maybe using both technologies, I thought this is a good example:

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Record;
import org.jooq.SelectQuery;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;
import org.springframework.data.repository.CrudRepository;

import static ac.simons.bootiful_databases.db.tables.Genres.GENRES;
import static ac.simons.bootiful_databases.db.tables.Plays.PLAYS;
import static ac.simons.bootiful_databases.db.tables.Tracks.TRACKS;
import static org.jooq.impl.DSL.count;
import static org.jooq.impl.DSL.rank;
import static org.jooq.impl.DSL.select;

public interface GenreRepository extends 
        CrudRepository<GenreEntity, Integer>, GenreRepositoryExt {

    List<GenreEntity> findAllByOrderByName();
}

interface GenreRepositoryExt {
    List<GenreWithPlaycount> findAllWithPlaycount();

    List<GenreEntity> findWithHighestPlaycount();
}

class GenreRepositoryImpl implements GenreRepositoryExt {

    private final EntityManager entityManager;

    private final DSLContext create;

    public GenreRepositoryImpl(EntityManager entityManager, DSLContext create) {
        this.entityManager = entityManager;
        this.create = create;
    }

    @Override
    public List<GenreWithPlaycount> findAllWithPlaycount() {
        final Field<Integer> cnt = count().as("cnt");
        return this.create
                .select(GENRES.GENRE, cnt)
                .from(PLAYS)
                .join(TRACKS).onKey()
                .join(GENRES).onKey()
                .groupBy(GENRES.GENRE)
                .orderBy(cnt)
                .fetchInto(GenreWithPlaycount.class);
    }

    @Override
    public List<GenreEntity> findWithHighestPlaycount() {
        /*
        select id, genre 
        from (
          select g.id, g.genre, rank() over (order by count(*) desc) rnk 
            from plays p
            join tracks t on p.track_id = t.id
            join genres g on t.genre_id = g.id
           group by g.id, g.genre
        ) src
        where src.rnk = 1;
        */
        final SelectQuery<Record> sqlGenerator = 
        this.create.select()
                .from(
                        select(
                                GENRES.ID, GENRES.GENRE, 
                                rank().over().orderBy(count().desc()).as("rnk")
                        ).from(PLAYS)
                        .join(TRACKS).onKey()
                        .join(GENRES).onKey()
                        .groupBy(GENRES.ID, GENRES.GENRE)
                ).where(DSL.field("rnk").eq(1)).getQuery();

         // Retrieve sql with named parameter
        final String sql = sqlGenerator.getSQL(ParamType.NAMED);
        // and create actual hibernate query
        final Query query = this.entityManager.createNativeQuery(sql, GenreEntity.class);
        // fill in parameter
        sqlGenerator.getParams().forEach((n, v) -> query.setParameter(n, v.getValue()));
        // execute query
        return query.getResultList();
    }
}

I spoke about this a couple of times. There is no silver bullet in those tech, sometimes it's a very thin judgement:

The full talk is here: https://speakerdeck.com/michaelsimons/live-with-your-sql-fetish-and-choose-the-right-tool-for-the-job

As well as the recorded version of it: https://www.youtube.com/watch?v=NJ9ZJstVL9E

The full working example is here https://github.com/michael-simons/bootiful-databases.

IMHO if you want a performing and maintainable application which uses a database at its core, you don't want to abstract away the fact that you are using a database. JOOQ gives you full control because you can read and write the actual query in your code but with type safety.

JPA embraces the OO model and this simply does not match the way a database works in all cases, which could result in unexpected queries such as N+1 because you put the wrong annotation on a field. If you are not paying enough attention this will lead to performance issues when scaling your application. JPA Criteria helps a bit but it still way harder to write and read.

As a result, with JPA you are first writing your query in SQL and then use half a day to translate it to Criteria. After years of working with both frameworks I would use JOOQ even for simple a CRUD application (because there is no such thing as a simple CRUD application :-)).

Edit: I don't think that you can mix JPA with JOOQ, question is, why would you want to? They are both using a different approach so just choose one. It's difficult enough to learn the intricacies of one framework.

Spring Data JPA gives you the following:

1. An ORM layer, allowing you to treat database tables as if they were Java objects. It allows you to write code that is largely database-agnostic (you can use MySQL, Oracle, SQL Server, etc) and that avoids much of the error-prone code that you get when writing bare SQL.
2. The Unit of Work pattern. One reason why you see so many articles on C# explaining what a unit of work is, and practically none for Java, is because of JPA. Java has had this for 15 years; C#, well, you never know.
3. Domain-driven design repositories. DDD is an approach to object-oriented software that does away with the anaemic domain model you so often see in database-driven applications, with entity object only having data and accessor methods (anaemic model), and all business logic in service classes. There's more to it, but this is the most important bit that pertains to Spring Data JPA.
4. Integration into the Spring ecosystem, with inversion of control, dependency injection, etc.

jOOQ, on the other hand, is a database mapping library that implements the active record pattern. It takes an SQL-centric approach to database operations, and uses a domain-specific language for that purpose.

As happens so often, there is no one correct or superior choice. Spring Data JPA works very well if you don't care about your database. If you're happy not to do any complicated queries, then Spring Data JPA will be enough. However, once you need to do joins between tables, you notice that a Spring Data JPA repository really isn't a very good match for certain operations.

As @michael-simons mentioned, combining the two can sometimes be the best solution.