Hi, All

I have a class User which has a set of Role, User and Role are hibernate mapped POJO. User and Role are many-to-many mapped.
Now I want to query all users which has multiple specified roles (The number of roles to match is dynamicly specified by user through the page).
I don't know how to construct the HQL for this query. I hope there's some thing like:

from User as user where user.roles intersect (select role from Role where role.id in (:roleFilter))

here the roleFilter may be "10,11,14,39"

Obviously the above hql will not work.

It's great if it can work like:

from User as user where user.roles contains some (select role from Role where role.id in (:roleFilter))

or

from User as user where user.roles in (select role from Role where role.id in (:roleFilter))

Can anybody give me a correct HQL solution to this problem?

Thank you so much,
Yours,

Use IN and a subquery.

See:
http://forum.hibernate.org/viewtopic.php?t=947645

HTH

Dear joshua,

Thank you so much. It works quite well. The point is that I can use User.roles.id. I only thought that I need to extract each role with such as elements(User.roles).id.

Yous,
wang