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