Hi, I have come up against a problem to which I can find no work-around. The ideal solution would be to have some sort of Criterion which replicates the beaviour of the HQL 'in elements()' clause.

Something like:



I've had a play with the Hibernate source code in the past (I spent ages creating a 'distinct' Restriction and then realised it didn't help in that case :@ ) but this is way beyond me. The idea of this is to basically create subqueries that use aliases from the main query. How would I go about requesting such a feature for future Hibernate releases and would it be likely to ever happen?

Here is the problem:

I have a search form which has various fields to add criteria to the search. The result objects are made up of a tree structure so you may wish to put some criteria on the children or even sub-children. There is one section where you specify that one particular sub-child has a set that must contain each of the options the user selects from a multiselect list. So this ends up as am int[] of ids. I need to loop through this array and add an inElements for each int value. It's basically the opposite of Restrictions.in().

At the moment I am doing it programatically with lots of loops but apart from being horrible, it also means Hibernate has to load up all the child objects meaning I get LOADS of queries where I could just have a couple of extra subqueries inside my main criteria query.

I have seen similar posts but none of them want to add multiple inElements criteria. Any other way of doing this would be greatly appreciated also.

Solution:

Use Subquery.exists to do a similar job but using the SQL EXISTS expression.

My Boss told me they had previously had some situations where EXISTS was much quicker than IN so I looked up this with Hibernate, the site that helped me most was http://www.cereslogic.com/pages/2008/09/22/hibernate-criteria-subqueries-exists/