distinc in HQL

mwang168 · **Joined:** Thu Oct 05, 2006 4:00 pm **Posts:** 22

Hi, I have the following query

select distinct parent from parent left join fetch child where parent.name like ?
return getSession()
.createQuery(hqlString)
.setString("searchString",name)
.setFirstResult(startPosition)
.setMaxResults(maxResult)
.list();

I keep getting duplicate parent back if parent has 1 or more children.

is the SQL correct? How to use distinct correctly?

The child is a set in parent defined in parent.hbm.xml.

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

mwang168 wrote:

Hi, I have the following query

select distinct parent from parent left join fetch child where parent.name like ?
return getSession()
.createQuery(hqlString)
.setString("searchString",name)
.setFirstResult(startPosition)
.setMaxResults(maxResult)
.list();

I keep getting duplicate parent back if parent has 1 or more children.

is the SQL correct? How to use distinct correctly?

The child is a set in parent defined in parent.hbm.xml.

thats correct put the list you are getting into set ... to remove dups

mwang168 · **Joined:** Thu Oct 05, 2006 4:00 pm **Posts:** 22

thanks for the quick reply. is there a more efficient way to do this?

Currently I am using a TreeSet to sort it. is there a short cut API convert from list to set?

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

mwang168 wrote:

thanks for the quick reply. is there a more efficient way to do this?

Currently I am using a TreeSet to sort it. is there a short cut API convert from list to set?

I dont think there is ..( i might be wrong ) for HQL ...but there is
http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/CriteriaSpecification.html#DISTINCT_ROOT_ENTITY
if you are using criteria