一对多关系在不使用“distinct"的情况下获取重复的对象.为什么? [英] One-To-Many relationship gets duplicate objects without using "distinct". Why?

问题描述

我有 2 个类处于一对多关系和一个有点奇怪的 HQL 查询.即使我已经阅读了一些已经发布的问题，我似乎也不太清楚.

班级部{@OneToMany(fetch=FetchType.EAGER，mappedBy="部门")设置<员工>雇员;}班级员工{@ManyToOne@JoinColumn(name="id_department")部门部门；}

当我使用以下查询时，我得到重复的 Department 对象:

session.createQuery("select dep from Department as dep left join dep.employees");

因此，我必须使用不同的:

session.createQuery("从部门中选择不同的dep作为dep left join dep.employees");

这种行为是否符合预期?我认为这很不寻常，因为它与 SQL 进行了比较.

解决方案

此问题在 休眠常见问题:

<块引用>

首先，您需要了解 SQL 以及 OUTER JOIN 在 SQL 中的工作原理.如果你不完全理解和理解 SQL 中的外连接，不要继续阅读此常见问题解答项目，但请参阅 SQL 手册或教程.否则你将无法理解下面的解释并且你会在 Hibernate 论坛上抱怨这种行为.典型的可能返回同一订单的重复引用的示例对象:

List result = session.createCriteria(Order.class).setFetchMode("lineItems", FetchMode.JOIN).列表();<class name="订单"><set name="lineItems" fetch="join">...</class>列表结果 = session.createCriteria(Order.class).列表();

<小时>

List result = session.createQuery("select o from Order o left join fetch o.lineItems").list();

<块引用>

所有这些示例都生成相同的 SQL 语句:

SELECT o.*, l.* from ORDER o LEFT OUTER JOIN LINE_ITEMS l ON o.ID = l.ORDER_ID

<块引用>

想知道为什么会有重复项吗?看SQL结果集，Hibernate 不会在左侧隐藏这些重复项外部连接的结果，但返回所有重复的驾驶台.如果您在数据库中有 5 个订单，并且每个订单有 3 个行项目，结果集将为 15 行.Java 结果列表其中的查询将有 15 个元素，所有元素都是 Order 类型.只有 5订单实例将由 Hibernate 创建，但SQL 结果集保留为对这 5 个的重复引用实例.如果你不明白这最后一句话，你需要阅读 Java 以及 Java 上的实例之间的区别堆和对此类实例的引用.(为什么是左外连接?如果你会有一个没有订单项的额外订单，结果集将是 16 行 NULL 填充右侧，该行项目数据用于其他订单.你想要订单，即使他们没有订单项，对吗?如果没有，请在您的 HQL 中使用内部连接获取).
默认情况下，Hibernate 不会过滤掉这些重复的引用.有些人(不是你)实际上想要这个.你怎么能过滤掉它们?像这样:

Collection result = new LinkedHashSet( session.create*(...).list() );

LinkedHashSet 过滤掉重复的引用(它是一个集合)和它保留插入顺序(结果中元素的顺序).那太容易了，所以你可以在许多不同的和更困难的情况下做到方法:

List result = session.createCriteria(Order.class).setFetchMode("lineItems", FetchMode.JOIN).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).列表();<class name="订单">...<set name="lineItems" fetch="join">列表结果 = session.createCriteria(Order.class).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).列表();List result = session.createQuery("select o from Order o left join fetch o.lineItems").setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)//是的，真的！.列表();List result = session.createQuery("select distinct o from Order o left join fetch o.lineItems").list();

<块引用>

最后一个很特别.看起来您正在使用 SQL此处为 DISTINCT 关键字.当然，这不是SQL，这是HQL.这个在这种情况下，distinct 只是结果转换器的快捷方式.是的，在其他情况下，HQL distinct 将直接转换为 SQL清楚的.不是在这种情况下:您不能在SQL 级别，产品/连接的本质禁止这样做 - 你想要重复或您没有获得所需的所有数据.所有这些重复过滤发生在内存中，当结果集是编组为对象.也应该很明显为什么结果集基于行的限制"操作，例如 setFirstResult(5) 和setMaxResults(10) 不适用于此类急切获取查询.如果你将结果集限制为一定数量的行，你就会切断数据随机.有一天 Hibernate 可能足够聪明，知道如果你调用 setFirstResult() 或 setMaxResults() 它不应该使用连接，但是第二个 SQL SELECT.试试看，你的 Hibernate 版本可能已经够聪明了.如果没有，写两个查询，一个用于限制东西，另一个用于渴望获取.你想知道为什么带有 Criteria 查询的示例没有忽略 fetch="join"在映射中设置但 HQL 不在乎?阅读下一个常见问题解答.

I have 2 classes in a one-to-many relationship and a HQL query that is a bit strange. Even if I have read some questions already posted, it does not seem clear to me.

Class Department{
   @OneToMany(fetch=FetchType.EAGER, mappedBy="department")
   Set<Employee> employees;
}
Class Employee{
   @ManyToOne
   @JoinColumn(name="id_department")
   Department department;
}

When I use the following query I get duplicates Department objects:

session.createQuery("select dep from Department as dep left join dep.employees");

Thus, I have to use distinct:

session.createQuery("select distinct dep from Department as dep left join dep.employees");

Is this behaviour an expected one? I consider this unusual, as comparing it with SQL.

解决方案

This question is thoroughly explained on Hibernate FAQ:

First, you need to understand SQL and how OUTER JOINs work in SQL. If you do not fully understand and comprehend outer joins in SQL, do not continue reading this FAQ item but consult a SQL manual or tutorial. Otherwise you will not understand the following explanation and you will complain about this behavior on the Hibernate forum. Typical examples that might return duplicate references of the same Order object:

List result = session.createCriteria(Order.class)  
                        .setFetchMode("lineItems", FetchMode.JOIN)  
                        .list();

<class name="Order">           
    <set name="lineItems" fetch="join">
    ...
</class>

List result = session.createCriteria(Order.class)  
                        .list();  

---

List result = session.createQuery("select o from Order o left join fetch o.lineItems").list();  

All of these examples produce the same SQL statement:

SELECT o.*, l.* from ORDER o LEFT OUTER JOIN LINE_ITEMS l ON o.ID = l.ORDER_ID   

Want to know why the duplicates are there? Look at the SQL resultset, Hibernate does not hide these duplicates on the left side of the outer joined result but returns all the duplicates of the driving table. If you have 5 orders in the database, and each order has 3 line items, the resultset will be 15 rows. The Java result list of these queries will have 15 elements, all of type Order. Only 5 Order instances will be created by Hibernate, but duplicates of the SQL resultset are preserved as duplicate references to these 5 instances. If you do not understand this last sentence, you need to read up on Java and the difference between an instance on the Java heap and a reference to such an instance. (Why a left outer join? If you'd have an additional order with no line items, the result set would be 16 rows with NULL filling up the right side, where the line item data is for other order. You want orders even if they don't have line items, right? If not, use an inner join fetch in your HQL).
Hibernate does not filter out these duplicate references by default. Some people (not you) actually want this. How can you filter them out? Like this:

Collection result = new LinkedHashSet( session.create*(...).list() );  

A LinkedHashSet filteres out duplicate references (it's a set) and it preserves insertion order (order of elements in your result). That was too easy, so you can do it in many different and more difficult ways:

List result = session.createCriteria(Order.class)  
                        .setFetchMode("lineItems", FetchMode.JOIN)  
                        .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)  
                        .list();  


<class name="Order">  
    ...  
    <set name="lineItems" fetch="join">  

List result = session.createCriteria(Order.class)  
                        .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)  
                        .list();  

List result = session.createQuery("select o from Order o left join fetch o.lineItems")  
                      .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) // Yes, really!  
                      .list();  

List result = session.createQuery("select distinct o from Order o left join fetch o.lineItems").list();       

The last one is special. It looks like you are using the SQL DISTINCT keyword here. Of course, this is not SQL, this is HQL. This distinct is just a shortcut for the result transformer, in this case. Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: you can not filter out duplicates at the SQL level, the very nature of a product/join forbids this - you want the duplicates or you don't get all the data you need. All of this filtering of duplicates happens in-memory, when the resultset is marshalled into objects. It should be also obvious why resultset row-based "limit" operations, such as setFirstResult(5) and setMaxResults(10) do not work with these kind of eager fetch queries. If you limit the resultset to a certain number of rows, you cut off data randomly. One day Hibernate might be smart enough to know that if you call setFirstResult() or setMaxResults() it should not use a join, but a second SQL SELECT. Try it, your version of Hibernate might already be smart enough. If not, write two queries, one for limiting stuff, the other for eager fetching. Do you want to know why the example with the Criteria query did not ignore the fetch="join" setting in the mapping but HQL didn't care? Read the next FAQ item.

这篇关于一对多关系在不使用“distinct"的情况下获取重复的对象.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！