Search code examples
hibernatejpajooqdto

Jooq and Hibernate (n+1) problem while map into Dtos

We want to integrate together with Hibernate, so idea is to use Jooq as a complex query generator. I saw a lot of examples that is describe in JOOQ Official example

But I didn't manage to solve the n+1 problem and mapping into the DTO projections(we want the same Dtos to use form mapping entities as we used for Spring Data)

So for me is a problem mapping one-to-many relations into dto without additional query.

@Entity Parent have one too many connections to child entities. Methods that Fetch candidates.

@Override
@Transactional
public io.vavr.collection.List<ParentDto> getAllParents() {
    DSLContext dslContext = DSL.using(SQLDialect.POSTGRES);
    dslContext.configuration().settings().setRenderNameStyle(RenderNameStyle.AS_IS);

    /* 2. using multiset*/

    SelectJoinStep<org.jooq.Record> jooqQuery= dslContext.select(
                  /* This Parent class is generated by jOOQ generator.
                   * Parent extends TableImpl<ParentRecord>
                   */
                    Parent.asterisk(),
                    multiset(
                  /* This CHILD class is generated by jOOQ generator.
                   * CHILD extends TableImpl<ChildRecord>
                   */
                            select(CHILD.ID)
                                    .from(CHILD)
                                    .where(PARENT.ID.eq(CHILD.PARENT_ID)))
            )
            .from(Parent);

    javax.persistence.Query query = entityManager.createNativeQuery(jooqQuery.getSQL(),
     /* this Parent class is anotated with javax.persistence.entity */
     Parent.class);


    List<Parent> parents = query.getResultList();

    return parents.stream().map(ParentDto::new);
}

Problem: During to convert to DTO additional sql is executed to fetch list of childs. So query created by JOOQ is overrided by hibernate and generated new one.

So what is best approach to solve this additional query, and use same dtos (hibernate for persistence and simple queries and JOOQ for complex query) ?

https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities/

Solution

  • You cannot use MULTISET unless you execute the query with jOOQ

    You cannot use multiset() unless you're executing your query with jOOQ. While multiset() is a standard SQL operator, jOOQ has to emulate it on almost any SQL dialect (except Informix, which has native support). You can see how this works from the manual section about MULTISET

    But even with native support, it would be a stretch to expect JPA implementations to know what to do with the result set and map things to DTO's or POJOs as intended, let alone when jOOQ serialises things in its own internal ways using JSON or XML.

    You don't actually need JPA to execute this query

    Why not just use jOOQ to execute your query? This blog post shows multiple examples of why executing queries directly with jOOQ is recommended.. In summary (at least):

    • Mapping type safety.
    • Mapping algorithms.
    • Execution emulations, like nested records or multiset, or batched connections
    • Converters and bindings
    • User defined type support
    • Stored procedure support
    • Fetching of identity values

    Especially, given that you hardly get any benefits out of the intermediary entity fetching and mapping step. You don't need the entity, you only want the DTO, and jOOQ can map your SQL results into any DTO structure.