java - hibernate 条件 : adding additional restriction to Restrictions. isEmpty

Question

我创建了一个 Hibernate (3.5) 条件查询:

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

当使用 Criteria.list 执行时，Hibernate 会生成以下 SQL(为了便于阅读而进行了简化):

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)

我想做的是在“不存在”部分添加一个限制，以便 SQL 查询看起来像这样

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 <strong>AND DEPRECATED = 0</strong> )

是否可以使用 Hibernate Criteria API，如果可以，如何实现？或者是否有其他可能实现相同的结果(但仍然使用 Criteria API)？

Answer 1

是的，有可能，使用子查询:

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

这相当于以下 HQL 查询:

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)