Criteria在where中使用子查询(无关联)

Oracle中可以使用如下的SQL语句



SELECT *
FROM BZ_COMPANY
WHERE( CLN_DATE, COMPNAY_NAME ) IN (
SELECT MAX(CLN_DATE),COMPANY_NAME
FROM BZ_COMPANY
GROUP BY COMPANY_NAME
);






可在hibernate中QBC中应该怎么去写呢



QBC的in只能做到如下的实现：



DetachedCriteria dc = DetachedCriteria.forClass(BzCompany.class);
dc.setProjection(Projections.projectionList()
//.add(Projections.max("clnDate"))
.add(Projections.groupProperty("companyName")));
Criteria criteria = session.createCriteria(BzCompany.class);
criteria.add(Example.create(bean)
.ignoreCase()
.enableLike(MatchMode.ANYWHERE))
.add(Subqueries.propertyIn("companyName", dc));




propertyIn的属性参数只支持单属性,编写MySubqueries,重载propertyIn方法的一个实现,让它支持多属性。

这里涉及两个类：



1、对应于Subqueries的MySubqueries:



package util;

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Subqueries;

public class MySubqueries extends Subqueries {

public static Criterion propertyIn(String[] propertyNames, DetachedCriteria dc) {
return new MySubqueryExpression(propertyNames, "in", null, dc);
}
} 2、对应于SubqueryExpression的MySubqueryExpression:



package util;

import org.hibernate.Criteria;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.SubqueryExpression;
import org.hibernate.util.StringHelper;

public class MySubqueryExpression extends SubqueryExpression {

private String[] propertyNames;

protected MySubqueryExpression(String[] propertyNames, String op, String quantifier, DetachedCriteria dc) {
super(op, quantifier, dc);
this.propertyNames = propertyNames;
}

protected String toLeftSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
String[] columns = new String[propertyNames.length];
for (int i = 0; i < propertyNames.length; i++) {
columns[i] = criteriaQuery.getColumn(criteria, propertyNames[i]);
}
return "(" + StringHelper.join(", ", columns) + ")";
}

}
使用例子:



DetachedCriteria dc = DetachedCriteria.forClass(BzCompany.class);
dc.setProjection(Projections.projectionList()
.add(Projections.max("clnDate"))
.add(Projections.groupProperty("companyName")));
Criteria criteria = session.createCriteria(BzCompany.class);
criteria.add(Example.create(bean)
.ignoreCase()
.enableLike(MatchMode.ANYWHERE))
.add(MySubqueries.propertyIn(new String[]{"clnDate","companyName"}, dc));

不过这是in(子查询)的实现,in(集合)是使用Restrictions

但奇怪的是Restrictions并不允许继承,构造函数没公开~~

如果要实现,继承就做不了的,但还是通过类似的手段实现目的。