Oracle中(column1,column2.)in(聚合/子查询)的hibernate实现
Oracle中(column1,column2..)in(集合/子查询)的hibernate实现
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并不允许继承,构造函数没公开~~
如果要实现,继承就做不了的,但还是通过类似的手段实现目的。
http://raywithu.iteye.com/admin/blogs/1139511