Java 一对多关系在不使用“distinct”的情况下获取重复对象。为什么？

One-To-Many relationship gets duplicate objects without using "distinct". Why?

提问by herti

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.

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

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:

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

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

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");

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.

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

采纳答案by gerrytan

This question is thoroughly explained on Hibernate FAQ:

这个问题在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:

首先，您需要了解 SQL 以及 OUTER JOIN 在 SQL 中的工作方式。如果您不完全理解和理解 SQL 中的外连接，请不要继续阅读此常见问题解答项目，而是查阅 SQL 手册或教程。否则你不会理解下面的解释，你会在 Hibernate 论坛上抱怨这种行为。可能返回相同 Order 对象的重复引用的典型示例：

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:

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

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:

想知道为什么会有重复吗？看SQL结果集，Hibernate并没有在外连接结果的左侧隐藏这些重复项，而是返回了驱动表的所有重复项。如果数据库中有 5 个订单，每个订单有 3 个行项目，则结果集将为 15 行。这些查询的 Java 结果列表将有 15 个元素，所有元素都是 Order 类型。Hibernate 只会创建 5 个 Order 实例，但 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)  
                        .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.

最后一个很特别。看起来您在这里使用了 SQL DISTINCT 关键字。当然，这不是SQL，这是HQL。在这种情况下，这种不同只是结果转换器的捷径。是的，在其他情况下，HQL distinct 将直接转换为 SQL DISTINCT。不是在这种情况下：您不能在 SQL 级别过滤掉重复项，产品/联接的本质禁止这样做 - 您想要重复项或者您没有获得所需的所有数据。当结果集被编组为对象时，所有这些重复过滤都发生在内存中。为什么基于结果集行的“限制”操作（例如 setFirstResult(5) 和 setMaxResults(10)）不适用于这些类型的急切获取查询也应该是显而易见的。如果您将结果集限制为一定数量的行，你随机切断数据。有一天 Hibernate 可能足够聪明，知道如果你调用 setFirstResult() 或 setMaxResults() 它不应该使用连接，而是第二个 SQL SELECT。试试吧，你的 Hibernate 版本可能已经足够智能了。如果没有，请编写两个查询，一个用于限制内容，另一个用于预先获取。您想知道为什么带有 Criteria 查询的示例没有忽略映射中的 fetch="join" 设置但 HQL 不在乎吗？阅读下一个常见问题解答项目。您想知道为什么带有 Criteria 查询的示例没有忽略映射中的 fetch="join" 设置但 HQL 不在乎吗？阅读下一个常见问题解答项目。您想知道为什么带有 Criteria 查询的示例没有忽略映射中的 fetch="join" 设置但 HQL 不在乎吗？阅读下一个常见问题解答项目。

回答by Jayram Kumar

Use the result transformer Criteria.DISTINCT_ROOT_ENTITY:

使用结果转换器Criteria.DISTINCT_ROOT_ENTITY：

List result = session.createQuery("hql query")  
                        .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)  
                        .list();