jdbc调用通用存储过程完成分页

 

分页是我们做WEB系统必备的技术了，但是分页的方式却多种多样，有客户端分页，服务器端分页，以及它们的折中等等。这里我们简单的说一下：

 

客户端分页：由WEB服务器的组件一次性从数据库服务器的表中取出全部数据，再存储在自身的内存中。好处是以后需要某页数据时不用再重新查找数据库，直接从自身的内存中取到，大大节约时间开销；坏处是浪费了内存空间，当数据海量时明显不合适。

服务器端分页：每当用户需要浏览某一页的数据时，就由WEB服务器的组件建立和数据库服务器的连接并取出相应数据。好处是本着节约精神，用多少就取多少；坏处是每次分页都要访问数据库，时间开销大，性能下降。

二者的折中：既然没有完美，那我们只有折中了。所谓折中当然就是在反应时间和内存空间上取得平衡，典型的例子就是baidu和google搜索的分页了。我们可以每次取10页或者20页的数据存储在WEB服务器的内存中，这样当某页的数据存在于内存就直接从中取出，不存在再去访问数据库。既不占用大量内存空间，也不用每次翻页都要花时间去访问数据库。

 

       我们在这里给大家介绍的主要是服务器端分页，并且用存储过程来实现。以sqlserver2000为例，我们先来看一下一个查找book表(主键为b_id)进行分页的简单存储过程：

CREATE PROC findAllBookDemo

@pageIndex int,

@pageSize int

as

begin

declare @SQL varchar(1000)

set @SQL='select top '

       +cast(@pageSize as varchar)

       +' * from book where b_id not in (select top '

       +cast((@pageIndex-1)*@pageSize as varchar)

       +' b_id from book)'

exec (@SQL)

end

       在sqlserver 2000的查询分析器中运行exec findAllBookDemo 1,3就可以查询到第1页的最多3条记录。

       但这个存储过程太简单了，它只能查找book表，而且不能排序和过滤，下面我们来看一个更通用的分页存储过程：

/*

经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：

exec GetRecordByPage tableName,id,10,100000

tableName为表名, id为主键

*/

 

/*

函数名称: GetRecordByPage

函数功能: 获取指定页的数据

参数说明: @tblName 包含数据的表名

@fldName 关键字段名

@PageSize 每页记录数

@PageIndex 要获取的页码

@OrderType 排序类型, 0 - 升序, 1 - 降序

@strWhere 查询条件 (注意: 不要加 where)

*/

CREATE PROCEDURE GetRecordByPage

@tblName varchar(255), -- 表名

@fldName varchar(255), -- 字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序

@strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)

AS

 

declare @strSQL varchar(6000) -- 主语句

declare @strTmp varchar(1000) -- 临时变量

declare @strOrder varchar(500) -- 排序类型

 

if @OrderType != 0

begin

set @strTmp = '<(select min'

set @strOrder = ' order by [' + @fldName + '] desc'

end

else

begin

set @strTmp = '>(select max'

set @strOrder = ' order by [' + @fldName +'] asc'

end

 

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'

+ @strOrder

 

if @strWhere != ''

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

 

if @PageIndex = 1

begin

set @strTmp = ''

if @strWhere != ''

set @strTmp = ' where (' + @strWhere + ')'

 

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + ']' + @strTmp + ' ' + @strOrder

end

exec (@strSQL)

GO

 

       在sqlserver 2000的查询分析器中运行exec GetRecordByPage book,b_id,3,1,1,'b_id<>3'就可以查询到book表中第1页的最多3条的记录。它以b_id为主键，过滤条件为b_id<>3,并且按照降序排列。那这样是不是大功告成了呢？当然没有，我们还要能够用JDBC操作这个存储过程来获得查询的记录呢！核心测试代码如下：

              try {

                     CallableStatement cs = con.prepareCall("{call GetRecordByPage(?,?,?,?,?,?)}");

                     cs.setString(1, "book");

                     cs.setString(2, "b_id");

                     cs.setInt(3, 3);

                     cs.setInt(4, 1);

                     cs.setInt(5, 1);

                     cs.setString(6, "b_id<>3");

                     ResultSet rs = cs.executeQuery();

                     while(rs.next()){

                            System.out.println(rs.getInt(1)+" "+rs.getString(2));

                     }

              } catch (SQLException e) {

                     // TODO Auto-generated catch block

                     e.printStackTrace();

              }

       }

}

       呵呵，这样子你就知道在java WEB应用中如何根据不同状况来使用不同的分页策略，更重要的是怎么样写一个高效而通用的存储过程来完成分页功能了。当然最终少不了获取存储过程查询出来的数据，而这一点，对于JDBC而言，就和操作普通SQL语句一样简单。好啦，时间紧迫，就写到这里，祝各位学习愉快！