一对多关系无需使用"distinct"即可获得重复的对象.为什么? [英] One-To-Many relationship gets duplicate objects without using "distinct". Why?

问题描述

我有2个一对多关系的类和一个有点奇怪的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;
}

当我使用以下查询时，我得到重复的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");

此行为是预期的吗?与SQL进行比较，我认为这很不正常.

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

推荐答案

此问题在

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

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

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

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   

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

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

LinkedHashSet过滤掉重复的引用(这是一个集合)，然后 它保留插入顺序(结果中元素的顺序).那 太容易了，所以您可以在许多不同且更困难的地方完成 方式:

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

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

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屋！