Hibernate Criteria: adding additional restriction to Restrictions.isEmpty

hibernate, hibernate-criteria, java

Solution

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)

Problem

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)?

Original source