MultipleBagFetchException thrown by Hibernate

Question

I want to have an option in my repository layer to eager load entites, so I tried adding a method that should eager load a question entity with all the relationships, but it throws MultipleBagFetchException. How can I fix this? I am using Hibernate 4.16.

@NamedQuery(name = Question.FIND_BY_ID_EAGER, query = "SELECT q FROM Question q LEFT JOIN FETCH q.answers LEFT JOIN FETCH q.categories LEFT JOIN FETCH q.feedback LEFT JOIN FETCH q.participant WHERE q.id = :id"),

How do I get a question object which is initially lazy loaded, to be eager loaded with all relations?

Solution 1

This is a rather nasty problem in Hibernate and actually ORM in general.

What happens is that the many (fetch) joins cause a rather large cartesian product to be created. I.e for ever other join new columns and new rows appear in the result, leading to a (fairly) large 'square' result.

Hibernate needs to distill a graph from this table, but it's not smart enough to match the right columns to the right entities.

E.g.

Suppose we have the result

A B C
A B D

Which needs to become:

 A
 |
 B
 /\
C  D

Hibernate could deduct from the primary keys and some encoding magic, what the graph must be, but in practice it needs explicit help to pull this off.

One way to do this is by specifying the Hibernate specific @IndexColumn or the JPA standard @OrderColumn on the relations.

E.g.

@Entity
public class Question {


    @ManyToMany
    @JoinTable(
        name = "question_to_answer",
        joinColumns = @JoinColumn(name = "question_id"),
        inverseJoinColumns = @JoinColumn(name = "answer_id")
    )
    @IndexColumn(name = "answer_order")
    private List<Answer> answers;

    // ...
}

In this example I'm using a join table, with an extra column answer_order. Via this column, which has a unique sequential number per Question/Answer relation, Hibernate can distinguish the entries in the result table and create the required Object graph.

One note btw, if it concerns more than a few entities, using so many eager joins can potentially lead to a much larger result set than you might think based on the number of entities involved.

Further reading:

• Hibernate Exception - Simultaneously Fetch Multiple Bags 1
• Hibernate Exception - Simultaneously Fetch Multiple Bags 2
• Solving simultaneously fetch multiple bags - Using the @IndexColumn

Solution 2

Hibernate doesn't allow fetching more than one bag because that would generate a Cartesian product, and for unordered Lists, which are called baggs in Hibernate terminology, this will cause duplicate entries even if the underlying collection does not have those duplicated rows. So, Hibernate simply prevents this situation when the JPQL query is compiled.

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.

The right fix

Instead of using multiple JOIN FETCH in a single JPQL or Criteria API query:

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

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

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.

Comments

[JRSofty]

It should be noted that @IndexColumn is now deprecated (currently using Hibernate 5.2.4.Final)

[qwazer]

Use @OrderColumn since JPA 2.0

[Nero]

I tried this solution but the resulting posts only contains tags(because this is fetched last) but not comments. Comments is lazily loaded. Trying to access posts.get(0).getComments().size() throws LazyIntializationEXception

[Vlad Mihalcea]

All the code is on GitHub and worked like a charm.

[Nero]

Yep it worked, I had to add @Transactional on my method. In your example above, if I used Set<Post> posts instead of list and removed the query hint and distinct keyword and ran 2 queries just like above, would I essentially get the same result?

[qleoz12]

hi,For me hapends the same , i get outer query for each query, I'm using spring and I must to create a bean for get the entityFactory and use '@persistenceContext' and I marked the method with the anotation with '@transactional' for to do the query of the 'bag' or relation i wanted to fetch, is there someone easy way to do ? in spring