应用Hibernate3的DetachedCriteria实现分页查询

楼上的，你写的东西恐怕有问题吧。

String countQueryString = " select count (*); " + JdbcUtils.removeSelect(JdbcUtils.removeOrders(queryString););; 

就看这句，如果有group by呢？

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

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

首先，为了实现分页查询，我封装了一个Page类：

/*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;
    }
    
}

上面的这个Page类对象只是一个完整的Page描述，接下来我写了一个PageUtil，负责对Page对象进行构造：

/*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;
    }
    

}

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

面对一个具体的业务逻辑：分页查询出User，每页10个结果。具体做法如下：
1. 编写一个通用的结果存储类Result，这个类包含一个Page对象的信息，和一个结果集List：

/*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;
    }
}

2. 编写业务逻辑接口，并实现它（UserManager， UserManagerImpl）

/*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;

}

/*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);;
    }

}

其中，UserManagerImpl中调用userDAO的方法实现对User的分页查询，接下来编写UserDAO的代码：
3. UserDAO 和 UserDAOImpl:

/*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;

}

/*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();;
    }

}

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

下面给出一个webwork调用示例：

/*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;
    }
}

上面的代码似乎看不出什么地方设置了page的相关初值，事实上，可以通过配置文件来进行配置，例如，我想每页显示10条记录，那么只需要：

<?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>

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

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

我也遇到了与"破碎虚空"同样的问题,调用代码如下

		DetachedCriteria detachedCriteria = DetachedCriteria
				.forClass(User.class);;

		//if (user.getGroup(); != null);
		//	detachedCriteria.add(Restrictions.eq("group", user.getGroup();););;
		
		PaginationSupport ps = findPageByCriteria(detachedCriteria,startIndex,pageSize);;
		return ps;

然后调用  ps.getItems(); 反回的结果里竟然是一个integer类型的数据而不是User对象,
希望Robin贴出使用代码

int totalCount = ((Integer); criteria.setProjection(Projections.rowCount(););.uniqueResult(););.intValue();;
criteria.setProjection(null);;						criteria.setResultTransformer(Criteria.ROOT_ENTITY);;

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

criteria.setFirstResult(0);.setMaxResults(totalCount);;

我也遇到了与"破碎虚空"同样的问题,调用代码如下

		DetachedCriteria detachedCriteria = DetachedCriteria
				.forClass(User.class);;

		//if (user.getGroup(); != null);
		//	detachedCriteria.add(Restrictions.eq("group", user.getGroup();););;
		
		PaginationSupport ps = findPageByCriteria(detachedCriteria,startIndex,pageSize);;
		return ps;

然后调用  ps.getItems(); 反回的结果里竟然是一个integer类型的数据而不是User对象,
希望Robin贴出使用代码