I am attempting to map the result of a SQL left join to an object structure of the form parent -> [child], where a condition on the SQL query restricts the result to one parent. The result set contains n rows because there are n children, but of course each row only belongs to the single parent.
I'm using Hibernate, and have placed a @Subselect on my 'parent' entity, and this contains my entire left join query.
@Subselect("SELECT x.*, y.* FROM x INNER JOIN y on x.a = y.a WHERE x.a = 1")
public class X {
... X attributes
private List<Y>;
}
public class Y {
... Y attributes
}
How can I instruct Hibernate to 'collapse' the columns on the left side of the result set to a single parent object, and coalesce the remaining columns from each row into many instances of 'child' which are added to the list on the parent?
Is this possible, or do I need to join using Hibernates' @OneToMany annotation. This does work for me, but results in two separate calls to the database, which I feel is inefficient.
This can be achieved using JPQL (or HQL if you are using Hibernate) and the 'join fetch' command :
SELECT a from Author a left join fetch a.books;
Attempting to perform multiple joins in this manner can produce a 'MultipleBagFetchException'. This can be worked around, but in any case, by farming the joins (especially several joins) off to the DB server, we produce a cartesian product result set which can grow very large.
Counterintuitively, it can actually be more efficient to make multiple round trips to the database (using batching to mitigate the N + 1) problem, and then join the parent and children in memory using ORM.
Thanks to https://stackoverflow.com/users/1025118/vlad-mihalcea for many pointers on this topic across the web, in particular this article on his blog - https://stackoverflow.com/users/1025118/vlad-mihalcea