How do you create a Distinct query in HQL

Question

Is there a way to create a Distinct query in HQL   Either by using the  distinct  keyword or some other method   I am not sure if distinct is a valid keywork for HQL  but I am looking for the HQL equivalent of the SQL keyword  distinct

User · Answer

I had some problems with result transformers combined with HQL queries. When I tried

final ResultTransformer trans = new DistinctRootEntityResultTransformer();
qry.setResultTransformer(trans);

it didn't work. I had to transform manually like this:

final List found = trans.transformList(qry.list());

With Criteria API transformers worked just fine.

User · Answer

I had some problems with result transformers combined with HQL queries. When I tried

final ResultTransformer trans = new DistinctRootEntityResultTransformer();
qry.setResultTransformer(trans);

it didn't work. I had to transform manually like this:

final List found = trans.transformList(qry.list());

With Criteria API transformers worked just fine.

User · Answer

It s worth noting that the distinct keyword in HQL does not map directly to the distinct keyword in SQL    If you use the distinct keyword in HQL  then sometimes Hibernate will use the distinct SQL keyword  but in some situations it will use a result transformer to produce distinct results  For example when you are using an outer join like this   select distinct o from Order o left join fetch o lineItems   It is not possible to filter out duplicates at the SQL level in this case  so Hibernate uses a ResultTransformer to filter duplicates after the SQL query has been performed

User · Answer

do something like this next time    Criteria crit    Criteria  session                    createCriteria SomeClass class                     setResultTransformer Criteria DISTINCT ROOT ENTITY     List claz   crit list

User · Answer

Here s a snippet of hql that we use   Names have been changed to protect identities   String queryString    select distinct f from Foo f inner join foo bars as b                      where f creationDate  gt     and f creationDate  lt    and b bar               return getHibernateTemplate   find queryString  new Object    startDate  endDate  bar

User · Answer

If you need to use new keyword for a custom DTO in your select statement and need distinct elements  use new outside of new like as follows-  select distinct new com org AssetDTO a id  a address  a status  from Asset as a where

User · Answer

It s worth noting that the distinct keyword in HQL does not map directly to the distinct keyword in SQL    If you use the distinct keyword in HQL  then sometimes Hibernate will use the distinct SQL keyword  but in some situations it will use a result transformer to produce distinct results  For example when you are using an outer join like this   select distinct o from Order o left join fetch o lineItems   It is not possible to filter out duplicates at the SQL level in this case  so Hibernate uses a ResultTransformer to filter duplicates after the SQL query has been performed

User · Answer

You can simply add GROUP BY instead of Distinct   Query value    from someTableEntity where entityCode in  entityCode                  group by entityCode  entityName  entityType   List lt someTableEntity  gt  findNameByCode  Param  entityCode   List lt String gt  entityCode

User · Answer

If you need to use new keyword for a custom DTO in your select statement and need distinct elements  use new outside of new like as follows-  select distinct new com org AssetDTO a id  a address  a status  from Asset as a where

User · Answer

Here s a snippet of hql that we use   Names have been changed to protect identities   String queryString    select distinct f from Foo f inner join foo bars as b                      where f creationDate  gt     and f creationDate  lt    and b bar               return getHibernateTemplate   find queryString  new Object    startDate  endDate  bar

User · Answer

do something like this next time    Criteria crit    Criteria  session                    createCriteria SomeClass class                     setResultTransformer Criteria DISTINCT ROOT ENTITY     List claz   crit list

User · Answer

You can also use Criteria DISTINCT ROOT ENTITY with Hibernate HQL query as well   Example   Query query   getSession   createQuery  from java pojo name    query setResultTransformer Criteria DISTINCT ROOT ENTITY   return query list

User · Answer

Suppose you have a Customer Entity mapped to CUSTOMER INFORMATION table and you want to get list of distinct firstName of customer  You can use below snippet to get the same   Query distinctFirstName   session createQuery  select ci firstName from Customer ci group by ci firstName    Object    firstNamesRows   distinctFirstName list      I hope it helps  So here we are using group by instead of using distinct keyword    Also previously I found it difficult to use distinct keyword when I want to apply it to multiple columns  For example I want of get list of distinct firstName  lastName then group by would simply work  I had difficulty in using distinct in this case

User · Answer

You can simply add GROUP BY instead of Distinct   Query value    from someTableEntity where entityCode in  entityCode                  group by entityCode  entityName  entityType   List lt someTableEntity  gt  findNameByCode  Param  entityCode   List lt String gt  entityCode

User · Answer

You can also use Criteria DISTINCT ROOT ENTITY with Hibernate HQL query as well   Example   Query query   getSession   createQuery  from java pojo name    query setResultTransformer Criteria DISTINCT ROOT ENTITY   return query list

User · Answer

It s worth noting that the distinct keyword in HQL does not map directly to the distinct keyword in SQL    If you use the distinct keyword in HQL  then sometimes Hibernate will use the distinct SQL keyword  but in some situations it will use a result transformer to produce distinct results  For example when you are using an outer join like this   select distinct o from Order o left join fetch o lineItems   It is not possible to filter out duplicates at the SQL level in this case  so Hibernate uses a ResultTransformer to filter duplicates after the SQL query has been performed

User · Answer

You can you the distinct keyword in you criteria builder like this   CriteriaBuilder builder   session getCriteriaBuilder    CriteriaQuery lt Orders gt  query   builder createQuery Orders class   Root lt Orders gt  root   query from Orders class   query distinct true  multiselect root get  cust email   as String class      And create the field constructor in your model class

User · Answer

My main query looked like this in the model    NamedQuery name    getAllCentralFinancialAgencyAccountCd        query    select distinct i from CentralFinancialAgencyAccountCd i     And I was still not getting what I considered  distinct  results   They were just distinct based on a primary key combination on the table   So in the DaoImpl I added an one line change and ended up getting the  distinct  return I wanted   An example would be instead of seeing 00 four times I now just see it once   Here is the code I added to the DaoImpl    SuppressWarnings  unchecked   public List lt CacheModelBase gt  getAllCodes          Session session    Session  entityManager getDelegate        org hibernate Query q   session getNamedQuery  getAllCentralFinancialAgencyAccountCd        q setResultTransformer Criteria DISTINCT ROOT ENTITY      This is the one line I had to add to make it do a more distinct query      List lt CacheModelBase gt  codes      codes   q list        return codes             I hope this helped   Once again  this might only work if you are following coding practices that implement the service  dao  and model type of project

User · Answer

Here s a snippet of hql that we use   Names have been changed to protect identities   String queryString    select distinct f from Foo f inner join foo bars as b                      where f creationDate  gt     and f creationDate  lt    and b bar               return getHibernateTemplate   find queryString  new Object    startDate  endDate  bar

User · Answer

Here s a snippet of hql that we use   Names have been changed to protect identities   String queryString    select distinct f from Foo f inner join foo bars as b                      where f creationDate  gt     and f creationDate  lt    and b bar               return getHibernateTemplate   find queryString  new Object    startDate  endDate  bar

User · Answer

I have got a answer for Hibernate Query Language to use Distinct fields  You can use  SELECT DISTINCT TO CITY  FROM FLIGHT ROUTE   If you use SQL query  it return String List  You can t use it return value by Entity Class  So the Answer to solve that type of Problem is use HQL with SQL   FROM FLIGHT ROUTE F WHERE F ROUTE ID IN  SELECT SF ROUTE ID FROM FLIGHT ROUTE SF GROUP BY SF TO CITY     From SQL query statement it got DISTINCT ROUTE ID and input as a List  And IN query filter the distinct TO CITY from IN  List    Return type is Entity Bean type  So you can it in AJAX such as AutoComplement   May all be OK

User · Answer

I have got a answer for Hibernate Query Language to use Distinct fields  You can use  SELECT DISTINCT TO CITY  FROM FLIGHT ROUTE   If you use SQL query  it return String List  You can t use it return value by Entity Class  So the Answer to solve that type of Problem is use HQL with SQL   FROM FLIGHT ROUTE F WHERE F ROUTE ID IN  SELECT SF ROUTE ID FROM FLIGHT ROUTE SF GROUP BY SF TO CITY     From SQL query statement it got DISTINCT ROUTE ID and input as a List  And IN query filter the distinct TO CITY from IN  List    Return type is Entity Bean type  So you can it in AJAX such as AutoComplement   May all be OK

User · Answer

You can you the distinct keyword in you criteria builder like this   CriteriaBuilder builder   session getCriteriaBuilder    CriteriaQuery lt Orders gt  query   builder createQuery Orders class   Root lt Orders gt  root   query from Orders class   query distinct true  multiselect root get  cust email   as String class      And create the field constructor in your model class

User · Answer

It s worth noting that the distinct keyword in HQL does not map directly to the distinct keyword in SQL    If you use the distinct keyword in HQL  then sometimes Hibernate will use the distinct SQL keyword  but in some situations it will use a result transformer to produce distinct results  For example when you are using an outer join like this   select distinct o from Order o left join fetch o lineItems   It is not possible to filter out duplicates at the SQL level in this case  so Hibernate uses a ResultTransformer to filter duplicates after the SQL query has been performed

User · Answer

Suppose you have a Customer Entity mapped to CUSTOMER INFORMATION table and you want to get list of distinct firstName of customer  You can use below snippet to get the same   Query distinctFirstName   session createQuery  select ci firstName from Customer ci group by ci firstName    Object    firstNamesRows   distinctFirstName list      I hope it helps  So here we are using group by instead of using distinct keyword    Also previously I found it difficult to use distinct keyword when I want to apply it to multiple columns  For example I want of get list of distinct firstName  lastName then group by would simply work  I had difficulty in using distinct in this case

User · Answer

My main query looked like this in the model    NamedQuery name    getAllCentralFinancialAgencyAccountCd        query    select distinct i from CentralFinancialAgencyAccountCd i     And I was still not getting what I considered  distinct  results   They were just distinct based on a primary key combination on the table   So in the DaoImpl I added an one line change and ended up getting the  distinct  return I wanted   An example would be instead of seeing 00 four times I now just see it once   Here is the code I added to the DaoImpl    SuppressWarnings  unchecked   public List lt CacheModelBase gt  getAllCodes          Session session    Session  entityManager getDelegate        org hibernate Query q   session getNamedQuery  getAllCentralFinancialAgencyAccountCd        q setResultTransformer Criteria DISTINCT ROOT ENTITY      This is the one line I had to add to make it do a more distinct query      List lt CacheModelBase gt  codes      codes   q list        return codes             I hope this helped   Once again  this might only work if you are following coding practices that implement the service  dao  and model type of project

[hibernate] How do you create a Distinct query in HQL

Examples related to hibernate

Examples related to hql

Examples related to distinct