Equivalent for HQL "elements()" in Hibernate Crite

vleidl · **Joined:** Sat Apr 15, 2006 4:50 am **Posts:** 9

Hi all,

I have two entity classes: User and Role. There is an unidirectional many-to-many relationship between users and roles. Each user holds a collection of roles in its 'roles' property.

Is there a simple way of expressing the following HQL with the Hibernate Criteria API?

Code:

"from User user where :role in elements (user.roles)"

It would be easy if there was something like

Code:

Role role;
// ...
session.createCriteria(User.class).add(Restrictions.contains("roles", role));

which is somewhat the opposite of the existing Restrictions.in(...). There is probably another solution and I'm just to blind to figure it out. I'd appreciate any hints into the right direction.

Beset regards,
Volker

Environment:
Java 1.5
Hibernate 3.1
Oracle 10g XE

damnhandy · **Posted:** Sat Apr 15, 2006 6:13 pm

Wrap the singular role in a collection or array using the "in" restriction:

Code:

Collection roles = new ArrayList();
roles.add(role);
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.in("roles",roles));

Or:

Code:

Role[] roles = {role};
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.in("roles",roles));

The "in" restriction always takes a collection or an array so you will need to wrap a the value. It may seem in efficent, but it will give you the results you are looking for.

Ryan-

vleidl · **Joined:** Sat Apr 15, 2006 4:50 am **Posts:** 9

Thanks for the help Ryan.

I'm afraid that Restrictions.in() is not what I'm looking for. It's probably my fault that I just whacked that HQL query into my posting without explaining what I actually want to achieve.

I have a User class that holds a set of Roles in its 'roles' property. Now I would like to retrieve all users that have a specific role assigned to them. In HQL that is quite a simple query. But since I want to add various other search criteria to it in a dynamic way, I thought, that's just what the Hibernate Criteria API is for.

As far as I understand, Restrictions.in() checks if the value of the property given in the first param is contained in the collection specified in the second param. What I need is a restriction that checks if a given role is contained in the value of a specified property (which happens to be a collection). I tried your suggested solution and Hibernate chokes on it. It gives me an SQLException that says 'Missing IN or OUT parameter at index:: 2'.

If you have any ideas about that, I'd be very grateful. Otherwise I'd just have to stick with plain old HQL.

Cheers,
Volker

damnhandy · **Posted:** Sun Apr 16, 2006 10:09 am

If you were to write your query using plain SQL to get the same results, what would it look like? I generally start this way and work backwards when using the Criteria API for the more complex stuff.

Also, did you have SQL logging enabled when you tested the example I posted? This will show you what Hibernate is generating and you'd be able to see why it's throwing that exception.

Ryan-

vleidl · **Joined:** Sat Apr 15, 2006 4:50 am **Posts:** 9

Ok, I've found a working solution:

Code:

Role r;
// ...
Criteria c = s.createCriteria(User.class)
        .createCriteria("roles")
        .add(Restrictions.eq("id", r.getId()));

(hmm, quite obvious when I look at it now ;-))

Yet, I have to expose the identifier of the role to the application. I couldn't find a way to directly compare the object I pass into Restrictions.eq().
However, at least it does what I want.

Cheers,
Volker

damnhandy · **Posted:** Mon Apr 17, 2006 8:22 pm

You actually don't have to expose Role.getId() if you don't want to. Hibernate can utilize private or protected getters and setters if needed. So you can make the Role.getId() and Role.setId() methods protected or private if that makes more sense in regards to the application. But then again, I'm sure that Roles are in a completely different package than the DAO :)

Ryan-

vleidl · **Joined:** Sat Apr 15, 2006 4:50 am **Posts:** 9

Making getId() protected doesn't work, because I have to access r.getId() in my DAO to pass it to Restrictions.eq(), but I guess I can live with it for the time being.

Regards,
Volker