57

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?

SternK
  • 11,649
  • 22
  • 32
  • 46
Zaprogramowany
  • 623
  • 1
  • 7
  • 10

3 Answers3

95

Considering we have the following entities:

JPA entity domain model

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 1
    In end of this article you were said: "Although it’s always better to simply use indexed Lists or Sets". How to create indexed list? – Zaprogramowany May 08 '15 at 10:03
  • This query gives me the following error, but still runs: `JOIN FETCH expressions cannot be defined with an identification variable.` – K.Nicholas Aug 24 '18 at 22:09
  • There's just one problem, your query returns a post and no category. – Reza P. Mar 15 '19 at 11:44
  • @VladMihalcea slightly unrelated - but what is the advantage of using `join fetch` vs `eager` when you join fetch all composite members? – ACV Sep 24 '20 at 19:34
  • Hi @Vlad. If i want to get one `post`, how can i tell hibernate to get the `post` and it's `comments` and then update the `post` by getting it's `tags`? – Arash May 12 '22 at 18:27
  • 1
    Why isnt the first result overridden? Answer: It is, but because of the additional in check in the 2nd query this works. – ABC Nov 18 '22 at 13:04
  • 1
    @ABC Because it's not overridden. It's merged. – Vlad Mihalcea Nov 18 '22 at 13:46
  • still have the problem if we get only one post? – robert trudel Mar 25 '23 at 04:10
  • 1
    @roberttrudel Yes, of course. If you have a single `Post` that has N collections that have other M collections that you `JOIN FETCH`, you'd still get an N x M Cartesian Product. – Vlad Mihalcea Mar 25 '23 at 06:40
  • Hello - i wanted to do the exact thing using LAZY fetching of the -toMany-side. That is, fetching one top level element (post) and get the toMany-relations (tags, comments,...) upon accessing the collection during post-processing. (Reason: fetching the whole tree yields a 3.2M rows result-set). Is there a simple full-code example available to inspect, since i fear our DOM is not properly designed? – DocJones Apr 24 '23 at 14:00
3

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);
Govind Singh
  • 15,282
  • 14
  • 72
  • 106
wangf
  • 895
  • 9
  • 12
0

A workaround is to use @Query and @EntityGraph together, like it mentioned here use @Query and @EntityGraph together

Community
  • 1
  • 1
koly
  • 501
  • 1
  • 5
  • 12