Oracle中(column1,column2..)in(集合/子查询)的hibernate实现

最新推荐文章于 2022-10-29 12:30:53 发布

最新推荐文章于 2022-10-29 12:30:53 发布 · 396 阅读

文章标签：

#Oracle #in #多字段 #hibernate #子查询

Hibernate 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何将Oracle SQL查询转换为Hibernate Query by Criteria (QBC) 的实现方式，特别是针对包含子查询的情况。文中提供了具体的代码示例，展示了如何自定义Subqueries以支持多属性的in子查询。

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