How to left join fetch multiple children in Hibern

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?

Answer 1

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

Answer 2

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;

Answer 3

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