To improve performance I've had to implement an eager get method in my DAO. Here is the HQL:



The relationships are lists (and lists in lists) and I get many many duplicates in the results. The distinct only appears to work for the root object (as does the result transform trick). Is there a way to make all the collections only contain distinct values?

I've tried (Hibernate 3 legacy) criterias and I can't get the eager loading on the nested (list in a list) relationships to work. I can't see how to specify distinct results either.

Only changing the lists to sets in the model seems to work - and that screws up the ordering of the elements.
Changing the collections to eager is not an option because it cripples performance in the rest of the application that only needs a tiny bit of the (lazy) loaded object.

Any ideas?

I'm beginning to wonder if there is any way of eagerly fetching one or a list of objects with no duplicate associates in its tree. Not without designing the mapping around this very problem (tightly coupled code by any measure).

Using Sets removes the duplicates (as one would expect), but as mentioned earlier, the sorting fails. It's not as if Java's LinkedHashSet can't maintain an order. The annotation @OrderColumn doesn't work with a (LinkedHash)Set and complains about nulls during write. I guess this is because Hibernate's implementation relies on a Java List's internal index for setting the order column value? Can hibernate not automatically create this value when writing out an ordered set? I can use the @OrderBy annotation for entity associations (those with an appropriate index attribute) but what should I do about element collections (e.g. of a string)? I can't order by the string value because the order is by creation and not alphabetical.

I'm pretty stuck. Does anyone have any solutions? Is there a way of leaving my model alone and getting rid of the duplicates in the eagerly loaded associated lists?