应用Hibernate3的DetachedCriteria实现分页查询

Hibernate3提供了DetachedCriteria，使得我们可以在Web层构造detachedCriteria，然后调用业务层Bean，进行动态条件查询，根据这一功能，我设计了通用的抽象Bean基类和分页类支持，代码来自于Quake Wang的javaeye-core包的相应类，然后又做了很多修改。

分页支持类：

package com.javaeye.common.util;

import java.util.List;

public class PaginationSupport {

	public final static int PAGESIZE = 30;

	private int pageSize = PAGESIZE;

	private List items;

	private int totalCount;

	private int[] indexes = new int[0];

	private int startIndex = 0;

	public PaginationSupport(List items, int totalCount) {
		setPageSize(PAGESIZE);
                setTotalCount(totalCount);
		setItems(items);		
		setStartIndex(0);
	}

	public PaginationSupport(List items, int totalCount, int startIndex) {
                setPageSize(PAGESIZE);
		setTotalCount(totalCount);
		setItems(items);		
		setStartIndex(startIndex);
	}

	public PaginationSupport(List items, int totalCount, int pageSize, int startIndex) {
                setPageSize(pageSize);
		setTotalCount(totalCount);
		setItems(items);
		setStartIndex(startIndex);
	}

	public List getItems() {
		return items;
	}

	public void setItems(List items) {
		this.items = items;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		if (totalCount > 0) {
			this.totalCount = totalCount;
			int count = totalCount / pageSize;
			if (totalCount % pageSize > 0)
				count++;
			indexes = new int[count];
			for (int i = 0; i < count; i++) {
				indexes[i] = pageSize * i;
			}
		} else {
			this.totalCount = 0;
		}
	}

	public int[] getIndexes() {
		return indexes;
	}

	public void setIndexes(int[] indexes) {
		this.indexes = indexes;
	}

	public int getStartIndex() {
		return startIndex;
	}

	public void setStartIndex(int startIndex) {
		if (totalCount <= 0)
			this.startIndex = 0;
		else if (startIndex >= totalCount)
			this.startIndex = indexes[indexes.length - 1];
		else if (startIndex < 0)
			this.startIndex = 0;
		else {
			this.startIndex = indexes[startIndex / pageSize];
		}
	}

	public int getNextIndex() {
		int nextIndex = getStartIndex() + pageSize;
		if (nextIndex >= totalCount)
			return getStartIndex();
		else
			return nextIndex;
	}

	public int getPreviousIndex() {
		int previousIndex = getStartIndex() - pageSize;
		if (previousIndex < 0)
			return 0;
		else
			return previousIndex;
	}

}

抽象业务类

/**
 * Created on 2005-7-12
 */
package com.javaeye.common.business;

import java.io.Serializable;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.javaeye.common.util.PaginationSupport;

public abstract class AbstractManager extends HibernateDaoSupport {

	private boolean cacheQueries = false;

	private String queryCacheRegion;

	public void setCacheQueries(boolean cacheQueries) {
		this.cacheQueries = cacheQueries;
	}

	public void setQueryCacheRegion(String queryCacheRegion) {
		this.queryCacheRegion = queryCacheRegion;
	}

	public void save(final Object entity) {
		getHibernateTemplate().save(entity);
	}

	public void persist(final Object entity) {
		getHibernateTemplate().save(entity);
	}

	public void update(final Object entity) {
		getHibernateTemplate().update(entity);
	}

	public void delete(final Object entity) {
		getHibernateTemplate().delete(entity);
	}

	public Object load(final Class entity, final Serializable id) {
		return getHibernateTemplate().load(entity, id);
	}

	public Object get(final Class entity, final Serializable id) {
		return getHibernateTemplate().get(entity, id);
	}

	public List findAll(final Class entity) {
		return getHibernateTemplate().find("from " + entity.getName());
	}

	public List findByNamedQuery(final String namedQuery) {
		return getHibernateTemplate().findByNamedQuery(namedQuery);
	}

	public List findByNamedQuery(final String query, final Object parameter) {
		return getHibernateTemplate().findByNamedQuery(query, parameter);
	}

	public List findByNamedQuery(final String query, final Object[] parameters) {
		return getHibernateTemplate().findByNamedQuery(query, parameters);
	}

	public List find(final String query) {
		return getHibernateTemplate().find(query);
	}

	public List find(final String query, final Object parameter) {
		return getHibernateTemplate().find(query, parameter);
	}

	public PaginationSupport findPageByCriteria(final DetachedCriteria detachedCriteria) {
		return findPageByCriteria(detachedCriteria, PaginationSupport.PAGESIZE, 0);
	}

	public PaginationSupport findPageByCriteria(final DetachedCriteria detachedCriteria, final int startIndex) {
		return findPageByCriteria(detachedCriteria, PaginationSupport.PAGESIZE, startIndex);
	}

	public PaginationSupport findPageByCriteria(final DetachedCriteria detachedCriteria, final int pageSize,
			final int startIndex) {
		return (PaginationSupport) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				int totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
				criteria.setProjection(null);
				List items = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
				PaginationSupport ps = new PaginationSupport(items, totalCount, pageSize, startIndex);
				return ps;
			}
		}, true);
	}

	public List findAllByCriteria(final DetachedCriteria detachedCriteria) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				return criteria.list();
			}
		}, true);
	}

	public int getCountByCriteria(final DetachedCriteria detachedCriteria) {
		Integer count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				return criteria.setProjection(Projections.rowCount()).uniqueResult();
			}
		}, true);
		return count.intValue();
	}
}

用户在web层构造查询条件detachedCriteria，和可选的startIndex，调用业务bean的相应findByCriteria方法，返回一个PaginationSupport的实例ps。

ps.getItems()得到已分页好的结果集
ps.getIndexes()得到分页索引的数组
ps.getTotalCount()得到总结果数
ps.getStartIndex()当前分页索引
ps.getNextIndex()下一页索引
ps.getPreviousIndex()上一页索引

连续看了两篇robbin有关DetachedCriteria的介绍，感觉真的不错，尤其是上面的示例代码，让我着实觉得该对我原来的分页查询做一下代码重构了。

我把原本我的做法也提供出来供大家讨论吧：

首先，为了实现分页查询，我封装了一个Page类：
[code:1]
/*Created on 2005-4-14*/
package org.flyware.util.page;

/**
* @author Joa
*
*/
public class Page {

/** imply if the page has previous page */
private boolean hasPrePage;

/** imply if the page has next page */
private boolean hasNextPage;

/** the number of every page */
private int everyPage;

/** the total page number */
private int totalPage;

/** the number of current page */
private int currentPage;

/** the begin index of the records by the current query */
private int beginIndex;


/** The default constructor */
public Page(){

}

/** construct the page by everyPage
* @param everyPage
* */
public Page(int everyPage){
this.everyPage = everyPage;
}

/** The whole constructor */
public Page(boolean hasPrePage, boolean hasNextPage,
int everyPage, int totalPage,
int currentPage, int beginIndex) {
this.hasPrePage = hasPrePage;
this.hasNextPage = hasNextPage;
this.everyPage = everyPage;
this.totalPage = totalPage;
this.currentPage = currentPage;
this.beginIndex = beginIndex;
}

/**
* @return
* Returns the beginIndex.
*/
public int getBeginIndex() {
return beginIndex;
}

/**
* @param beginIndex
* The beginIndex to set.
*/
public void setBeginIndex(int beginIndex) {
this.beginIndex = beginIndex;
}

/**
* @return
* Returns the currentPage.
*/
public int getCurrentPage() {
return currentPage;
}

/**
* @param currentPage
* The currentPage to set.
*/
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}

/**
* @return
* Returns the everyPage.
*/
public int getEveryPage() {
return everyPage;
}

/**
* @param everyPage
* The everyPage to set.
*/
public void setEveryPage(int everyPage) {
this.everyPage = everyPage;
}

/**
* @return
* Returns the hasNextPage.
*/
public boolean getHasNextPage() {
return hasNextPage;
}

/**
* @param hasNextPage
* The hasNextPage to set.
*/
public void setHasNextPage(boolean hasNextPage) {
this.hasNextPage = hasNextPage;
}

/**
* @return
* Returns the hasPrePage.
*/
public boolean getHasPrePage() {
return hasPrePage;
}

/**
* @param hasPrePage
* The hasPrePage to set.
*/
public void setHasPrePage(boolean hasPrePage) {
this.hasPrePage = hasPrePage;
}

/**
* @return Returns the totalPage.
*
*/
public int getTotalPage() {
return totalPage;
}

/**
* @param totalPage
* The totalPage to set.
*/
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}

}
[/code:1]

上面的这个Page类对象只是一个完整的Page描述，接下来我写了一个PageUtil，负责对Page对象进行构造：
[code:1]
/*Created on 2005-4-14*/
package org.flyware.util.page;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
* @author Joa
*
*/
public class PageUtil {

private static final Log logger = LogFactory.getLog(PageUtil.class);

/**
* Use the origin page to create a new page
* @param page
* @param totalRecords
* @return
*/
public static Page createPage(Page page, int totalRecords){
return createPage(page.getEveryPage(), page.getCurrentPage(), totalRecords);
}

/**
* the basic page utils not including exception handler
* @param everyPage
* @param currentPage
* @param totalRecords
* @return page
*/
public static Page createPage(int everyPage, int currentPage, int totalRecords){
everyPage = getEveryPage(everyPage);
currentPage = getCurrentPage(currentPage);
int beginIndex = getBeginIndex(everyPage, currentPage);
int totalPage = getTotalPage(everyPage, totalRecords);
boolean hasNextPage = hasNextPage(currentPage, totalPage);
boolean hasPrePage = hasPrePage(currentPage);

return new Page(hasPrePage, hasNextPage,
everyPage, totalPage,
currentPage, beginIndex);
}

private static int getEveryPage(int everyPage){
return everyPage == 0 ? 10 : everyPage;
}

private static int getCurrentPage(int currentPage){
return currentPage == 0 ? 1 : currentPage;
}

private static int getBeginIndex(int everyPage, int currentPage){
return (currentPage - 1) * everyPage;
}

private static int getTotalPage(int everyPage, int totalRecords){
int totalPage = 0;

if(totalRecords % everyPage == 0)
totalPage = totalRecords / everyPage;
else
totalPage = totalRecords / everyPage + 1 ;

return totalPage;
}

private static boolean hasPrePage(int currentPage){
return currentPage == 1 ? false : true;
}

private static boolean hasNextPage(int currentPage, int totalPage){
return currentPage == totalPage || totalPage == 0 ? false : true;
}


}
[/code:1]

上面的这两个对象与具体的业务逻辑无关，可以独立和抽象。

面对一个具体的业务逻辑：分页查询出User，每页10个结果。具体做法如下：
1. 编写一个通用的结果存储类Result，这个类包含一个Page对象的信息，和一个结果集List：
[code:1]
/*Created on 2005-6-13*/
package com.adt.bo;

import java.util.List;

import org.flyware.util.page.Page;

/**
* @author Joa
*/
public class Result {

private Page page;

private List content;

/**
* The default constructor
*/
public Result() {
super();
}

/**
* The constructor using fields
*
* @param page
* @param content
*/
public Result(Page page, List content) {
this.page = page;
this.content = content;
}

/**
* @return Returns the content.
*/
public List getContent() {
return content;
}

/**
* @return Returns the page.
*/
public Page getPage() {
return page;
}

/**
* @param content
* The content to set.
*/
public void setContent(List content) {
this.content = content;
}

/**
* @param page
* The page to set.
*/
public void setPage(Page page) {
this.page = page;
}
}
[/code:1]

2. 编写业务逻辑接口，并实现它（UserManager， UserManagerImpl）
[code:1]
/*Created on 2005-7-15*/
package com.adt.service;

import net.sf.hibernate.HibernateException;

import org.flyware.util.page.Page;

import com.adt.bo.Result;

/**
* @author Joa
*/
public interface UserManager {

public Result listUser(Page page) throws HibernateException;

}
[/code:1]

[code:1]
/*Created on 2005-7-15*/
package com.adt.service.impl;

import java.util.List;

import net.sf.hibernate.HibernateException;

import org.flyware.util.page.Page;
import org.flyware.util.page.PageUtil;

import com.adt.bo.Result;
import com.adt.dao.UserDAO;
import com.adt.exception.ObjectNotFoundException;
import com.adt.service.UserManager;

/**
* @author Joa
*/
public class UserManagerImpl implements UserManager {

private UserDAO userDAO;

/**
* @param userDAO The userDAO to set.
*/
public void setUserDAO(UserDAO userDAO) {
this.userDAO = userDAO;
}

/* (non-Javadoc)
* @see com.adt.service.UserManager#listUser(org.flyware.util.page.Page)
*/
public Result listUser(Page page) throws HibernateException, ObjectNotFoundException {
int totalRecords = userDAO.getUserCount();
if(totalRecords == 0)
throw new ObjectNotFoundException("userNotExist");
page = PageUtil.createPage(page, totalRecords);
List users = userDAO.getUserByPage(page);
return new Result(page, users);
}

}
[/code:1]

其中，UserManagerImpl中调用userDAO的方法实现对User的分页查询，接下来编写UserDAO的代码：
3. UserDAO 和 UserDAOImpl:
[code:1]
/*Created on 2005-7-15*/
package com.adt.dao;

import java.util.List;

import org.flyware.util.page.Page;

import net.sf.hibernate.HibernateException;

/**
* @author Joa
*/
public interface UserDAO extends BaseDAO {

public List getUserByName(String name) throws HibernateException;

public int getUserCount() throws HibernateException;

public List getUserByPage(Page page) throws HibernateException;

}
[/code:1]

[code:1]
/*Created on 2005-7-15*/
package com.adt.dao.impl;

import java.util.List;

import org.flyware.util.page.Page;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;

import com.adt.dao.UserDAO;

/**
* @author Joa
*/
public class UserDAOImpl extends BaseDAOHibernateImpl implements UserDAO {

/* (non-Javadoc)
* @see com.adt.dao.UserDAO#getUserByName(java.lang.String)
*/
public List getUserByName(String name) throws HibernateException {
String querySentence = "FROM user in class com.adt.po.User WHERE user.name=:name";
Query query = getSession().createQuery(querySentence);
query.setParameter("name", name);
return query.list();
}

/* (non-Javadoc)
* @see com.adt.dao.UserDAO#getUserCount()
*/
public int getUserCount() throws HibernateException {
int count = 0;
String querySentence = "SELECT count(*) FROM user in class com.adt.po.User";
Query query = getSession().createQuery(querySentence);
count = ((Integer)query.iterate().next()).intValue();
return count;
}

/* (non-Javadoc)
* @see com.adt.dao.UserDAO#getUserByPage(org.flyware.util.page.Page)
*/
public List getUserByPage(Page page) throws HibernateException {
String querySentence = "FROM user in class com.adt.po.User";
Query query = getSession().createQuery(querySentence);
query.setFirstResult(page.getBeginIndex())
.setMaxResults(page.getEveryPage());
return query.list();
}

}
[/code:1]

至此，一个完整的分页程序完成。前台的只需要调用userManager.listUser(page)即可得到一个Page对象和结果集对象的综合体，而传入的参数page对象则可以由前台传入，如果用webwork，甚至可以直接在配置文件中指定。

下面给出一个webwork调用示例：
[code:1]
/*Created on 2005-6-17*/
package com.adt.action.user;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.flyware.util.page.Page;

import com.adt.bo.Result;
import com.adt.service.UserService;
import com.opensymphony.xwork.Action;

/**
* @author Joa
*/
public class ListUser implements Action {

private static final Log logger = LogFactory.getLog(ListUser.class);

private UserService userService;

private Page page;

private List users;

/*
* (non-Javadoc)
*
* @see com.opensymphony.xwork.Action#execute()
*/
public String execute() throws Exception {
Result result = userService.listUser(page);
page = result.getPage();
users = result.getContent();
return SUCCESS;
}

/**
* @return Returns the page.
*/
public Page getPage() {
return page;
}

/**
* @return Returns the users.
*/
public List getUsers() {
return users;
}

/**
* @param page
* The page to set.
*/
public void setPage(Page page) {
this.page = page;
}

/**
* @param users
* The users to set.
*/
public void setUsers(List users) {
this.users = users;
}

/**
* @param userService
* The userService to set.
*/
public void setUserService(UserService userService) {
this.userService = userService;
}
}
[/code:1]

上面的代码似乎看不出什么地方设置了page的相关初值，事实上，可以通过配置文件来进行配置，例如，我想每页显示10条记录，那么只需要：
[code:1]
<?xml version="1.0"?>
<!DOCTYPE xwork PUBLIC "-//OpenSymphony Group//XWork 1.0//EN" "http://www.opensymphony.com/xwork/xwork-1.0.dtd">

<xwork>

<package name="user" extends="webwork-interceptors">

<!-- The default interceptor stack name -->
<default-interceptor-ref name="myDefaultWebStack"/>

<action name="listUser" class="com.adt.action.user.ListUser">
<param name="page.everyPage">10</param>
<result name="success">/user/user_list.jsp</result>
</action>

</package>

</xwork>

[/code:1]

这样就可以通过配置文件和OGNL的共同作用来对page对象设置初值了。并可以通过随意修改配置文件来修改每页需要显示的记录数。

注：上面的<param>的配置，还需要webwork和Spring整合的配合。

我写的一个用于分页的类，用了泛型了，hoho

[code:1]
package com.intokr.util;

import java.util.List;

/**
* 用于分页的类<br>
* 可以用于传递查询的结果也可以用于传送查询的参数<br>
*
* @version 0.01
* @author cheng
*/
public class Paginator<E> {
private int count = 0; // 总记录数
private int p = 1; // 页编号
private int num = 20; // 每页的记录数
private List<E> results = null; // 结果

/**
* 结果总数
*/
public int getCount() {
return count;
}

public void setCount(int count) {
this.count = count;
}

/**
* 本结果所在的页码，从1开始
*
* @return Returns the pageNo.
*/
public int getP() {
return p;
}

/**
* if(p<=0) p=1
*
* @param p
*/
public void setP(int p) {
if (p <= 0)
p = 1;
this.p = p;
}

/**
* 每页记录数量
*/
public int getNum() {
return num;
}

/**
* if(num<1) num=1
*/
public void setNum(int num) {
if (num < 1)
num = 1;
this.num = num;
}

/**
* 获得总页数
*/
public int getPageNum() {
return (count - 1) / num + 1;
}

/**
* 获得本页的开始编号，为 (p-1)*num+1
*/
public int getStart() {
return (p - 1) * num + 1;
}

/**
* @return Returns the results.
*/
public List<E> getResults() {
return results;
}

public void setResults(List<E> results) {
this.results = results;
}

public String toString() {
StringBuilder buff = new StringBuilder();
buff.append("{");
buff.append("count:").append(count);
buff.append(",p:").append(p);
buff.append(",nump:").append(num);
buff.append(",results:").append(results);
buff.append("}");
return buff.toString();
}

}

[/code:1]

感谢大家的分享
尤其是看到了 Projections 的咚咚
以前用Criteria的时候就是没法做count才改用HQL，现在能够这样做的话，还是相当理想的。
我看简单应用的后台分页部分这样基本上也算是圆满了，如果用tapestry，再构造一个支持后台数据库分页，排序的东西，将会带来极大的方便，嘿嘿

今天看Robin的代码，测试了一下，发现一个问题。
public PaginationSupport findPageByCriteria(final DetachedCriteria detachedCriteria, final int pageSize,
final int startIndex)
这里如果detachedCriteria中，加入了Order对象，在函数中，计算
int totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
criteria.setProjection(null);
会报告异常，指示Order对象有问题。
假设查询语句为：
from Person where type ＝ 2 order by logTime

在计算总个数时，Hibernate输出的SQL语句是
select count(*) from Person where type ＝ 2 order by logTime
语法出错。

是否在函数中，将Order对象单独处理，作为参数传入。
public PaginationSupport findPageByCriteria(final DetachedCriteria detachedCriteria, Order order，final int pageSize,
final int startIndex){
return (PaginationSupport) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria criteria = detachedCriteria.getExecutableCriteria(session);
int totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
criteria.setProjection(null);
criteria.addOrder(order);
List items = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
PaginationSupport ps = new PaginationSupport(items, totalCount, pageSize, startIndex);
return ps;
}
}, true);
}

重要的 page的算法
http://pear.php.net/package/Pager

而不是 如何从数据库中 获取分页的数据

最好同时提供测试代码

我个人认为，如果在Web层或业务层构造detachedCriteria，会造成整个应用对hibernate的依赖，限定了data access一定要由hibernate实现，如果想要替换成其他实现就会牵涉到多个层的改动。
问题：
有没有其他的解决方案呢？

这段代码有如下几个问题：
1。如果cirteria设置了projection，这里将其改变，那么查出的items则是不正确的。因此只要将tocalcount和items的位置换一下即可了
2。如果criteria设置了order的话，那么criteria生成的sql将会有语法错误，比如order by中的某某字段不在聚合函数中，如果能将criteria的order去掉就好了，可是criteria没有这个api......

- 1.cirteria设置了projection统计完rowCount后又setProjection(null)，所以“基本”上应该是可行的。
- 2.这个order问题的确很伤心，我的做法是将Order[]作为一个参数传入，在rowCount统计完成后，再在criteria中加入。

这种分页做法很优美，完全把分页程序与具体表分离了，复用性很高。遗憾的是DetachedCriteria/Criteria现在实现的还不是十分完善，比如：
我用的hibernate是3.0.5，发现多次使用同一个DetachedCriteria对象后，在作rowCount projection时会出错，返回null（可前几次都返回了正确的结果）。

该方法对于单表查询应该还是没多大问题的，对于某些复杂的查询条件需要做更多的测试。

1:具体的说是单表查询可以，但是如果关联其它表，或者createAlias，返回的items就会有问题，掉换顺序也不行，items里面的每个元素都是一个数组。我的解决办法是再加一个ResultTransformer参数，在setProjection(null)后再setResultTransformer

DetachedCriteria确实实现的不是很完善，我们希望它是一个无状态的，仅保存查询条件的值对象，但实际上它做不到。一个DetachedCriteria对象反复做分页查询，第一次查询时调用的setMaxResults方法和setFirstResult方法后，这个状态保存在DetachedCriteria上了，会影响下一次count操作，因此每次查询必需new一个DetachedCriteria。同样的原因导致第一个问题交换操作顺序也不行。