本文介绍了一种分页查询优化方案，通过预搜索获取主键ID集与分页显示相结合的方式减少数据库负担，提高查询效率。该方案适用于大型数据集查询场景。
关于分页显示方面的文章，网上搜索会有好几大箩，当然每个人都有自己的思路和出发点。我的基本思路也很简单，就是将搜索过程分为两个部分：“预搜索”和“分页显示”。预搜索负责查询记录主键ID集，并且在整个查询分页过程中只执行一次；分页显示负责从预搜索的主键ID集中找出当前要显示的记录，每进行一次翻页操作便执行一次，从而实现分页显示功能。由于查询的Where条件为主键ID，并且每次只查询当页显示的记录数（比如：20条），所以在重量级查询中有着一定的性能优势。
本文主要从以下三个方面（三篇文章）来进行讲解：
1. 构建生成分页变量实体、存储查询结果实体
2. 预搜索类、分页搜索类、HibernateUtil工具类部分关联代码
3. 实现预搜索、分页搜索的两个类及用Struts的Action调用分页实例
注：View视图层的JSP代码未贴出来，相信大家在得到Result后都知道如何用Struts标签显示
首先我们为查询分页构建三个基础类：Page（保存分页的当前状态值）、PageUtil（生成Page）、Result（保存查询结果），代码都有详细的文档解释，就不再另作说明了，这三个类的代码分别如下：
/**********************************Page类*********************************************/
package com.nyhr.struts.page;
/**
* 分页实体类，保存当前分页状态变量
* @author Yeno.hhr
*/
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 total record number */
private int totalRecords;
/** 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 totalRecords,
int currentPage, int beginIndex) {
this.hasPrePage = hasPrePage;
this.hasNextPage = hasNextPage;
this.everyPage = everyPage;
this.totalPage = totalPage;
this.totalRecords = totalRecords;
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;
}
/**
* @param totalRecords
* The totalRecords to set.
*/
public void settotalRecords(int totalRecords)
{
this.totalRecords = totalRecords;
}
/**
* @return Returns the totalRecords.
*
*/
public int getTotalRecords()
{
return this.totalRecords;
}
}
/**********************************PageUtil类*********************************************/
package com.nyhr.struts.page;
/**
* 分页工具类，初始化Page对象
* @author Yeno.hhr
*/
public class PageUtil {
/**
* 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, totalRecords,
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;
}
}
/**********************************Result类*********************************************/
package com.nyhr.struts.page;
import java.util.List;
/**
* <p>Title: 检索结果集实体类</p>
* <p>Description: 保存分页参数及数据库查询的结果,用于页面显示</p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 四方人才网</p>
* @author Yeno.hhr
* @version 1.0
*/
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;
}
/**
* The content to set.
* @param content
*/
public void setContent(List content) {
this.content = content;
}
/**
* The page to set.
* @param page
*/
public void setPage(Page page) {
this.page = page;
}
}
现在展示在大家面前的是查询分页两个核心类 ：AbstractSearch（预查询初始化程序）、AbstractList（分页及初始化分页程序），代码如下：
/********************************AbstractSearch类************************************/
package com.nyhr.struts.frame;
import java.util.List;
import com.nyhr.struts.beans.HibernateUtil;
/**
* <p>Title: 预查询初始化程序</p>
* <p>Description: 所有的初始化查询必须继承此类，本类只负责预查询ID集和Page对象的初始化，不实现显示逻辑</p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 四方人才网</p>
* @author Yeno.hhr
* @version 1.0
*/
public abstract class AbstractSearch {
public AbstractSearch()
{
super();
}
/**
* 根据HQL查询出记录的主键ID（主索引）集合
* 注：此hql必须是只检索主键及复合主键的查询语句，具体见应用实例
* @param hql 不带查询的查询语句
* @return idArray 主索引集合（可以主键ID，也可以是复合ID）
*/
public Object[] getIDList(String hql)
{
List list = HibernateUtil.query(hql);
if (list==null || list.size()==0)
return null;
return list.toArray();
}
/**
* 根据HQL查询出记录的主键ID（主索引）集合
* 注：此hql必须是只检索主键及复合主键的查询语句，具体见应用实例
* @param hql 带参数的查询语句
* @param bean 参数设置实体类
* @return Object[] 主索引集合（可以主键ID，也可以是复合ID）
*/
public Object[] getIDList(String hql, Object bean)
{
List list = HibernateUtil.query(hql,bean);
if (list==null || list.size()==0)
return null;
return list.toArray();
}
/**子类方法：根据子类的需要选择调用AbstractSearch的“带参”和“不带参”两种查询中的一种返回主键ID的数组集*/
abstract public Object[] getList();
/**子类方法：设定查询条件*/
abstract protected void condition();
}
/********************************AbstractList类************************************/
package com.nyhr.struts.frame;
import com.nyhr.struts.page.*;
import com.nyhr.struts.constant.SysConstant;
import com.nyhr.struts.hibernate.HibernateSessionFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import java.util.List;
import java.util.ArrayList;
/**
* <p>Title: 分页及初始化分页程序</p>
* <p>Description: 所有的初始化分页必须继承此类，如果是预查询调用，同时会初始化Page实体，否则Page实体会由FormBean提交生成</p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 四方人才网</p>
* @author Yeno.hhr
* @version 1.0
*/
abstract public class AbstractList {
private Page page;
/**查询结果的主键ID集*/
private Object[] idList;
public AbstractList(){}
/**
* 预查询初始化分页构造（初次查询）
* @param hql 查询语句
* @param search 预查询类
*/
public AbstractList(AbstractSearch search){
this.idList=search.getList();
}
/**
* 查询分页构造（分页查询）
* @param page 分页状态实体
* @param idList
*/
public AbstractList(Page page, Object[] idList){
this.idList=idList;
this.page=page;
}
/**子类方法：设置分页查询的查询语句*/
abstract protected String getHql();
/**
* 返回查询结果
* @return Result
*/
public Result getResult(){
if(page==null){
if(idList==null)
this.page = PageUtil.createPage(SysConstant.PAGE_SIZE,1,0);
else
this.page = PageUtil.createPage(SysConstant.PAGE_SIZE,1,idList.length);
}
return new Result(page, getListByPage());
}
/**
* 分页查询，返回当前页的查询结果集
* @param hql
* @return list 结果集
*/
public List getListByPage(){
List list = null;
if (page.getTotalPage() < 1)
return list;
try{
String hql=getHql();
if(hql==null || hql.equals(""))
return list;
Object[] bean=getCurrentIds();
if(bean!=null)
list=HibernateUtil.query(hql,bean);
else
list=HibernateUtil.query(hql);
}catch(Exception e){
System.out.println(e.getMessage());
throw new RuntimeException(e);
}
return list;
}
/**
* 从查询结果的ID集中找出当前页的ID集
* @param arr 所有查询结果的主键ID集
* @return Object[]
*/
private Object[] getCurrentIds(){
if(idList==null) return null;
ArrayList<Object> list = new ArrayList<Object>();
int begin = page.getBeginIndex();
int ends = page.getTotalRecords();
int end = begin+page.getEveryPage();
if (end >= ends)
end = ends;
for (int l=begin;l<end;l++){
list.add(idList[l]);
}
return list.toArray();
}
/**
* 返回查询结果主键ID集的字符串组合形式
* @return String
*/
public String getIdList(){
String ids="";
if(idList == null)
return ids;
for(int x=0; x<idList.length; x++){
ids+=idList[x].toString();
if(x<idList.length-1)
ids+=",";
}
return ids;
}
}
/********************************HibernateUtil类************************************/
public class HibernateUtil {
private HibernateUtil(){}
/**
* 执行数据库查询,返回结果集List
* @param hsql HSQL查询语句
* @return list 结果集
*/
public static List query(String hql) {
List list = null;
Query query = null;
Session sess = HibernateSessionFactory.currentSession();
try{
//创建一条HQL查询语句
if (hql.toLowerCase().contains("from "))
query = sess.createQuery(hql);
else
query = sess.getNamedQuery(hql);
list = query.list();
}catch(HibernateException e) {
System.out.println("Hibernate Exception:@"+e.getMessage());
throw new RuntimeException(e);
}finally {
HibernateSessionFactory.closeSession();
}
return list;
}
public static List query(String hql, Object bean){
List list = null;
Query query = null;
Session sess = HibernateSessionFactory.currentSession();
try {
//创建一条HQL查询语句
if (hql.toLowerCase().contains("from "))
query = sess.createQuery(hql);
else
query = sess.getNamedQuery(hql);
query.setProperties(bean);
list = query.list();
}catch(HibernateException e) {
System.out.println("Hibernate Exception:@"+e.getMessage());
throw new RuntimeException(e);
} finally {
HibernateSessionFactory.closeSession();
}
return list;
}
}
扫一扫
695
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
