CODEWITHSUNDEEP
hibernatehqldistinct
Bindu
Bindu

Reputation: 39

How to use distinct in hql

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.

Upvotes: 0

Views: 13032

Answers (2)

San Lin Naing
San Lin Naing

Reputation: 127

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.

Upvotes: 0

Jaffar Ramay
Jaffar Ramay

Reputation: 1165

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.

Upvotes: 0

Related Questions