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

Answer 1

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

Answer 2

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.

Answer 3

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.