Menu
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?
976
What is Left Join? The Left Join is a keyword in SQL, which returns all data from the left-hand side table and matching records from the right-hand side table. We can see the NULL values from the right side if there is no match.
JOIN FETCH (or LEFT JOIN FETCH ) will collect all the associations along with their owner object. Meaning that the collection will be retrieved in the same select. This can be shown by enabling Hibernate's statistics. A (left/outer) join fetch is great for *ToOne (many-to-one or one-to-one) associations.
JPA + Hibernate - JPQL FETCH JOIN Query example. The 'FETCH' option can be used on a JOIN (either INNER JOIN or LEFT JOIN) to fetch the related entities in a single query instead of additional queries for each access of the object's lazy relationships.
The Left Join is a keyword in SQL, which returns all data from the left-hand side table and matching records from the right-hand side table. We can see the NULL values from the right side if there is no match. We can apply the Joins in Hibernate by using the HQL query or native SQL query.
Only one 'select' statement for the main query was executed, hence the lazy employee#tasks relations were loaded at the same time. The FETCH option can also be used with 'LEFT JOIN'. As we have seen in our last tutorial the only difference with LEFT JOIN is, it will also return those left entity instances (employees) which have no related tasks.
JPQL FETCH JOIN. The 'FETCH' option can be used on a JOIN (either INNER JOIN or LEFT JOIN) to fetch the related entities in a single query instead of additional queries for each access of the object's lazy relationships.
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;
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
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
