How to left join fetch multiple children in Hibernate?

Question

I'm working with hibernate and I'm having troubles creating an hql query that fetches all the children of my object.

For example: The Object User has a list of Cars and a list of Friends.

To get a user with his cars I would use following query:

from User u left join fetch u.cars where u.id = ?

This works fine, so I thought it would be easy to get a user with his cars and with his friends with following query:

from User u left join fetch u.cars left join fetch u.friends where u.id = ?

But this gives me following error:

HibernateException: cannot simultaneously fetch multiple bags

Now my question is: what is the right way to fetch multiple children in hibernate?

Duplicate : http://stackoverflow.com/questions/4334970/hibernate-cannot-simultaneously-fetch-multiple-bags. With Hibernate Use : @LazyCollection(LazyCollectionOption.FALSE) — willome, Aug 29 '16 at 13:40
Possible duplicate of [Hibernate cannot simultaneously fetch multiple bags](https://stackoverflow.com/questions/4334970/hibernate-cannot-simultaneously-fetch-multiple-bags) — Dherik, Dec 19 '18 at 22:05

score 16 · Answer 1 · answered Mar 07 '12 at 19:37

16

At most one of the children collection must be a bag (i.e. declared as a List). Declare the other collections as Sets, and it will work.

Beware, though, that doing such fetch joins makes a cartesiann product of the rows. If both collections have 100 elements, such a query retrieves 10,000 rows fro the database. It's sometimes more efficient to execute a first query which fetches one collection, and a second one which fetches the other (which thus reduces the number of rows retrieved to 200). This is also a way to avoid the problem you have:

select u from User u left join fetch u.cars where u.id = :id;
select u from User u left join fetch u.friends where u.id = :id;

answered Mar 07 '12 at 19:37

JB Nizet

678,734
91
1,224
1,255

1

Nice to know. It's not intuitively obvious that the scond query will actually populate the friends collection on the same user entities loaded by the first query. But that's how it works (thanks to cache)! – Pierre Henry Jan 15 '13 at 09:09
1

Can this two HSQL queries be written inside one `@NamedQuery`? – WelcomeTo Nov 07 '13 at 11:14
@MyTitle: no. A named query is a single query, not two. – JB Nizet Nov 07 '13 at 14:22
How can I execute 2 queries with JPA Criteria? I've tried but I couldn't find any example. I need to implement your solution because I can't change from List to Set, or any other alternative to avoid this. – Pablo Jan 04 '15 at 09:22
@Pablo You execute one query, then you execute the other. I'm sure you should find an example showing to execute one criteria query easily. – JB Nizet Jan 04 '15 at 09:26

score 10 · Answer 2 · edited Sep 28 '16 at 09:54

You just hit the Collection/List (bag) issue.

Here is a link to an Hibernate official "issue" about this: https://hibernate.atlassian.net/browse/HHH-1718. As you can see, it has been open in 2006 and is still open.

In addition to what JB Nizet propose, I suggest you use Set instead of Collection or List in your model (if you can), otherwise, you can also specify your Collection/List as FetchMode.SUBSELECT and as a last option (painful to implement), you can use @IndexColumn on your @OneToMany/@ManyToMany.

This blog article will guide you into implementing solutions: http://jroller.com/eyallupu/entry/hibernate_exception_simultaneously_fetch_multiple

In other words, there is no solution to do exactly what you want to do, except workarounds.

Hope this helps!

Edit: typo

score 0 · Answer 3 · edited Feb 05 '15 at 16:02

Not possible as it would be too heavy for the application/database, you need to create 2 separate criteria and retrieve data separately.

Cat cat = sess.createCriteria(Cat.class)
              .add(Restrictions.like("name", "F%"))
              .uniqueResult();

List kitten = sess.createCriteria(Kitten.class)
                  .add(Restrictions.eq("cat", cat))
                  .createCriteria("kittens")
                  .add(Restrictions.like("name", "F%"))
                  .list();

List mate = sess.createCriteria(Mate.class)
                .add(Restrictions.eq("cat", cat))
                .createCriteria("mate")
                .add(Restrictions.like("name", "F%"))
                .list();

score 0 · Answer 4 · answered Mar 06 '23 at 04:08

If you want to fetch deeper objects, you can try

from User u left join u.friends f left join fetch f.kids k where u.id = :id;
select u from User u left join fetch u.friends where u.id = :id;

In this you are fetching the deeper level children first and then fetch the main objects.

Note: The first query that fetches the deep nested children has no select.

How to left join fetch multiple children in Hibernate?

4 Answers4

Linked