CODEWITHSUNDEEP
javahibernatesubquery
fredcrs
fredcrs

Reputation: 3621

How to retrieve objects using criteria, detached criteria and subquery

For four days now I´ve been trying to perform a query in my database.

Heres the problem:

  1. I have a Group of Users
  2. Users can have birth date (it might be null)
  3. I want to list Groups that have no Users AND Groups that have Users in which all the users have 'null' for the birthdate

I was able to get the Groups that have no Users easily (I did a left join), my problem is with the second part, getting groups where all users have null for the birthdate.

So far this is the code I have:

Criteria c = session.createCriteria(Group.class);
c.createAlias("userCollection", "u", Criteria.LEFT_JOIN);
c.add(Restrictions.or(Restrictions.isNull("userCollection"), 
            Restrictions.isNull("u.birthDate")));

This works fine, but when a user in the group have a birthDate not null the group still appears in the query because others in the group have a null birthdate. I think I need to use not in, right?

If anyone could help please, I tried a lot but I am getting a null pointer exception.

Upvotes: 1

Views: 2607

Answers (1)

ChssPly76
ChssPly76

Reputation: 100706

The easiest way to do this is to exclude groups that have users with non-null birthDate, which takes care of both conditions simultaneously (groups with no users would not be on this list due to inner join).

So, something like:

DetachedCriteria excludedGroups = DetachedCriteria.forClass(Group.class);
excludedGroups.createAlias("userCollection", "u");
excludedGroups.add(Restrictions.isNotNull("u.birthDate"));
excludedGroups.setProjection(Projections.id());

Criteria c = session.createCriteria(Group.class);
c.add(Subqueries.propertyNotIn("id", excludedGroups));

BTW, doing this via HQL would be even easier.

Upvotes: 2

Related Questions