Hi forum,

I'm struggling to find the answer to this question, and I hope one of the experts here will show how it's done.

I have three (simplified for the question) classes:



I have a list of Person objects that I get with this:



I want to add a subquery to this, so I only get persons that have, for example, a Skill "Java coding" with skill level 3.

And to make things more difficult, I'll have any number of (skill, level) pairs that need to be matched. For example, "Java coding" at 3 and "SQL" at 5.

This is easy to do with SQL, but I haven't yet figured out how to do it with Hibernate. Here's one of my attempts at it:



This gives me a MappingException "Unknown entity: ...PersonSkill".

I hope one of you will set me on the right track.

Thank you for your time.

Bo Thorsen,
Monty Program AB.

Just to make it clear, here is the SQL statement I need to execute. In this case, I'm searching for persons that have the skill with id 1 and a level higher than 2:



Adding more skill searches is just a matter of adding more of the last two parts of this statement.

I found the answer myself.

There is no way currently to create a Criteria based search using projections, aliases, subqueries or any other standard Criteria based class on a CollectionOfElements.

The only way to do this is to use Restrictions.sqlRestriction. In my case from above, I did this:



With X and Y replaced, of course.

This works by simply adding a WHERE clause to the search, and is not optimal for MariaDB (or MySQL), but at least it works.

Bo Thorsen,
Monty Program AB.

Great find. I had a similar problem, and this seems to be the only solution. What would be even more slick is if we could made X and Y bind variables.