HQL how to query ElementCollection of String

Question

I have class like

public User{
   Long id;
   Set<String> roles;
}

How do I query all User objects with the role of "ADMIN"

EDIT:

I'm using Hibernate 3.0.5. And have tried most of the obvious approaches.

from Users where roles in('ADMIN') gives a JDBC error. from Users u where u.roles in('ADMIN') gives a class cast exception

I think this may be a problem with this particular version of hibernate.

Answer 1

I've found solution:

"from User as user where 'ADMIN' in elements(user.roles)";

Somehow hql function value() have to help with this, you can also experiment with it, but that hql query above works for me.

Answer 2

You can use the query below

"from User as user where user.id in (select user.id from Role as role left join role.user as user where role.name = 'ADMIN')"

Answer 3

This should do it:

session.createQuery("from User where roles in ('ADMIN')");