How to use distinct in hql

Question

I am working on a web application using Hibernate, struts and extjs, Here is my scenario.

String hql = "from product";

In my product table three columns are there id, name, section

I need the results based on distinct name of product but i have only option is writing hql no other option is there.

I can use group by but i need the size of the result

so i am using hql.list().get(0);

In my code above code is in common method that is every dao uses the same method.

I can use joins but the data is in millions so execution of query is too slow, So can any one help how can i write distinct keyword in hql.

Your question is not clear. What do you want to select? The number of distinct product names? — JB Nizet
I want the product data with distinct product names, primary key id has different values i need to ignore them. If i get data i will automatically get the count — Bindu

Jaffar Ramay Jaffar Ramay · Accepted Answer · 2012-02-09T10:58:52

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

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.

How to use distinct in hql

2 Answers