如何在一个 JPQL 查询中使用多个 JOIN FETCH [英] How to use multiple JOIN FETCH in one JPQL query

问题描述

我有以下实体:

public class Category {私有整数 ID；@OneToMany(mappedBy = "父")私人列表<主题>话题;}公共课主题{私有整数 ID；@OneToMany(mappedBy = "父")私人列表<帖子>职位；@ManyToOne@JoinColumn(name = "id")私有类别父级；}公共课后{私有整数 ID；@ManyToOne@JoinColumn(name = "id")私人主题父级；/* 发布字段 */}

并且我想使用 JPQL 查询获取所有带有加入的 topics 和加入的 posts 的类别.我写了如下查询:

SELECT c FROM Category cJOIN FETCH c.topics tJOIN FETCH t.posts p WHERE

但是我得到了错误

org.hibernate.loader.MultipleBagFetchException: 不能同时获取多个包

我找到了有关此错误的文章，但这些文章仅描述了在一个实体中有两个要加入的集合的情况.我的问题有点不同，我不知道如何解决.

可以在一个查询中完成吗?

解决方案

考虑到我们有以下实体:

并且，您想要获取一些父 Post 实体以及所有关联的 comments 和 tags 集合.

如果您使用多个 JOIN FETCH 指令:

Listpost = entityManager.createQuery(""";选择 p来自邮政左连接获取 p.comments左连接获取 p.tags其中 :minId 和 :maxId 之间的 p.id""", Post.class).setParameter(minId", 1L).setParameter("maxId", 50L).getResultList();

Hibernate 将抛出 MultipleBagFetchException:

org.hibernate.loader.MultipleBagFetchException: 不能同时获取多个包 [com.vladmihalcea.book.hpjp.hibernate.fetching.Post.comments，com.vladmihalcea.book.hpjp.hibernate.fetching.Post.tags]

Hibernate 抛出此异常的原因是它不允许获取多个包，因为这会生成笛卡尔积.

最糟糕的解决方案"其他人可能会试图卖掉你

现在，您会发现很多答案、博客文章、视频或其他资源都告诉您对收藏使用Set 而不是List.>

这是一个糟糕的建议.不要那样做！

使用 Sets 而不是 Lists 会使 MultipleBagFetchException 消失，但笛卡尔积仍然存在，实际上更糟，因为您会在应用此修复"很久之后发现性能问题.

正确的解决方案

您可以执行以下操作:

Listpost = entityManager.createQuery(""";选择不同的 p来自邮政左连接获取 p.comments其中 :minId 和 :maxId 之间的 p.id""", Post.class).setParameter(minId", 1L).setParameter("maxId", 50L).setHint(QueryHints.PASS_DISTINCT_THROUGH，假).getResultList();post = entityManager.createQuery(""";选择不同的 p来自邮政左连接获取 p.tags t其中 p 在 :posts""", Post.class).setParameter(帖子", 帖子).setHint(QueryHints.PASS_DISTINCT_THROUGH，假).getResultList();

<块引用>

在第一个 JPQL 查询中，distinct 不会转到 SQL 语句.这就是我们将 PASS_DISTINCT_THROUGH JPA 查询提示设置为 false 的原因.

DISTINCT 在 JPQL 中有两个含义，在这里，我们需要它在 Java 端，而不是 SQL 端对 getResultList 返回的 Java 对象引用进行去重.

只要您使用 JOIN FETCH 最多获取一个集合，就可以了.

通过使用多个查询，您将避免笛卡尔积，因为任何其他集合都是使用辅助查询获取的.

始终避免使用 FetchType.EAGER 策略

如果您在映射 @OneToMany 或 @ManyToMany 关联时使用 FetchType.EAGER 策略，那么您可以轻松结束MultipleBagFetchException.

最好从 FetchType.EAGER 切换到 Fetchype.LAZY，因为急切获取是一个糟糕的想法，会导致关键的应用程序性能问题.

结论

避免 FetchType.EAGER 并且不要从 List 切换到 Set 仅仅因为这样做会使 Hibernate 隐藏 MultipleBagFetchException 在地毯下.一次只取一个集合，你会没事的.

只要您使用与要初始化的集合相同数量的查询来执行此操作，就可以了.只是不要在循环中初始化集合，因为这会触发 N+1 查询问题，这也对性能不利.

I have below entities:

public class Category {
   private Integer id;

   @OneToMany(mappedBy = "parent")
   private List<Topic> topics;
}

public class Topic {
   private Integer id;

   @OneToMany(mappedBy = "parent")
   private List<Posts> posts;

   @ManyToOne
   @JoinColumn(name = "id")
   private Category parent;
}

public class Post {
   private Integer id;

   @ManyToOne
   @JoinColumn(name = "id")
   private Topic parent;
   /* Post fields */
}

and I want to fetch all categories with joined topics and joined posts using JPQL query. I wrote query like below:

SELECT c FROM Category c
JOIN FETCH c.topics t
JOIN FETCH t.posts p WHERE 

But I got the error

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags

I found articles about this error, but these articles only describe situation where in one entity are two collections to join. My problem is a little different and I don't know how to solve it.

It is possible to do it in one query?

解决方案

Considering we have the following entities:

And, you want to fetch some parent Post entities along with all the associated comments and tags collections.

If you are using more than one JOIN FETCH directives:

List<Post> posts = entityManager.createQuery("""
    select p
    from Post p
    left join fetch p.comments
    left join fetch p.tags
    where p.id between :minId and :maxId
    """, Post.class)
.setParameter("minId", 1L)
.setParameter("maxId", 50L)
.getResultList();

Hibernate will throw the MultipleBagFetchException:

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags [
  com.vladmihalcea.book.hpjp.hibernate.fetching.Post.comments,
  com.vladmihalcea.book.hpjp.hibernate.fetching.Post.tags
]

The reason why Hibernate throws this exception is that it does not allow fetching more than one bag because that would generate a Cartesian product.

The worst "solution" others might try to sell you

Now, you will find lots of answers, blog posts, videos, or other resources telling you to use a Set instead of a List for your collections.

That's terrible advice. Don't do that!

Using Sets instead of Lists will make the MultipleBagFetchException go away, but the Cartesian Product will still be there, which is actually even worse, as you'll find out the performance issue long after you applied this "fix".

The proper solution

You can do the following trick:

List<Post> posts = entityManager.createQuery("""
    select distinct p
    from Post p
    left join fetch p.comments
    where p.id between :minId and :maxId
    """, Post.class)
.setParameter("minId", 1L)
.setParameter("maxId", 50L)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();

posts = entityManager.createQuery("""
    select distinct p
    from Post p
    left join fetch p.tags t
    where p in :posts
    """, Post.class)
.setParameter("posts", posts)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();

In the first JPQL query, distinct DOES NOT go to the SQL statement. That's why we set the PASS_DISTINCT_THROUGH JPA query hint to false.

DISTINCT has two meanings in JPQL, and here, we need it to deduplicate the Java object references returned by getResultList on the Java side, not the SQL side.

As long as you fetch at most one collection using JOIN FETCH, you will be fine.

By using multiple queries, you will avoid the Cartesian Product since any other collection but the first one is fetched using a secondary query.

Always avoid the FetchType.EAGER strategy

If you're using the FetchType.EAGER strategy at mapping time for @OneToMany or @ManyToMany associations, then you could easily end up with a MultipleBagFetchException.

You are better off switching from FetchType.EAGER to Fetchype.LAZY since eager fetching is a terrible idea that can lead to critical application performance issues.

Conclusion

Avoid FetchType.EAGER and don't switch from List to Set just because doing so will make Hibernate hide the MultipleBagFetchException under the carpet. Fetch just one collection at a time, and you'll be fine.

As long as you do it with the same number of queries as you have collections to initialize, you are fine. Just don't initialize the collections in a loop, as that will trigger N+1 query issues, which are also bad for performance.

这篇关于如何在一个 JPQL 查询中使用多个 JOIN FETCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！