英文:
Can Hibernate map the result set of a 1:M join in a subselect to a parent with a child collection?
问题
我正在尝试将SQL左连接的结果映射到一个对象结构，形式为parent -> [child]，其中SQL查询上的条件将结果限制为一个父项。结果集包含n行，因为有n个子项，但是当然每行只属于单个父项。
我正在使用Hibernate，并且在我的“parent”实体上放置了一个@Subselect，其中包含了整个左连接查询。
@Subselect("SELECT x.*, y.* FROM x INNER JOIN y on x.a = y.a WHERE x.a = 1")
public class X {
... X 属性
private List<Y>;
}
public class Y {
... Y 属性
}
我如何指示Hibernate将结果集左侧的列“折叠”为单个父对象，并将每行的剩余列合并为许多“child”实例，这些实例添加到父对象的列表中？
这种操作是否可行，还是我需要使用Hibernate的@OneToMany注解进行连接？虽然这对我有效，但会导致两次单独的数据库调用，我觉得这样效率不高。
英文:
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.
答案1
得分: 0
这可以通过使用JPQL（如果您正在使用Hibernate，则为HQL）和'join fetch'命令来实现：
SELECT a from Author a left join fetch a.books;
尝试以这种方式执行多个连接可能会产生'MultipleBagFetchException'。虽然可以通过某种方式解决这个问题，但无论如何，通过将连接（尤其是多个连接）发送到数据库服务器，我们会产生一个可能会变得非常大的笛卡尔积结果集。
令人反直觉的是，通过多次与数据库进行交互（使用批处理来减轻N + 1问题），然后在内存中使用ORM连接父项和子项，实际上可能更有效。
感谢https://stackoverflow.com/users/1025118/vlad-mihalcea 在网络上分享了许多关于这个主题的指导，特别是他博客上的这篇文章 - https://stackoverflow.com/users/1025118/vlad-mihalcea
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论