我是否应该在此JPQL查询中包括非重复? [英] Should I include distinct in this JPQL query?

问题描述

在SO上和许多流行的博客中，我已经看到了关于JPQL JOIN FETCH查询中distinct关键字的必要性和PASS_DISTINCT_THROUGH查询提示的多个答案和问题.

I have seen multiple answers and questions here on SO and in many popular blogs about the necessity of the distinct keyword in JPQL JOIN FETCH queries and about the PASS_DISTINCT_THROUGH query hint.

例如，看到这两个问题

• 在使用JPA和Hibernate时DISTINCT的工作原理
• 在JPA上选择DISTINCT

• How does DISTINCT work when using JPA and Hibernate
• Select DISTINCT on JPA

以及这些博客文章

• 在JPPA和Hibernate中使用JPQL DISTINCT关键字的最佳方法
• DISTINCT传递休眠查询提示
• 休眠提示:如何将DISTINCT应用于JPQL但不应用于SQL查询

• The best way to use the JPQL DISTINCT keyword with JPA and Hibernate
• The DISTINCT pass-through Hibernate Query Hint
• Hibernate Tips: How To Apply DISTINCT to Your JPQL But Not Your SQL Query

现在，我的问题是我无法完全理解JPQL查询中何时必须确切地包含distinct关键字.更具体地说，是否取决于执行查询的方法(getResultList或getSingleResult).

Now my problem is that I cannot fully understand when exactly the distinct keyword must be included in the JPQL query. More specifically, if it depends on which method is used to execute the query (getResultList or getSingleResult).

下面是一个例子，以阐明我的意思.

The following is an example to clarify what I mean.

从现在开始，我写的所有东西都在Ubuntu Linux 18.04，Java 8，Hibernate 5.4.13和内存H2数据库(1.4.200版)中进行了测试.

Everything I am writing from now on was tested on Ubuntu Linux 18.04, with Java 8, Hibernate 5.4.13 and an in-memory H2 database (version 1.4.200).

假设我有一个Department实体，该实体与DepartmentDirector实体具有 lazy 双向一对多关系:

Suppose I have a Department entity which has a lazy bidirectional one to many relationship with a DepartmentDirector entity:

// Department.java
@Entity
public class Department {
    // ...
    private Set<DepartmentDirector> directors;

    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    public Set<DepartmentDirector> getDirectors() {
        return directors;
    }
    // ...
}

// DepartmentDirector.java
@Entity
public class DepartmentDirector {
    // ...
    private Department department

    @ManyToOne
    @JoinColumn(name = "department_fk")
    public Department getDepartment() {
        return department;
    }
    // ...
}

假设我的数据库当前包含一个部门(department1)和与其相关联的两个主管.

Suppose that my database currently contains one department (department1) and two directors associated with it.

现在，我想通过其uuid(主键)及其所有主管来检索部门.这可以通过以下JOIN FETCH JPQL查询来完成:

Now I want to retrieve the department by its uuid (the primary key), along with all its directors. This can be done with the following JOIN FETCH JPQL query:

String query = "select department from Department department left join fetch "
             + "department.directors where department.uuid = :uuid";

由于前面的查询使用带有子集合的join fetch，所以我希望它在发出时返回两个重复的部门:但是，这仅在将查询与getResultList方法一起使用时发生，而不是在使用getSingleResult时发生方法.这在某种程度上是合理的，但是我发现getSingleResult的Hibernate实现在幕后使用了getResultList，因此我希望抛出NonUniqueResultException.

As the preceding query uses a join fetch with a child collection, I expected it to return two duplicated departments when issued: however this only happens when using the query with the getResultList method and not when using the getSingleResult method. This is somehow reasonable, but I have found that the Hibernate implementation of getSingleResult uses getResultList behind the curtains so I expected a NonUniqueResultException to be thrown.

我还简要地介绍了JPA 2.2规范，但没有提及在处理两种方法之间的重复项方面的区别，并且每个与此问题相关的代码示例都使用getResultList方法.

I also briefly went through JPA 2.2 specification but no distinction in treating the duplicates between the two methods is mentioned, and every code sample concerning this issue uses the getResultList method.

在我的示例中，我发现用getSingleResult执行的JOIN FETCH查询没有遇到重复实体问题，这在我在背景部分中链接的资源中得到了解释.

In my example I found out that JOIN FETCH queries executed with getSingleResult do not suffer the duplicated entities problem explained in the resources I linked in the section Background.

如果以上说法正确，则意味着相同的JOIN FETCH查询如果使用getResultList执行则需要distinct，但使用getSingleResult执行时将不需要它.

If the above claim would be correct, it would mean that the same JOIN FETCH query would need distinct if executed with getResultList, but would not need it when executed with getSingleResult.

如果这是预期的或者我误解了一些东西，我需要有人向我解释.

I need someone to explain me if this is expected or if I misunderstood something.

两个查询的结果:

1. 查询使用方法运行.我得到了两个重复的部门(按预期(只是为了测试查询的行为而完成的，应该使用getSingleResult代替)):

List<Department> resultList = entityManager.createQuery(query, Department.class)
        .setParameter("uuid", department1.getUuid())
        .getResultList();

assertThat(resultList).containsExactly(department1, department1); // passes

查询使用方法运行.我希望可以检索相同的重复部门，从而抛出NonUniqueResultException.取而代之的是，只检索一个部门，一切都很好:

Query ran with the getSingleResult method. I would expect the same duplicated departments to be retrieved, and thus a NonUniqueResultException to be thrown. Instead, a single department is retrieved and everything works nice:

Department singleResult = entityManager.createQuery(query, Department.class)
        .setParameter("uuid", department1.getUuid())
        .getSingleResult();

assertThat(singleResult).isEqualTo(department1); // passes

推荐答案

有趣的问题.

首先让我指出，getSingleResult()是针对由于其性质总是返回单个结果的查询(意思是:大多数是汇总的查询，例如SELECT SUM(e.id) FROM Entity e).根据某些特定于业务领域的规则，您 认为的查询应返回单个结果，但实际上并没有资格.

First of all let me point out that getSingleResult() was meant for queries that due to their nature always return a single result (meaning: mostly aggregate queries like SELECT SUM(e.id) FROM Entity e). A query that you think, based on some business domain-specific rule, should return a single result, does not really qualify.

话虽如此，JPA规范指出，当查询返回多个结果时，getSingleResult()应该抛出NonUniqueResultException:

That being said, the JPA Spec states that getSingleResult() should throw NonUniqueResultException when the query returns more than one result:

当调用Query.getSingleResult或TypedQuery.getSingleResult时，持久性提供程序将抛出NonUniqueResultException，并且该查询有多个结果.如果当前事务处于活动状态，则此异常不会导致当前事务被标记为回滚.

The NonUniqueResultException is thrown by the persistence provider when Query.getSingleResult or TypedQuery.getSingleResult is invoked and there is more than one result from the query. This exception will not cause the current transaction, if one is active, to be marked for rollback.

但是，请看一下Hibernate实现:

However, looking at the Hibernate implementation:

    @Override
    public R getSingleResult() {
        try {
            final List<R> list = list();
            if ( list.size() == 0 ) {
                throw new NoResultException( "No entity found for query" );
            }
            return uniqueElement( list );
        }
        catch ( HibernateException e ) {
            if ( getProducer().getFactory().getSessionFactoryOptions().isJpaBootstrap() ) {
                throw getExceptionConverter().convert( e );
            }
            else {
                throw e;
            }
        }
    }

    public static <R> R uniqueElement(List<R> list) throws NonUniqueResultException {
        int size = list.size();
        if ( size == 0 ) {
            return null;
        }
        R first = list.get( 0 );
        for ( int i = 1; i < size; i++ ) {
            if ( list.get( i ) != first ) {
                throw new NonUniqueResultException( list.size() );
            }
        }
        return first;
    }

事实证明，Hibernate对多个结果"的解释似乎是多个唯一结果".

it turns out Hibernate's interpretation of 'more than one result' seems to be 'more than one unique result'.

事实上，我与所有JPA提供程序一起测试了您的方案，事实证明:

In fact, I tested your scenario with all JPA providers, and it turns out that:

• Hibernate确实会从getResultList()返回重复项，但由于实现getSingleResult()的特殊方式，不会引发异常
• EclipseLink是唯一一个不会遭受getResultList()中的重复结果错误困扰的人，因此，getSingleResult()不会不抛出异常(对我而言，此行为仅是逻辑上的) ，但事实证明，这全都是解释问题)
• OpenJPA和DataNucleus都从getResultList()返回重复的结果，并从getSingleResult()
引发异常

• Hibernate does indeed return duplicates from getResultList(), but does not throw the exception due to the peculiar way getSingleResult() is implemented
• EclipseLink is the only one that does not suffer from the duplicate result bug in getResultList() and consequently, getSingleResult() does not throw an exception, either (to me, this behaviour is only logical, but as it turns out, it is all a matter of interpretation)
• OpenJPA and DataNucleus both return duplicate results from getResultList() and throw an exception from getSingleResult()

Tl; DR

如果这是预期的或者我误解了一些东西，我需要有人向我解释.

I need someone to explain me if this is expected or if I misunderstood something.

这实际上归结为您如何解释规范

It really boils down to how you interpret the specification

这篇关于我是否应该在此JPQL查询中包括非重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！