How to use multiple JOIN FETCH in one JPQL query

Question

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?

Answer 1

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.

Answer 2

Here is a working example of complex join and multiple consition:

    String query_findByProductDepartmentHospital = "select location from ProductInstallLocation location "
            + " join location.product prod " + " join location.department dep "
            + " join location.department.hospital hos " + " where  prod.name = :product "
            + " and dep.name.name = :department " + " and hos.name = :hospital ";

    @Query(query_findByProductDepartmentHospital)
    ProductInstallLocation findByProductDepartmentHospital(@Param("product") String productName,@Param("department") String departName, @Param("hospital") String hospitalName);