By Mike Pone


2008-11-04 23:17:38 8 Comments

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".

9 comments

@Santosh Singh 2018-08-21 09:31:44

You can you the distinct keyword in you criteria builder like this.

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Orders> query = builder.createQuery(Orders.class);
Root<Orders> 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.

@Nathan Mitchell 2014-11-03 18:25:09

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<CacheModelBase> 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<CacheModelBase> 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.

@aadi53 2014-08-14 14:11:04

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();

@Haroldo_OK 2019-01-21 11:49:34

That's suboptimal: instead of discarding the repetitions at the database level, it will just pull the data from the database to the memory with repetitions and all, and then discard the repetitions afterwards; depending on how often the data repeats, that can increase the I/O operations quite a lot.

@Daniel Alexiuc 2008-11-23 22:38:11

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.

@Daniel Alexiuc 2011-03-31 04:00:19

@chammu 2015-05-12 14:53:28

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.

@San Lin Naing 2013-02-22 13:22:17

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

@Michael 2011-05-19 23:10:27

do something like this next time

 Criteria crit = (Criteria) session.
                  createCriteria(SomeClass.class).
                  setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

 List claz = crit.list();

@Haroldo_OK 2019-01-21 11:49:00

That's suboptimal: instead of discarding the repetitions at the database level, it will just pull the data from the database to the memory with repetitions and all, and then discard the repetitions afterwards; depending on how often the data repeats, that can increase the I/O operations quite a lot.

@Tadeusz Kopec 2009-05-28 10:52:20

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.

@mboullouz 2018-01-02 22:10:00

to get to 10k (:

@Feet 2008-11-04 23:25:10

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 >= ? and f.creationDate < ? and b.bar = ?";
        return getHibernateTemplate().find(queryString, new Object[] {startDate, endDate, bar});

@boecko 2012-04-23 14:23:58

that doesn't work on mssql, when text types are involved

@Feet 2012-05-13 23:51:40

I only ever used hibernate with MySQL - not sure how to deal with the mssql issue.

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] How do you do a limit query in HQL?

24 Answered Questions

[SOLVED] Is there any difference between GROUP BY and DISTINCT

2 Answered Questions

How to use distinct in hql

19 Answered Questions

[SOLVED] LINQ's Distinct() on a particular property

1 Answered Questions

[SOLVED] Using SELECT DISTINCT in HQL

3 Answered Questions

[SOLVED] Hibernate HQL Query : How to set a Collection as a named parameter of a Query?

  • 2009-02-20 16:26:56
  • karlgrz
  • 96026 View
  • 64 Score
  • 3 Answer
  • Tags:   java hibernate hql

2 Answered Questions

[SOLVED] why does hibernate hql distinct cause an sql distinct on left join?

2 Answered Questions

[SOLVED] Converting Native distinct on sql query to hql

1 Answered Questions

HQL distinct set

  • 2011-07-14 10:10:12
  • Clinton Bosch
  • 462 View
  • 0 Score
  • 1 Answer
  • Tags:   hibernate hql

1 Answered Questions

[SOLVED] Hibernate new keyword with distinct

Sponsored Content