Hi,

i am in trouble with a SQL-Statement and Hibernate. I am using two associatiuon the tabel/object order and the table/object item. The item store a property field status.

The SQL statement returns the orders with the status XX orderd by the date:

SELECT o
FROM ShopOrder o
INNER JOIN FETCH o.items item
WHERE item.status = :orderItemStatus
ORDER BY o.date

But this statement returns duplicate Elements (because the cartesian product). In Hibernate in Action is written: "Hibernate doesn't return a distinct result list...... You wil probably need to make the result distinct yourself using, for example distinctResult = new HashMap(resultList).".

OK the trick with the set works fine but i lost the order! I tried several statements and the operator distinct but no succsess. Do I have to Convert the result in a Set (to make it distinct), return to a list for handmake sorting? Or can get an orderd List without duplicate elements with HSQL? Maby you can give me a tip. I have no idea how.

Thanxx
Marc

Try using a LinkedHashSet instead.

Here's a snippet of code:



Got this from the Hibernate FAQ:

http://www.hibernate.org/117.html

HTH

Craig

It works perfectly! Thanxx Craig

Is this really the way it should be done? For some reason this feels like a work around. Could a hibernate developer comment on this?

thanks in advance,
Mike

Looks like the "correct" way of doing this is by using a criteria object with:

.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)

Now my question is...how do I do this in HQL.