Hibernate Criteria: adding additional restriction to Restrictions.isEmpty

Question

I have created a Hibernate (3.5) Criteria query:

Criteria criteria = db.getSession().createCriteria(Vendor.class);
criteria.add(Restrictions.isEmpty("models"));

When executed with Criteria.list, Hibernate produces the following SQL (simplified for sake of readability):

SELECT this_.*
FROM   VENDOR this_
       left outer join MODEL models1_
         ON this_.id = models1_.VENDOR_ID
WHERE  NOT EXISTS (SELECT 1
                   FROM   MODEL
                   WHERE  this_.id = VENDOR_ID)

What I'd like to do is add a restriction within the "not exists" part so that the SQL query would look like this

SELECT this_.* FROM VENDOR this_
left outer join MODEL models1_
         ON this_.id = models1_.VENDOR_ID
WHERE
NOT EXISTS (SELECT 1
                             FROM   MODEL
                             WHERE  this_.id = VENDOR_ID AND DEPRECATED = 0 )

Is that possible using the Hibernate Criteria API and if yes, how? Or is there some other possibility to achieve the same result (but still using Criteria API)?

score 14 · Accepted Answer · answered May 11 '12 at 11:37

Yes, it's possible, using a subquery:

Criteria criteria = db.getSession().createCriteria(Vendor.class, "vendor");

DetachedCriteria dc = DetachedCriteria.forClass(Vendor.class, "vendor2");
dc.createAlias("vendor2.models", "model");
dc.add(Restrictions.eq("model.deprecated", 0));
dc.add(Restrictions.eqProperty("vendor2.id", "vendor.id"));
dc.setProjection(Projections.id());

criteria.add(Subqueries.notExists(dc));

which is the equivalent of the following HQL query:

select vendor from Vendor vendor
where not exists(select vendor2.id from Vendor vendor2
                 inner join vendor2.models model
                 where model.deprecated = 0
                 and vendor2.id = vendor.id)

Hibernate Criteria: adding additional restriction to Restrictions.isEmpty

1 Answers1

Linked