Need help expressing a query

emmanueln · **Joined:** Fri Jul 18, 2008 3:36 pm **Posts:** 1

Hi, I'm having trouble expressing a query.

I have a User class where each user has roles. Then I want to find all users that have ALL of the selected roles (this is the input parameter for the query).

For example, if I have one user Alice with roles [Role1, Role2, Role3], and the selected role list is [Role1, Role4], the query should return no users (because Alice doesn't have Role4), but if the selected role list is [Role1, Role2], the query should return Alice.

I'm using this query:

Code:

from User u left join u.roles r where r in (:selectedRoleList)

but this return all users that have ANY of the selected roles, not ALL of them.

Is there a way to express this in HQL or Criteria?

Thanks.

Cameron McKenzie · **Posted:** Sat Jul 19, 2008 10:13 am

I think you'll want to use the Criteria API. YOu can just create an Example object class or add new restrictions to a Criterion class.

Take a look at this tutorial:

Hibernate3 Tutorial: How to Use the Hibernate Criteria API

A simple critieria query with multiple restrictions might look like this:

Code:

public static void main(String args[]) {
  Session session = HibernateUtil.beginTransaction();
  Criterion c1 = Restrictions.gt("id", (long)2);
  Criterion c2 = Restrictions.lt("id", (long)8);
  Criterion c3 = Restrictions.isNotNull("emailAddress");
  User user = new User();
  user.setEmailAddress(".com");
  Example c4 = Example.create(user);
  c4.enableLike(MatchMode.END);
  c4.ignoreCase();
  Criteria criteria = session.createCriteria(User.class);
  criteria.add(c1);
  criteria.add(c2);
  criteria.add(c3);
  criteria.add(c4);
  List results = criteria.list();
  HibernateUtil.commitTransaction();
  for (int i = 0; i<results.size(); i++) {
    System.out.println(results.get(i).toString());
  }
}