×
文章路径: Java

HQL分页sql遇到distinct时count总数不对

发表于3年前(Dec 24, 2014 10:45:17 AM)  阅读 547  评论 0

分类: Java 数据库

标签: countingsort count hql

1、分页工具类:

项目中经常会有一个BaseDao,所有Dao都会继承这个BaseDao然后实现各自的Dao接口,BaseDao主要是封装了所有Dao通用的方法,其中就有分页查询。如下面的方法:

      /**
 * 分页查询
 * @param hql hql语句
 * @param values 参数数组
 * @param pageNum 页码
 * @param pageSize 每页显示的记录数
 * @return PageList分页查询结果的包装类
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public PageList findPageList(final String hql, final Object[] values,
		final int pageNum, final int pageSize) {

	String counthql = hql.substring(hql.toLowerCase().indexOf("from "));
       counthql = "select count(*) "+counthql;
       final String queryStr = counthql;
	List list = getHibernateTemplate().executeFind(new HibernateCallback() {
		public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
			Query query = session.createQuery(queryStr);
			if(values!=null) {
				for (int i = 0; i < values.length; i++) {
					query.setParameter(i, values[i]);
				}
			}
			List result = query.list();
			return result;
		}
	});
	int totalRecordNum = Integer.parseInt(list.get(0).toString());

	List data = getHibernateTemplate().executeFind(new HibernateCallback() {
		public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
			Query query = session.createQuery(hql);
			if(values!=null) {
				for (int i = 0; i < values.length; i++) {
					query.setParameter(i, values[i]);
				}
			}
			List result = query.setFirstResult((pageNum-1)*pageSize).setMaxResults(
					pageSize).list();
			return result;
		}
	});

	int totalPageNum = totalRecordNum/pageSize;
	if(totalRecordNum%pageSize!=0) {
		totalPageNum+=1;
	}
	PageList pageList = new PageList();
	pageList.setPageSize(pageSize);
	pageList.setCurrentPageNum(pageNum);
	pageList.setDataList(data);
	pageList.setTotalPageNum(totalPageNum);
	pageList.setTotalRecordNum(totalRecordNum);
	return pageList;
}

该方法每次会执行两个sql,首先一个sql用来统计总记录数,第二个sql才是分页查询指定数据。这是常用的一个方法,至少笔者所接触的项目基本都这么做的,至今还是没碰到过什么问题。

2、引发问题的sql

首先看下面这个sql:

String hql = "select p from Post p, Menu m where m.menuId in elements (p.menus) and p.postStatus=1 and p.postType=0 and m.menuId in ("+ids+")";

Post是文章实体类,Menu是文章所属目录实体类,两者是多对多的关系,该sql是查询指定目录下的文章。该sql有一个问题,一个文章属于多个目录,而且这两个目录都在指定查询的目录里面,这篇文章就会出现多次,ok,简单,加上distinct,去重,于是sql改成了下面这样:

String hql = "select distinct p from Post p, Menu m where m.menuId in elements (p.menus) and p.postStatus=1 and p.postType=0 and m.menuId in ("+ids+")";

再测试,发现查询出来的记录总数不对,会大于实际记录数。Debug看执行的count sql,原来,count sql是从from截取,把distinct去掉了,查出的结果当然不对了,这是count出来的记录数未去重的记录数。

3、解决办法:

看了一下网上解决办法,没太多有用的,把原来sql当成一个子查询在外面加一层count,这应该是比较容易想到也应该能实现的方法。不过笔者实现时,发现并不行,报语法错误,貌似这种子查询hql不支持,于是笔者改成了count(distinct p),修改BaseDao里面的findPageList组装count sql的语句:

String counthql = hql;
String lowsql = counthql.toLowerCase();
if(lowsql.contains("distinct")) {
	String disStr = counthql.substring(lowsql.indexOf("distinct"), lowsql.indexOf("from"));
	counthql = counthql.replace(disStr, " count("+disStr+") ");
} else {
	counthql = counthql.substring(lowsql.indexOf("from ")); 
	counthql = "select count(*) "+counthql; 
}

这样测试的sql是没有问题了。

4、思考:

上面的sql是没问题了的,但实现可以看到还是很粗糙的,很有可能别的distinct的语句就解决不了,到时就需要再酌情进行修改了。笔者觉得能不使用distinct还是尽量不要使用distinct,上面的语句笔者也一时不知道能不能改写。据说使用group by也会出现distinct问题。

发表评论