How does DISTINCT work when using JPA and Hibernate

Question

What column does DISTINCT work with in JPA and is it possible to change it   Here s an example JPA query using DISTINCT   select DISTINCT c from Customer c   Which doesn t make a lot of sense - what column is the distinct based on  Is it specified on the Entity as an annotation because I couldn t find one   I would like to specify the column to make the distinction on  something like   select DISTINCT c name  c from Customer c   I m using MySQL and Hibernate

User · Accepted Answer

Update  See the top-voted answer please   My own is currently obsolete  Only kept here for historical reasons     Distinct in HQL is usually needed in Joins and not in simple examples like your own   See also How do you create a Distinct query in HQL

User · Answer

You are close   select DISTINCT c name  from Customer c

User · Answer

Entity  NamedQuery name    Customer listUniqueNames                query    SELECT DISTINCT c name FROM Customer c   public class Customer                        private String name           public static List lt String gt  listUniqueNames                  return   getEntityManager   createNamedQuery                      Customer listUniqueNames   String class                      getResultList

User · Answer

Depending on the underlying JPQL or Criteria API query type  DISTINCT has two meanings in JPA  Scalar queries For scalar queries  which return a scalar projection  like the following query  List lt Integer gt  publicationYears   entityManager  createQuery       quot select distinct year p createdOn   quot         quot from Post p  quot         quot order by year p createdOn  quot   Integer class   getResultList     LOGGER info  quot Publication years     quot   publicationYears    The DISTINCT keyword should be passed to the underlying SQL statement because we want the DB engine to filter duplicates prior to returning the result set  SELECT DISTINCT     extract YEAR FROM p created on  AS col 0 0  FROM     post p ORDER BY     extract YEAR FROM p created on   -- Publication years   2016  2018   Entity queries For entity queries  DISTINCT has a different meaning  Without using DISTINCT  a query like the following one  List lt Post gt  posts   entityManager  createQuery       quot select p  quot         quot from Post p  quot         quot left join fetch p comments  quot         quot where p title    title quot   Post class   setParameter       quot title quot         quot High-Performance Java Persistence eBook has been released  quot     getResultList     LOGGER info       quot Fetched the following Post entity identifiers     quot        posts stream   map Post  getId  collect Collectors toList        is going to JOIN the post and the post comment tables like this  SELECT p id AS id1 0 0          pc id AS id1 1 1          p created on AS created 2 0 0          p title AS title3 0 0          pc post id AS post id3 1 1          pc review AS review2 1 1          pc post id AS post id3 1 0   FROM   post p LEFT OUTER JOIN        post comment pc ON p id pc post id WHERE        p title  High-Performance Java Persistence eBook has been released    -- Fetched the following Post entity identifiers   1  1   But the parent post records are duplicated in the result set for each associated post comment row  For this reason  the List of Post entities will contain duplicate Post entity references  To eliminate the Post entity references  we need to use DISTINCT  List lt Post gt  posts   entityManager  createQuery       quot select distinct p  quot         quot from Post p  quot         quot left join fetch p comments  quot         quot where p title    title quot   Post class   setParameter       quot title quot         quot High-Performance Java Persistence eBook has been released  quot     getResultList      LOGGER info       quot Fetched the following Post entity identifiers     quot        posts stream   map Post  getId  collect Collectors toList        But then DISTINCT is also passed to the SQL query  and that s not desirable at all  SELECT DISTINCT        p id AS id1 0 0          pc id AS id1 1 1          p created on AS created 2 0 0          p title AS title3 0 0          pc post id AS post id3 1 1          pc review AS review2 1 1          pc post id AS post id3 1 0   FROM   post p LEFT OUTER JOIN        post comment pc ON p id pc post id WHERE        p title  High-Performance Java Persistence eBook has been released     -- Fetched the following Post entity identifiers   1   By passing DISTINCT to the SQL query  the EXECUTION PLAN is going to execute an extra Sort phase which adds overhead without bringing any value since the parent-child combinations always return unique records because of the child PK column  Unique   cost 23 71  23 72 rows 1 width 1068   actual time 0 131  0 132 rows 2 loops 1    - gt   Sort   cost 23 71  23 71 rows 1 width 1068   actual time 0 131  0 131 rows 2 loops 1          Sort Key  p id  pc id  p created on  pc post id  pc review         Sort Method  quicksort  Memory  25kB         - gt   Hash Right Join   cost 11 76  23 70 rows 1 width 1068   actual time 0 054  0 058 rows 2 loops 1                Hash Cond   pc post id   p id                - gt   Seq Scan on post comment pc   cost 0 00  11 40 rows 140 width 532   actual time 0 010  0 010 rows 2 loops 1                - gt   Hash   cost 11 75  11 75 rows 1 width 528   actual time 0 027  0 027 rows 1 loops 1                      Buckets  1024  Batches  1  Memory Usage  9kB                     - gt   Seq Scan on post p   cost 0 00  11 75 rows 1 width 528   actual time 0 017  0 018 rows 1 loops 1                            Filter    title   text    High-Performance Java Persistence eBook has been released    text                            Rows Removed by Filter  3 Planning time  0 227 ms Execution time  0 179 ms  Entity queries with HINT PASS DISTINCT THROUGH To eliminate the Sort phase from the execution plan  we need to use the HINT PASS DISTINCT THROUGH JPA query hint  List lt Post gt  posts   entityManager  createQuery       quot select distinct p  quot         quot from Post p  quot         quot left join fetch p comments  quot         quot where p title    title quot   Post class   setParameter       quot title quot         quot High-Performance Java Persistence eBook has been released  quot     setHint QueryHints HINT PASS DISTINCT THROUGH  false   getResultList      LOGGER info       quot Fetched the following Post entity identifiers     quot        posts stream   map Post  getId  collect Collectors toList        And now  the SQL query will not contain DISTINCT but Post entity reference duplicates are going to be removed  SELECT        p id AS id1 0 0          pc id AS id1 1 1          p created on AS created 2 0 0          p title AS title3 0 0          pc post id AS post id3 1 1          pc review AS review2 1 1          pc post id AS post id3 1 0   FROM   post p LEFT OUTER JOIN        post comment pc ON p id pc post id WHERE        p title  High-Performance Java Persistence eBook has been released     -- Fetched the following Post entity identifiers   1   And the Execution Plan is going to confirm that we no longer have an extra Sort phase this time  Hash Right Join   cost 11 76  23 70 rows 1 width 1068   actual time 0 066  0 069 rows 2 loops 1    Hash Cond   pc post id   p id    - gt   Seq Scan on post comment pc   cost 0 00  11 40 rows 140 width 532   actual time 0 011  0 011 rows 2 loops 1    - gt   Hash   cost 11 75  11 75 rows 1 width 528   actual time 0 041  0 041 rows 1 loops 1          Buckets  1024  Batches  1  Memory Usage  9kB         - gt   Seq Scan on post p   cost 0 00  11 75 rows 1 width 528   actual time 0 036  0 037 rows 1 loops 1                Filter    title   text    High-Performance Java Persistence eBook has been released    text                Rows Removed by Filter  3 Planning time  1 184 ms Execution time  0 160 ms

User · Answer

I would use JPA s constructor expression feature  See also following answer   JPQL Constructor Expression - org hibernate hql ast QuerySyntaxException Table is not mapped  Following the example in the question  it would be something like this   SELECT DISTINCT new com mypackage MyNameType c name  from Customer c

User · Answer

I agree with kazanaki s answer  and it helped me  I wanted to select the whole entity  so I used   select DISTINCT c  from Customer c   In my case I have many-to-many relationship  and I want to load entities with collections in one query   I used LEFT JOIN FETCH and at the end I had to make the result distinct

[java] How does DISTINCT work when using JPA and Hibernate

Examples related to java

Examples related to jpa

Examples related to distinct