1. Overview

1.概述

In this article, we’ll discuss distinct HQL queries and how to avoid adding the distinct keyword in the SQL queries when it’s not necessary.

在这篇文章中，我们将讨论不同的HQL查询，以及如何避免在没有必要时在SQL查询中添加distinct关键字。

2. Understanding the Problem

2.了解问题

First, let’s look at our data model and identify what we’re trying to accomplish.

首先，让我们看看我们的数据模型，确定我们要完成的任务。

We’ll use the Post and Comment entity objects, which share a one-to-many relationship. We want to retrieve a list of posts with all their associated comments.

我们将使用Post和Comment实体对象，它们共享一个一对多的关系。我们想检索一个包含所有相关评论的帖子列表。

Let’s start by trying out the following HQL query:

让我们首先尝试一下下面的HQL查询。

String hql = "SELECT p FROM Post p LEFT JOIN FETCH p.comments";
List<Post> posts = session.createQuery(hql, Post.class).getResultList();

This will generate the following SQL query:

这将产生以下SQL查询。

select
    post0_.id as id1_3_0_,
    comment2_.id as id1_0_1_,
    post0_.title as title2_3_0_,
    comment2_.text as text2_0_1_,
    comments1_.Post_id as Post_id1_4_0__,
    comments1_.comments_id as comments2_4_0__
from
    Post post0_
left outer join
    Post_Comment comments1_
        on post0_.id=comments1_.Post_id
left outer join
    Comment comment2_
        on comments1_.comments_id=comment2_.id

The result will contain duplicates. A Post will be shown as many times as there are associated Comments – a Post with three Comments will show up in the result list three times.

结果将包含重复的内容。 一个帖子将被显示的次数与相关的评论一样多 – 一个帖子有三个评论将在结果列表中显示三次。

3. Using distinct in the HQL Query

3.在HQL查询中使用distinct

We’ll need to use the distinct keyword in our HQL query to eliminate the duplicates:

我们需要在我们的HQL查询中使用distinct 关键字来消除重复的内容。

String hql = "SELECT DISTINCT p FROM Post p LEFT JOIN FETCH p.comments";
List<Post> posts = session.createQuery(hql, Post.class).getResultList();

Now, we’re getting the correct result: There are no more duplicated Post objects. Let’s take a look at the SQL statement generated by Hibernate:

现在，我们得到了正确的结果。不再有重复的Post对象。让我们看一下Hibernate生成的SQL语句。

select
    distinct post0_.id as id1_3_0_,
    comment2_.id as id1_0_1_,
    post0_.title as title2_3_0_,
    comment2_.text as text2_0_1_,
    comments1_.Post_id as Post_id1_4_0__,
    comments1_.comments_id as comments2_4_0__
from
    Post post0_
left outer join
    Post_Comment comments1_
        on post0_.id=comments1_.Post_id
left outer join
    Comment comment2_
        on comments1_.comments_id=comment2_.id

We can notice that the distinct keyword was not only used by Hibernate but also included in the SQL query. We should avoid this because it’s unnecessary and will cause performance issues.

我们可以注意到，distinct关键字不仅被Hibernate使用，而且还包括在SQL查询中。我们应该避免这样做，因为它是不必要的，而且会导致性能问题。

4. Using QueryHint to Stop Passing the distinct Keyword

4.使用QueryHint来停止传递distinct关键字

Starting with Hibernate 5.2, we can leverage the pass-distinct-through mechanism to no longer pass the SQL statement’s HQL/JPQL distinct clause.

从Hibernate 5.2开始，我们可以利用pass-distinct-through机制，不再传递SQL语句的HQL/JPQL distinct子。

To disable pass-distinct-through, we need to add to the query the hint QueryHint.PASS_DISTINCT_THROUGH, with value false:

要禁用pass-distinct-through，我们需要在查询中添加提示QueryHint.PASS_DISTINCT_THROUGH，其值为false。

String hql = "SELECT DISTINCT p FROM Post p LEFT JOIN FETCH p.comments";
List<Post> posts = session.createQuery(hql, Post.class)
  .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
  .getResultList();

If we check the result, we’ll see there are no duplicated entities. Furthermore, the distinct clause wasn’t used in the SQL statement:

如果我们检查结果，我们会发现没有重复的实体。此外，在SQL语句中没有使用distinct子句。

select
    post0_.id as id1_3_0_,
    comment2_.id as id1_0_1_,
    post0_.title as title2_3_0_,
    comment2_.text as text2_0_1_,
    comments1_.Post_id as Post_id1_4_0__,
    comments1_.comments_id as comments2_4_0__ 
from
    Post post0_ 
left outer join
    Post_Comment comments1_ 
        on post0_.id=comments1_.Post_id 
left outer join
    Comment comment2_ 
        on comments1_.comments_id=comment2_.id

5. Conclusion

5.总结

In this article, we’ve discovered that the presence of the distinct keyword in the SQL query can be unnecessary, and it has performance implications. After that, we’ve learned how to use the PASS_DISTINCT_THROUGH query hint to avoid this behavior.

在这篇文章中，我们已经发现，在SQL查询中出现distinct关键字可能是不必要的，它对性能有影响。之后，我们已经学会了如何使用PASS_DISTINCT_THROUGH查询提示来避免这种行为。

As always, the source code is available over on GitHub.

像往常一样，源代码可在GitHub上获得。