Narrowing the result of query by subquery

Question

I'm trying to narrow the result set of Hibernate Criteria query by result of another query. I know how to resolve this problem with JPQL:

FROM DocPackage p WHERE
  EXISTS (SELECT g
    FROM ObjectGroup g JOIN g.items i, Person per
    WHERE g=p.applicantGroup
      AND i.objectClass = 'org.cp.model.common.Person'
      AND i.objectId=per.id
      AND lower(concat(per.firstName,' ',per.lastName)) like :applicant
  )

But I can't imagine how to make such query with Criteria. Any ideas how to implement this selection with Criteria? Hibernate 3.3 is used.

UPD: Trying to solve this problem I've made the following Criteria query:

Criteria resultCriteriaQuery = this.hibernateSession.createCriteria(DocPackage.class, "pack");
        DetachedCriteria personSubquery = DetachedCriteria.forClass(Person.class, "pers").
            add(Restrictions.like("pers.loFstLstName", "%" + searchObject.getApplicant().toLowerCase() + "%")).
            add(Restrictions.eqProperty("itm.objectId", "pers.id"));
        DetachedCriteria applicantsSubquery = DetachedCriteria.forClass(ObjectGroup.class, "objGrp").
            add(Restrictions.eqProperty("pack.applcantGroup", "objGrp")).
            createAlias("objGrp.items", "itm").
            add(Restrictions.eq("itm.objectClass", "org.cp.model.common.Person")).
            add(Subqueries.exists(personSubquery));
        resultCriteriaQuery.add(Subqueries.exists(applicantsSubquery));

But it doesn't work. I have a NullPointerException on resultCriteriaQuery.list(). What's wrong with this query? Any ideas?

score 0 · Answer 1 · answered Sep 06 '12 at 08:02

to analyze the case a little better, it prints the HQL statement generated automatically from the criteria, you have to put in the connection properties hibernate:

<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />

And the log to debug.

Once you have it, you can compare with which you want to generate and view the differences.

Regards,

score 0 · Answer 2 · answered Sep 06 '12 at 08:44

You can create sub-criteria with the Criteria.createCriteria(String) method.

Assuming the following scenario:

Class B has a name:String.

Class A has a elements:Set.

You now want (apart from other restrictions) only A-objects where there is one B with name="X":

Criteria crit = session.createCriteria(A.class);
<add your restrictions for A>

Criteria narrow = crit.createCriteria("elements");
narrow.add(Restrictions.eq("name", "X");

// This will respect the constraints applied to narrow
crit.list();

score 0 · Answer 3 · answered Oct 13 '13 at 19:36

Non-associated joins are not supported by Hibernate Criteria API. This row is a problem:

FROM ObjectGroup g JOIN g.items i, Person per

From what I see, you'll need to create an explicit mapping between ObjectGroupItem and Person. This may be accomplished by the means of Hibernate @Any annotation. Take a look at Hibernate Annotations, paragraph 2.4.5.2. When association will be mapped, use Criteria#createCriteria() or Criteria#createAlias() to add necessary joins to the sub query. You are already using correct API to add a subquery to the main query.

Narrowing the result of query by subquery

3 Answers3