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

问题描述

我有以下实体:

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 */
}

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

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 

但是我得到了错误

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:

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

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

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

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();

休眠将抛出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
]

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

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.

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

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.

那是可怕的建议.不要那样做！

使用Sets代替Lists将使MultipleBagFetchException消失，但笛卡尔乘积仍将存在，实际上甚至更糟，因为应用此方法很长时间后您就会发现性能问题修复".

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".

您可以执行以下技巧:

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();

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

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在JPQL中有两个含义，在这里，我们需要它在Java端而不是SQL端对getResultList返回的Java对象引用进行重复数据删除.查看本文以了解更多详细信息.

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. Check out this article for more details.

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

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.

如果在映射时为@OneToMany或@ManyToMany关联使用FetchType.EAGER策略，那么您很容易以MultipleBagFetchException结尾.

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.

最好从FetchType.EAGER切换到Fetchype.LAZY，因为渴望获取是

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.

避免使用FetchType.EAGER，并且不要从List切换到Set，因为这样做会使Hibernate将MultipleBagFetchException隐藏在地毯下.一次只获取一个集合，就可以了.

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屋！