How do you create a Distinct query in HQL

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

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

Solution 3:

do something like this next time

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

 List claz = crit.list();

Solution 4:

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

Solution 5:

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.