Hibernate在from子句中使用子查询

    最近用到了Hibernate来作为数据处理部分的框架，离线criteria的确很好用，但在分页上确实让人头疼。要想分页就需要记录的总条数，如果只是一般的查询只需要

criteria.setProjection(Projections.rowCount());

就可以了，但如果是distinct的查询，记录条数和你所需查询的列有关，那么可以这样

Projections.countDistinct(propertyName);

问题来了，好像只能有一列啊。没错在标准sql中的distinct count也只能是一列的，但当有多列查询的时候怎么办呢？criteria有个接口可以设定结果值为distinct，就这样

criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

但是这样做的问题在于，他是先执行，后在程序中distinct，这样会导致每页的记录可能不同，而且性能低下。
换个思路在写sql的时候可以这样写

select count(*) from (select distinct xxxxxx)

也就是说在外层套一个count 而真实的查询语句在from子句中，成为一个子查询。那看看如何变成Hibernate的形式吧。我在网站上搜索了好久，终于得出一个结论（经过Hibernate文档证明了）Hibernate不支持from子句中存在子查询，他们认为from应该是一个映射了的对象，子查询和他们的思想不符。
好吧，那再换个思路，Hibernate本身只是一个映射的过程，那么实际执行还是需要JDBC实现。如果是JDBC就需要sql语句，我们把criteria中蕴含的sql语句提取出来，然后加上我们外层的计数部分，然后再像Hibernate一样绑定参数就可以了，因为外层没有参数占位符，所以也不会影响Hibernate按位置绑定参数，因为没有接口，那只能读源码了。果然，hibernate最后通过sql（就是log中看到的那个）生成一个PreparedStatement，并通过方法绑定参数。废话少说，直接来个破冰之旅。

	/**
	 * 因为需要修改sql，就要传入你新的sql，criteria必须是绑定session的
	 * 因为我们用到了session
	 * @param sql
	 * @param criteria
	 * @return
	 */
	public static List<List> wrapAndExecute(String sql, Criteria criteria) {
		ResultSet rs = null;//最后的结果集
		PreparedStatement ps = null;//我们自己的PreparedStatement
		Connection connection = null;//session中获取的连接
		try {
			//先转型
			CriteriaImpl criteriaImpl = (CriteriaImpl) criteria;
//			获取SessionImplementor类型的session
			SessionImplementor session = criteriaImpl.getSession();
//			获取factory
			SessionFactoryImplementor factory = session.getFactory();
//			获取CriteriaQueryTranslator对象，按照Hibernate源码中的方式生成
			CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, criteriaImpl, criteriaImpl
					.getEntityOrClassName(), CriteriaQueryTranslator.ROOT_SQL_ALIAS);
//			从CriteriaQueryTranslator对象中获取保存参数的QueryParameters对象
			QueryParameters queryParameters = translator.getQueryParameters();
//			从factory中获取implementors，一般只用一个
			String[] implementors = factory.getImplementors(criteriaImpl.getEntityOrClassName());
//			因为只用一个，所以只需要一个loader，按照Hibernate源码中的方式新建
//			注意有些地方由于非public所以需要用反射，反射部分代码就不贴了，注意invokeMethod要从子类找到父类，直到object
//			因为有些方法是写在父类中的
			Loader loader = new CriteriaLoader((OuterJoinLoadable) ReflectUtil.invokeMethod(session,
					"getOuterJoinLoadable", new Class[] { String.class }, new Object[] { implementors[0] }), factory,
					criteriaImpl, implementors[0], ((SessionImpl) session).getLoadQueryInfluencers());
//			按照Hibernate源码方法获取walker
			CriteriaJoinWalker walker = new CriteriaJoinWalker((OuterJoinLoadable) factory
					.getEntityPersister(implementors[0]), translator, factory, criteriaImpl, criteriaImpl
					.getEntityOrClassName(), session.getLoadQueryInfluencers());
//			获取了criteria的sql
			String criteriaSql = walker.getSQLString();			
//			用传入的sql代替，注意PLACEHOLDER代表criteriaSql的占位符
			sql = sql.replace(PLACEHOLDER, criteriaSql);
//			获取session中的连接
			connection = session.getJDBCContext().getConnectionManager().getConnection();
//			构建新的PreparedStatement
			ps = connection.prepareStatement(sql);
//			注意一定要过滤过参数才能绑定
			queryParameters.processFilters(criteriaSql, session);
//			通过反射绑定参数
			ReflectUtil.invokeMethod(loader, "bindParameterValues", new Class[] { PreparedStatement.class,
					QueryParameters.class, int.class, SessionImplementor.class }, new Object[] { ps, queryParameters,
					1, session });
//			获取结果集
			rs = session.getBatcher().getResultSet(ps);
......

呼，好了，终于抠出了criteria的东西了。
最后值得注意的是，由于ResultSet和PreparedStatement是自己创建的，所以需要自己维护，就是要注意关闭咯。至于connection由于是session中获取的，可以不用管，否则这个session如果还有其他操作connection关了就要抛错了。

评论

2 楼 buptchenliang 2012-09-05  

liuluoqiu 写道

lz,这样是可以获得原生sql，但是里面的参数是？，如何才能显示？中内容啊

本文目的不是获取完全的sql，只是通过这种方式个性化sql。当然你想获取到完整的sql也是可以的，所有的参数都在QueryParameters 这个类里，你完全可以从这个对象中将每个参数获取，结合带有？的sql就可以得到完整的sql了。

1 楼 liuluoqiu 2012-06-15  

lz,这样是可以获得原生sql，但是里面的参数是？，如何才能显示？中内容啊