Technical difference between Spring Boot with JOOQ and Spring Data JPA

Question

When would you use Spring Data JPA over Spring Boot with JOOQ and vice versa?

I know that Spring Data JPA can be used for completing basic CRUD queries, but not really for complex join queries while using JOOQ makes it easier?

EDIT: Can you use both Spring data jpa with jooq?

Answer 1

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.

Answer 2

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.