通用的分页查询存储过程

 

--**************************************************************************
--版本号Ver1.0  通用的分页查询存储过程
--编写目的：   
--编写人：        赵安家                                   
--编写日期：    2013-06-04
--修改目的：   
--最后修改人：   
--最后修改日期：
--**************************************************************************
CREATE proc [dbo].[PagerProc]
@tblName                        varchar(1000),                    -- 表名如：'Table1',可以是多表查询,格式为'Table1 as t1 left join Table2 t2 on t1.id=t2.id'
@strGetFields                    varchar(1000) = '*',            -- 需要返回的列 单表如：'Column1,Column2',多表如't1.Column1,t1.Column2,t2.Column1'
@orderName                        varchar(255)='',                -- 排序的字段名如：id'
@orderType                        bit=1,                            -- 如果为零则为降序排列
@strWhere                        varchar(1500) = '',                -- 查询条件(注意:不要加where)如:'id <100
@pageIndex                        int = 1,                        -- 页码如：2
@pageSize                        int = 20,                        -- 每页记录数如：20
@recordCount                    int output,                        -- 记录总数
@doCount                        bit=1,                            -- 非0则统计,为0则不统计(统计会影响效率)
@isOnly                            bit=1                            -- 是否是单表,如果是单表则为1,多表则为0
AS
DECLARE @strSQL                        VARCHAR(5000);                --Sql语句
DECLARE @strCount                    NVARCHAR(1000);                --记录总数
DECLARE @strOrder                    NVARCHAR(500);                --排序句

--处理where条件
IF  @strWhere !=''
BEGIN
  SET @strWhere=' where '+@strWhere;
END

--总记录条数
IF(@doCount!=0)
BEGIN
    SET @strCount='select @count=count(1) from '+@tblName+' '+@strWhere;
    EXECUTE sp_executesql @strCount ,N'@count INT output',@RecordCount OUTPUT
end

--排序句
IF(@orderType !=0)
BEGIN
  SET  @strOrder = 'ORDER BY ' + @orderName +' DESC'
END
ELSE
BEGIN
  SET  @strOrder = 'ORDER BY ' + @orderName +' ASC'
END
--如果是多表查询则处理查询列
IF(@isOnly!=1)
BEGIN
    DECLARE @int_pos1                    INT;                        --查询列中的,位置
    DECLARE @int_pos2                    INT;                        --查询列中的.位置
    DECLARE @int_len                    INT;                        --查询列的长度(@strGetFields的长度)
    DECLARE @str_GetFields                VARCHAR(1000);                --去掉别名后的列如@strGetFields='t1.Column1,t1.Column2,t2.Column1',则@str_GetFields=Column1,Column2,Column1

    SET @int_len=LEN(@strGetFields);
    SET @int_pos2=1;
    SET @int_pos1=1;
    SET @str_GetFields='';
    WHILE (@int_pos1 < @int_len AND @int_pos2>0)
    BEGIN
        SET @int_pos2=CHARINDEX('.',@strGetFields,@int_pos2+1);
        SET @int_pos1=CHARINDEX(',',@strGetFields,@int_pos1+1);
        IF(@int_pos1=0)
        BEGIN
            SET @int_pos1=@int_len;
        END
        SET @str_GetFields=@str_GetFields+SUBSTRING(@strGetFields,@int_pos2+1,@int_pos1-@int_pos2);
    END
END
ELSE
BEGIN
    SET @str_GetFields=@strGetFields;
    PRINT @strGetFields
    PRINT @str_GetFields
END
IF(@pageIndex<=1)
BEGIN
    SET @pageIndex=1;   
END

SET  @strSQL='SELECT '+@str_GetFields+' FROM (SELECT ROW_NUMBER() OVER ( '+@strOrder+') AS ROWID,';
SET  @strSQL=@strSQL+@strGetFields+' FROM ' +@tblName+@strWhere;
SET  @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1);
SET  @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize);
EXEC (@strSQL)

程序调用

public ResultSet queryPage(String orderCln,String clnNames,String tableName,String where,int pageIndex,int size,boolean isDesc,boolean isOnly,boolean doCount,int recordCount) throws SQLException, Exception {
          CallableStatement callStat=conn.prepareCall("{call PagerProc(?,?,?,?,?,?,?,?,?,?)}");
          callStat.setString(1, tableName);
          callStat.setString(2, clnNames);
          callStat.setString(3, orderCln);
          callStat.setInt(4, isDesc?0:1);
          callStat.setString(5, where);
          callStat.setInt(6, pageIndex);
          callStat.setInt(7, size);
          callStat.registerOutParameter(8, Types.INTEGER);
          callStat.setInt(9, doCount?1:0);
          callStat.setInt(10, isOnly?1:0);
          rst=callStat.executeQuery();
          while (rst.next()) {
              for (int i = 0; i < rst.getMetaData().getColumnCount(); i++) {
                System.out.println(rst.getObject(i+1));
            }
          }
          if (!callStat.getMoreResults()) {
            recordCount=callStat.getInt(8);
            System.out.println(recordCount);
        }
          return rst;
      }

 

测试过如果是单表,
如果是前1000页几乎秒开,越是往后越慢,这个存储过程,基本上应付小项目没问题,二十万级别的3秒,二百万需要30秒,(不是简单的两个字段的那种,测试表都是10个字段以上的),只测试过有主键的,用主键进行order by,其它请自测

from:网络