Jooq 和 Hibernate 在映射到 DTO 时遇到的 (n+1) 问题

huangapple go评论59阅读模式
英文:

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

问题

我们希望与Hibernate集成,所以想法是使用Jooq作为复杂查询生成器。我看到了很多在JOOQ官方文档中描述的例子(链接:https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities/),但是我没有解决n+1问题和将结果映射到DTO投影中(我们希望使用相同的DTO来映射实体,就像我们用Spring Data做的那样)。

对我来说,将一对多关系映射到DTO中而不需要额外查询是一个问题。

父实体(Parent)有与子实体(Child)的多对一关系。
获取候选人的方法。

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

    /* 2. 使用multiset */

    SelectJoinStep<org.jooq.Record> jooqQuery = dslContext.select(
                  /* 这个Parent类是由jOOQ生成的。
                   * Parent extends TableImpl<ParentRecord>
                   */
                    Parent.asterisk(),
                    multiset(
                  /* 这个CHILD类是由jOOQ生成的。
                   * 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(),
     /* 这个Parent类被javax.persistence.entity注解标记 */
     Parent.class);


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

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

问题:在转换为DTO时,会执行额外的SQL以获取子实体的列表。因此,由JOOQ创建的查询会被Hibernate覆盖,并生成新的查询。

那么,解决这个额外查询并且同时使用相同的DTO(Hibernate用于持久化和简单查询,JOOQ用于复杂查询)的最佳方法是什么?

英文:

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&lt;ParentDto&gt; getAllParents() {
    DSLContext dslContext = DSL.using(SQLDialect.POSTGRES);
    dslContext.configuration().settings().setRenderNameStyle(RenderNameStyle.AS_IS);

    /* 2. using multiset*/

    SelectJoinStep&lt;org.jooq.Record&gt; jooqQuery= dslContext.select(
                  /* This Parent class is generated by jOOQ generator.
                   * Parent extends TableImpl&lt;ParentRecord&gt;
                   */
                    Parent.asterisk(),
                    multiset(
                  /* This CHILD class is generated by jOOQ generator.
                   * CHILD extends TableImpl&lt;ChildRecord&gt;
                   */
                            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&lt;Parent&gt; 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/

答案1

得分: 3

除非你使用jOOQ执行查询,否则不能使用MULTISET

除非你使用jOOQ执行查询,否则不能使用multiset()。虽然multiset()是标准SQL运算符,但jOOQ必须在几乎所有SQL方言(Informix除外,它具有本地支持)上模拟它。你可以从关于MULTISET的手册部分了解它的工作原理

但即使有本地支持,期望JPA实现知道如何处理结果集并将事物映射到预期的DTO或POJO,更别提jOOQ在其内部使用JSONXML时的序列化方式。

实际上,你不需要JPA来执行这个查询

为什么不直接使用jOOQ来执行你的查询呢?这篇博客文章展示了直接使用jOOQ执行查询的多个例子,并且推荐这样做。。总结一下(至少):

  • 映射类型安全性。
  • 映射算法。
  • 执行模拟,比如嵌套记录或multiset,或者批处理连接。
  • 转换器和绑定。
  • 用户定义的类型支持。
  • 存储过程支持。
  • 获取身份值。

特别是,考虑到你几乎无法从中间实体获取和映射步骤中获得任何好处。你不需要实体,你只需要DTO,而jOOQ可以将你的SQL结果映射到任何DTO结构中。

英文:

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.

huangapple
  • 本文由 发表于 2023年3月3日 20:45:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75627257.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定