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

Question

我有两个一对多关系类和一个有点奇怪的HQL查询。即使我读过一些已经贴出的问题，我也不清楚

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

使用以下查询时，我会得到重复的部门对象：

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

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

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

这种行为是预期的吗？我认为这是不寻常的，因为它与SQL相比。p>

Answer 1

我从弗拉德·米哈尔恰先生那里学到了一个很好的建议。有关更多提示： https://vladmihalcea.com/tutorials/hibernate/

    list = session.createQuery("SELECT DISTINCT c FROM Supplier c "
            +"LEFT JOIN FETCH c.salesList WHERE c.name LIKE :p1"
            , Supplier.class)
            .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
            .setParameter("p1", name + "%")
            .list();

Answer 2

使用结果转换器Criteria.DISTINCT_ROOT_ENTITY：

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

Answer 3

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