Hibernate fetch join -> cannot fetch multiple bags

Question

Problem is i have two bags in my entity which i would like to display in my jsf frontend (Spring in the back so no lazy loading). So i have to eagerly fetch them to display the information in a list like this:

Point 1 (Label 1, Label 2) (Tag1 ... Tag n)
Point 2 (Label 3, Label 4) (Tag1 ... Tag n)

Putting both Lists to eager didn't work. So i tried my luck with a fetch join. It allowed me to fetch one list, but when i added the second list i get the known "cannot fetch multiple bags" error.

Can Hibernate handle two fetch joins in a query?

public class PointOfInterest
 @OneToMany(mappedBy="poi")
private List<PointOfInterestLabel> labels = new ArrayList<PointOfInterestLabel>();

@ManyToMany
private List<Tag> tags = new ArrayList<Tag>();

My fetch join:

SELECT DISTINCT p from PointOfInterest p 
        left join fetch p.labels 
        left join fetch p.tags WHERE p.figure = :figure

On startup the creation of my hibernate factory fails with:

Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
    at org.hibernate.loader.BasicLoader.postInstantiate(BasicLoader.java:94)
    at org.hibernate.loader.hql.QueryLoader.<init>(QueryLoader.java:123)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:98)
    at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:557)
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:422)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1385)
    at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:954)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:883)
    ... 55 more

Stefan Steinegger · Accepted Answer · 2011-08-24T14:46:05.017

6

The answer is: no. It can't handle it. That's what it says.

For value types (composite-element) it wouldn't even work, because you don't get the information what actually belongs to the same bag item.

Usually it doesn't even make sense. If you query a table and get 10 records in the starting table, 10 in the first bag and another 10 in the second bag, you'll retrieve 1000 records just to create these 30 objects in memory. Imagine the number of records when there would be 100 records in each table (hint: 1,000,000 instead of 300) and when you fetch join another bag (hint: 100,000,000 instead of 400) ...

By the way: join fetch may lead to strange effects and problems and should be avoided, except you exactly know what you are doing.

edited Aug 24 '11 at 14:46

answered Aug 24 '11 at 14:41

Stefan Steinegger

63,782
15
129
193

Thanks! For the moment i replaced it with Set, but i think i will refactor the interface tomorrow and display fewer information. Thanks for the feedback! How would you solve it when you have no persistence scope in your frontend. Use Data Transfer Objects built in the DAO instead of fetched entities? I inherited the project and i am not allowed to make huge design changes, nor am i allowed to switch it to j2ee. :-/ – mkuff Aug 24 '11 at 14:58
You could turn off lazy loading, which is bad for performance. This fetches the bags immediately by separate queries. You could also access the bags (eg. size) to force loading. The perfect solution is to create the session outside the dao and keep it for a whole business transaction. (Creating a session in each call to the dao is called session-per-call and is an anti-pattern.) – Stefan Steinegger Aug 25 '11 at 10:00
So really this is a catch 22 problem: if you don't use join fetch, you get n+1 problem or lazy initialization exception. Session outside dao is a hack, first of all because it is nowhere near the JPA contract and second of all because it is hardly controllable, since you shouldn't make any assumptions on the lifetime of an entity at the moment where you create it. It might span over several requests, not necessarily http. – Deroude Aug 15 '16 at 12:06
You can avoid N+1 with the great batch-fetching feature, which is transparent and doesn't have any side effects. You don't need to reference the session from outside the dal, but you have to control the life time of the transaction from outside of the dal, because it is longer than a single call to the db. You can hide it behind a dal interface. In our project, we've implemented a environment transaction thing, which is stored in a thread static field in the dal. – Stefan Steinegger Aug 22 '16 at 09:13

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

Instead of using Set, you can split the query and load the entities in different queries.
Eg

    From PointOfInterest p left join fetch p.labels WHERE p.figure = :figure
    From PointOfInterest p left join fetch p.tags WHERE p.figure = :figure

Please refer the link

edited May 23 '17 at 10:28

Community

1
1

answered Sep 04 '12 at 09:51

Manu

3,467
3
29
28

score 0 · Answer 3 · answered Nov 14 '18 at 22:48

You can't have 2 1-n eager-joins (or HQL fetch-joins) in the same query.

If you join to a 1-n table and there are 10 rows matches, all the common columns on the non-N entities are duplicated for each row.

If it returns 10 rows, it would have to decide which 1-n caused the extra rows. Which, technically, it could based on the join keys - if the join keys are unique primary keys. But this exception indicates that hibernate cannot tell.

Hibernate fetch join -> cannot fetch multiple bags

3 Answers3