0

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.

Community
  • 1
  • 1
Flo
  • 27,355
  • 15
  • 87
  • 125

1 Answers1

2

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);
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Unfortunately it doesn't work for me. Neither the criteria nor the HQL version. I'm trying to query the association form [this question](http://stackoverflow.com/questions/7248668/hibernate-many-to-many-association-left-hand-side-collection-contains-elements/7249604#7249604). The problem is after I changed my association like you advised in your answer of that question. The query I mentioned in this question to query the user with a certain role doesn't work any more. No I'm totally confused what I'm doing wrong. – Flo Sep 01 '11 at 10:34
  • Be more explicit. What do you mean by "doesn't work"? Does it generate an error? Is the SQL generated correctly? What does it return? – JB Nizet Sep 01 '11 at 11:08
  • No it doesn't generate an error. First changed the line 'roleName.setProjection(Projections.id());' to 'roleOfUserWithName.setProjection(Projections.id());'.My test scenario: I create 3 users and 2 roles (Admin, User). First user gets the admin role, second user gets both roles and last user gets the user role. Then I add the users to the other side of the association (roles) and save all objects. So for this scenario the query should return users without admin role. So in this case only the last user with the user role should be returned. But the list returned by the query is empty. – Flo Sep 01 '11 at 11:21
  • Make sure to run the query in a separate test (with data prepared in advance in the database), or to at least flush the session before executing the query. But you're making things more complex by creating entities and testing the query in the same test, because the error might come from the query and from the insertion. – JB Nizet Sep 01 '11 at 11:29
  • Great, flushing the session worked!! Thank you very much. I think I still don't understand how hibernate works in the background do you have any suggestions for good tutorials. – Flo Sep 01 '11 at 11:38
  • Normally, you shouldn't have to flush the session, because Hibernate is smart enough to detect that the query you will execute needs a flush, based on the entities that the request uses. Hibernate, though, isn't smart enough and doesn't examine the entities used in the subqueries, and thus a manual flush is necessary in this case. – JB Nizet Sep 01 '11 at 12:12