CODEWITHSUNDEEP
javahibernatemany-to-manycriteria-api
Flo
Flo

Reputation: 27455

Querying many to many association where the entity doesn't have a certain value using Hibernate Criteria API

I got two classes User and Role which are mapped to each other using a many to many associations. Now I'm trying to query all users which doesn't have a certain role using the Hibernate Criteria API. But I'm a little bit stuck.

To query users with a certain role I use the following query, which works just fine.

Session session = getSessionFactory().getCurrentSession();          
Criteria mainCrit = session.createCriteria(boClass);
return mainCrit.createAlias("roles", "r").add( Restrictions.eq("r.name", roleName)).list();

Now I'm a little bit confused how to reverse the query and get all user that don't have a certain role. If possible I want to explicit exclude a certain role and don't query for all roles chaining them with OR as there may be more roles added later dynamically.

UPDATE

To get a better understanding of my scenario you can see the association I'm trying to query in another question.

Furthermore I would also add that the name property of the Role class is an Enum, don't know if this is important or changes the way to query the database.

Upvotes: 0

Views: 998

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

There's perhaps a more elegant way, but the only one I've found is to query for all the users for which there doesn't exist any role in the user's roles which has the given role name.

This is done like this:

Criteria c = session.createCriteria(User.class, "user");

DetachedCriteria roleOfUserWithName = DetachedCriteria.forClass(Role.class, "role");
roleOfUserWithName.createAlias("role.users", "userOfTheRole");
roleOfUserWithName.add(Restrictions.eqProperty("userOfTheRole.id", "user.id"));
roleOfUserWithName.add(Restrictions.eq("role.name", roleName);
roleOfUserWithName.setProjection(Projections.id());

c.add(Subqueries.notExists(roleOfUserWithName));

It's equivalent to the following HQL query:

select user from User user where not exists (
    select role.id from Role role inner join role.users userOfTheRole
    where userOfTheRole.id = user.id
    and role.name = :roleName);

Upvotes: 2

Related Questions