How to left join fetch multiple children in Hibernate?

hibernate hql fetch
Bjorn Rombaut picture Bjorn Rombaut · Mar 7, 2012 · Viewed 40.4k times · Source

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

JB Nizet picture JB Nizet · Mar 7, 2012

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;

Related questions

What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

Please help me understand where to use a regular JOIN and where a JOIN FETCH. For example, if we have these two queries FROM Employee emp JOIN emp.department dep and FROM Employee emp JOIN FETCH emp.department dep Is …

Read More
Why does Hibernate execute multiple SELECT queries instead of one when using @Fetch(FetchMode.JOIN)

I've got the following query which I expect to run in a single select request: @NamedQuery(name=Game.GET_GAME_BY_ID1, query = "SELECT g FROM Game g " + "JOIN FETCH g.team1 t1 " + "JOIN FETCH t1.players p1 " + "JOIN FETCH …

Read More
Hibernate: Overriding mapping's EAGER in HQL?

It's possible to override LAZY in HQL using LEFT JOIN FETCH. FROM Obj AS obj LEFT JOIN FETCH obj.otherObj WHERE obj.id = :id Is it also possible to override EAGER? How?

Read More