通用高效分页存储过程
1. 问题概述
1.不管是报表模板取数,还是一个WEB应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机。
2.更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。
3.最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。
关于如何使用游标实现分页这里不再叙述。现在流行的做法是使用子查询来进行分页查询。
2. 解决方案
比如要从数据库中取出从x条到y条的记录，代码如下：
FROM 表名
WHERE (主键 NOT IN
(SELECT TOP x-1 主键FROM 表名)
)
按照上面的思路，我们可以总结出分页查询的公式如下：
SELECT TOP 页大小 *
FROM 表名 WHERE (主键 NOT IN (SELECT TOP 页大小*（页数-1） 主键 FROM表名ORDER BY 排序字段))
ORDER BY 排序字段
按照这个公式，我们可以写出存储过程：
CREATE PROCEDUREpagination
(
@TableName nVARCHAR(4000), –表名
@PageIndex int–页码
@PageSize int–每页容纳的记录数
@PrimaryKey VARCHAR(255), –主键（或者有唯一约束的字段）
@SortKey VARCHAR(255) –排序字段
)
AS
DECLARE @StrSQL nVARCHAR(4000)
SET @StrSQL=‘SELECT TOP ‘+CAST(@PageSize AS VARCHAR(20))+‘ * FROM
‘+@TableName+‘ T WHERE T.’+@PrimaryKey+‘ NOT IN (SELECT TOP ‘+CAST((@PageSize*(@PageIndex-1))
AS VARCHAR(20))+‘ ‘+@PrimaryKey+‘ FROM ‘+@TableName+‘ ORDER BY ‘+@SortKey+’) ORDER BY ‘+@SortKey
PRINT @StrSQL
EXEC SP_ExecuteSql @StrSQL
GO
但这个存储过程有一个致命的缺点，就是它含有NOT IN字样。虽然我可以把它改造为：
SELECT TOP 页大小 *
FROM 表名a WHERE not exists
(select * from (select top (页大小*页数) * from 表名 order by 主键) b where b.主键=a.主键 )
order by 主键
即，用not exists来代替not in，二者的执行效率实际上是没有区别的。既便如此，用TOP 结合NOT IN的这个方法还是比用游标要来得快一些。
虽然用not exists并不能挽救上个存储过程的效率，但使用SQL SERVER中的TOP关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集，而我们在前面也已经提到了TOP的优势，通过TOP 即可实现对数据量的控制。
在分页算法中，影响我们查询速度的关键因素有两点：TOP和NOT IN。TOP可以提高我们的查询速度，而NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造NOT IN，同其他方法来替代它。
我们知道，几乎任何字段，我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值，所以如果这个字段不重复，那么就可以利用这些不重复的字段的max或min作为分页算法中分开每页的参照物。在这里，我们可以用操作符“>”或“<”号来完成这个使命如：
Select top 10 * from 表名 where id>200
于是就有了如下分页方案：
select top 页大小 *
from 表名
where id>
(select max(id) from
(select top ((页码-1)*页大小) id from 表名 order by id) as T
) order by id
我们可以做这样的测试，以下的三种sql语句得到的结果完全相同，我们可以考察他们的效率。
use pubs
select identity(int,1,1) as id,*
into test
from authors
go
–使用not in查询第二页数据
select top 5 *
from test
where id not in(select top 5 id from test order by id)
order by id
–使用exists查询第二页数据
select top 5 *
from test a
where not exists(select * from (select top 5 * from test order by id) as b where a.id=b.id)
order by a.id
–使用max查询第二页数据
select top 5 *
from test
where id>(select max(id) as id from (select top 5 id from test order by id) as T)
order by id
将这三个语句选中后，在查询分析器中选择：查询—〉显示估计的执行计划，得到三个查询的查询成本：查询1与查询2的成本完全一样，查询3的成本比查询1和查询2要低。
在确定了第二种分页方案后，我们可以据此写一个存储过程。下面的存储过程不仅含有分页方案，还会根据页面传来的参数来确定是否进行数据总数统计。
/*****************************************************
存储过程名称：pagination
功能:通用分页
返回值：无
参数：
1.表名(字符串，必要参数)
2.主键或者唯一约束字段（字符串，必要参数）
3.需不需要进行总数统计.（布尔类型，可选参数，默认为1。
1或者true是只统计总条数,不进行查询，
0或者false进行分页查询,不进行统计）
4.查询条件（字符串，可选参数，默认为无条件查询。
注意不要加where）
5.排序的字段名（字符串，可选参数，默认按照主键排序）
6.页码（整数，可选参数，默认第1页）
7.每页大小（整数，可选参数，默认每页10条记录）
8.排序类型（整数或布尔，可选参数，默认升序排序
1或true按降序排序
0或false按升序排列）
9.需要返回的列(字符串，可选参数,默认返回所有列)
author:周辉辉
*****************************************************/
CREATE PROCEDURE pagination
@TableName varchar(255), — 表名
@PrimaryKey varchar(255),–主键或者唯一约束字段
@DoCount bit = 0, — 需不需要进行总数统计.1是只统计总条数,不进行查询 0进行分页查询,不进行统计
@StrWhere varchar(1500) = ”, — 查询条件 (注意不要加 where)
@OrderField varchar(255)=@PrimaryKey, — 排序的字段名,默认按照主键排序
@PageIndex int = 1, — 页码
@PageSize int = 10, — 页尺寸
@OrderType bit = 0 ,– 设置排序类型非 0 值则降序
@StrGetFields varchar(1000) = ‘*’– 需要返回的列
AS
declare @strSQL varchar(5000) — 主语句
declare @strTmp varchar(110) — 临时变量
declare @strOrder varchar(400) — 排序类型
/********************************************
@DoCount传递过来的不是0，就执行总数统计
********************************************/
if @DoCount != 0
begin
if @StrWhere!=”
set @strSQL = ‘select count(*) as Total from ‘ + @TableName + ‘ where ‘+@strWhere
else
set @strSQL = ‘select count(*) as Total from ‘ + @TableName
end
/********************************************
以下的所有代码都是@DoCount为0的情况：
********************************************/
else
begin
/********************************************
确定是升序还是降序
********************************************/
if @OrderType != 0
begin
set @strTmp = ‘<(select min’
set @strOrder = ‘order by ‘ + @OrderField +’ desc’
end
else
begin
set @strTmp = ‘>(select max’
set @strOrder = ‘order by ‘ + @OrderField +’ asc’
end
/**********************************************
为了加快执行速度，判断一下是不是第一页
***********************************************/
if @PageIndex = 1
begin
if @strWhere != ”
set @strSQL =’select top ‘ + str(@PageSize) +’ ‘+@StrGetFields+ ‘ from ‘ + @TableName + ‘ where ‘ + @strWhere + ‘ ‘ + @strOrder
else
set @strSQL =’select top ‘ + str(@PageSize) +’ ‘+@strGetFields+ ‘ from ‘+ @TableName + ‘ ‘+ @strOrder
end
/**********************************************
不是第一页
***********************************************/
else
begin
if @strWhere=”
set @strSQL=’select top ‘+str(@PageSize)+’ ‘+@strGetFields+’ from ‘+ @TableName +’ where ‘+@PrimaryKey+@strTmp+’(‘+@PrimaryKey+’) as ‘+@PrimaryKey+’ from (select top ‘+str((@PageIndex-1)*@PageSize)+’ ‘+@PrimaryKey+’ from ‘+@TableName+’ ‘+@strOrder+’) as T) ‘+@strOrder
else
set @strSQL=’select top ‘+str(@PageSize)+’ ‘+@strGetFields+’ from ‘+ @TableName +’ where ‘+@PrimaryKey+@strTmp+’(‘+@PrimaryKey+’) as ‘+@PrimaryKey+’ from (select top ‘+str((@PageIndex-1)*@PageSize)+’ ‘+@PrimaryKey+’ from ‘+@TableName+’ where ‘+@StrWhere+’ ‘+@strOrder+’) as T) and ‘+@StrWhere+’ ‘+@strOrder
end
print @strSQL
end
execute(@strSQL)
3. 使用过程
A.在报表里使用,直接定义对应的参数,调用{call pagination(?,?,?,?,?,?,?,?,?)}
B.在JAVA代码使用过程:
1.连接数据库的DBManager.java代码：
package com.xaccp.common.db;
import java.sql.*;
public class DBManager {
public static final String DRIVERNAME=“com.microsoft.jdbc.sqlserver.SQLServerDriver”;
public static final String URL=“jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=bookstore”;
public static final String UID=“sa”;
public static final String PWD=“sa”;
private Connection conn=null;
public DBManager() throws ClassNotFoundException, SQLException {
getConnection();
}
/**
* 打开连接
* @throws SQLException
* @throws ClassNotFoundException
*/
private void getConnection() throws SQLException, ClassNotFoundException {
Class.forName(DRIVERNAME);
conn=DriverManager.getConnection(URL,UID,PWD);
}
/**
* 该方法执行增,删,改操作
* @param strSql String
* @throws SQLException
*/
public void executeUpdate(String strSql) throws SQLException {
Statement stat=conn.createStatement();
stat.executeUpdate(strSql);
}
/**
* 该方法执行查询操作,并返回结果集
* @param strSql String
* @return ResultSet
*/
public ResultSet executeQuerry(String strSql) throws SQLException {
Statement stat=conn.createStatement();
return stat.executeQuery(strSql);
}
/**
* 关闭数据库
*/
public void closeConnection() throws SQLException {
if(conn==null){
return;
}else{
if(!conn.isClosed()){
conn.close();
conn=null;
}
}
}
/**
* 该方法用于执行存储过程
* @param proc String
* @return CallableStatement
* @throws SQLException
*/
public CallableStatement getCallableStatement(String proc) throws
SQLException {
return conn.prepareCall(proc);
}
/**
* 该方法用于执行预编译的Sql语句
* @throws SQLException
*
*/
public PreparedStatement getPreParedStatment(String preStrSql) throws SQLException{
return conn.prepareStatement(preStrSql);
}
}
2.定义变量,调用分页存储过程,抽象类AbstractPageBean.java代码：
package com.xaccp.common.pagination;
import java.sql.*;
import java.util.*;
import com.xaccp.common.db.DBManager;
public abstract class AbstractPageBean {
private String tableName;
private String primaryKey;
private boolean doCount = false;
private String strWhere = “”;
private String orderField = primaryKey;
private int pageIndex = 1;
private int pageSize = 10;
private boolean orderType = false;
private String strGetFields = “*”;
/**
* 取得当前页页码
*
* @return int
*/
public int getPageIndex() {
return pageIndex;
}
/**
* 设置当前页码
*
* @param pageIndex:当前页码
*/
public void setPageIndex(int pageIndex) {
this.pageIndex = pageIndex;
}
/**
* 取得每页大小
*
* @return int
*/
public int getPageSize() {
return pageSize;
}
/**
* 设置每页大小
* <p>
* 默认为10条记录
*
* @param pageSize：每页大小.
*/
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
/**
* 需不需要进行总数统计
* <p>
* 1或者true是只统计总条数,不进行查询
* <p>
* 0或者false进行分页查询,不进行统计
* <p>
* 默认为只统计总条数,不进行查询
*
* @param doCount
*/
private void setDoCount(boolean doCount) {
this.doCount = doCount;
}
/**
* 设置排序的字段名
* <p>
* 默认按照主键排序
*
* @param orderField:排序的字段名
*/
public void setOrderField(String orderField) {
this.orderField = orderField;
}
/**
* 设置排序类型
* <p>
* 1或true按降序排序
* <p>
* 0或false按升序排列
* <p>
* 默认升序排序
*
* @param orderType:排序类型
*/
public void setOrderType(boolean orderType) {
this.orderType = orderType;
}
/**
* 设置主键或者唯一约束字段
* <p>
* 必要参数
*
* @param primaryKey:主键或者唯一约束字段
*/
public void setPrimaryKey(String primaryKey) {
this.primaryKey = primaryKey;
}
/**
* 设置需要返回的列
* <p>
* 默认返回所有列
* <p>
* 注意列与列之间用逗号隔开
*
* @param strGetFields:需要返回的列
*/
public void setStrGetFields(String strGetFields) {
this.strGetFields = strGetFields;
}
/**
* 设置查询条件
* <p>
* 为无条件查询
* <p>
* 注意不要加where
*
* @param strWhere：查询条件
*/
public void setStrWhere(String strWhere) {
this.strWhere = strWhere;
}
/**
* 设置表名或者视图名称
* <p>
* 必要参数
*
* @param tableName：表名或者视图名称
*/
public void setTableName(String tableName) {
this.tableName = tableName;
}
/**
* 获取记录总数
*
* @return 记录总数
* @throws SQLException
* @throws ClassNotFoundException
*/
public int getTatal() throws ClassNotFoundException, SQLException {
String proc = “{call pagination(?,?,?,?)}”;
this.setDoCount(true);//进行统计
DBManager dbm = new DBManager();
CallableStatement cstat = dbm.getCallableStatement(proc);
cstat.setString(1, this.tableName);
cstat.setString(2, this.primaryKey);
cstat.setBoolean(3, this.doCount);
cstat.setString(4, this.strWhere);
ResultSet rs = cstat.executeQuery();
int tatal = 0;
if (rs.next()) {
tatal = rs.getInt(“Total”);
}
dbm.closeConnection();
return tatal;
}
/**
* 需要子类实现的方法
* <p>
* 将查询到的结果集包装成对象放入集合
*
* @param rs
* @return
* @throws SQLException
*/
protected abstract ArrayList FillPageDate(ResultSet rs) throws SQLException;
/**
* 取得当前页的数据
* @return:当前页数据对象集合
* @throws ClassNotFoundException
* @throws SQLException
*/
public ArrayList getPageData() throws ClassNotFoundException, SQLException{
String proc = “{call pagination(?,?,?,?,?,?,?,?,?)}”;
DBManager dbm = new DBManager();
CallableStatement cstat = dbm.getCallableStatement(proc);
cstat.setString(1, this.tableName);
cstat.setString(2, this.primaryKey);
cstat.setBoolean(3, this.doCount);
cstat.setString(4, this.strWhere);
cstat.setString(5, this.orderField);
cstat.setInt(6, this.pageIndex);
cstat.setInt(7, this.pageSize);
cstat.setBoolean(8, this.orderType);
cstat.setString(9, this.strGetFields);
ResultSet rs = cstat.executeQuery();
return this.FillPageDate(rs);
}
}
3.实现抽象方法的子类PageBean.java代码：
package com.xaccp.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.xaccp.common.pagination.AbstractPageBean;
public class PageBean extends AbstractPageBean{
public PageBean(String tableName,String primaryKey) {
super.setTableName(tableName);
super.setPrimaryKey(primaryKey);
}
public PageBean() {
super();
}
public ArrayList FillPageDate(ResultSet rs) throws SQLException{
if(rs==null){
return null;
}
ArrayList<Book> books=new ArrayList<Book>();
Book book=null;
while(rs.next()){
book=new Book();
book.setId(rs.getInt(“id”));
book.setTitle(rs.getString(“title”));
book.setAuthor(rs.getString(“author”));
book.setPrice(rs.getFloat(“price”));
books.add(book);
}
return books;
}
}
4.测试代码：
public static void main(String[] args) {
AbstractPageBean pageBean=new PageBean(“bookinfo”,“id”);//1.表名(字符串，必要参数)2.主键或者唯一约束字段（字符串，必要参数）
pageBean.setStrWhere(“title like ‘%开发%’”);//查询条件（字符串，可选参数，默认为无条件查询,注意不要加where）
pageBean.setPageIndex(2);// 页码（整数，可选参数，默认第1页）
pageBean.setPageSize(5);// 每页大小（整数，可选参数，默认每页10条记录）
pageBean.setOrderField(“id”);//排序的字段名
pageBean.setStrGetFields(“id,title,author,price”);//需要返回的列(字符串，可选参数,默认返回所有列)
try {
Iterator it=pageBean.getPageData().iterator();
while(it.hasNext()){
Book book=(Book)it.next();
System.out.println(book.getId()+” | “+book.getTitle()+” | “+book.getAuthor()+” | “+book.getPrice());
}
System.out.println(“总共["+pageBean.getTatal()+"]条记录/每页["+pageBean.getPageSize()+"]条/当前第["+pageBean.getPageIndex()+"]页“);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
5.后台打印