利用存储过程进行分页 游标

利用存储过程进行分页

2012-03-07 15:53:15      我来说两句      

收藏     我要投稿

在开发中我们经常以列表的方式进行数据的访问，当访问的数据量非常的大的时候
 
我们就要进行分页，其中的一种方式就是利用存储过程进行分页。
 
环境：netbeans7.1 ,jdk 1.7 ,sqlserver 2008,tomcat 7
 
1.       存储过程的编写：
 
CREATE PROCEDUREproduce
 
@sqlstrnvarchar(4000),--查询字符串
 
@currentpageint,--第N页
 
@pagesizeint--每页行数
 
AS
 
BEGIN
 
         SETNOCOUNTON;
 
         declare--声明变量
 
         @P1int,-- 游标的ID
 
         @rowcountint,--总行记录
 
         @totalpageint--总页数
 
execsp_cursoropen@P1output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountoutput
 
set@totalpage=CEILING(1.0*@rowcount/@pagesize)--计算总页数
 
select@currentpageascurrpage,@totalpageastotalpage,@rowcountastotalrow--查出需要的值
 
if@currentpage>@totalpageset@currentpage=@totalpage--当前页码不能大于总页数
 
set@currentpage=(@currentpage-1)*@pagesize+1
 
execsp_cursorfetch@P1,16,@currentpage,@pagesize
 
execsp_cursorclose@P1
 
setnocountoff
 
END
 
系统参数说明：
 
 
 
/**
 
sp_cursoropen：
 
系统参数，打开游标。sp_cursoropen定义与游标和游标选项相关联的SQL 语句，然后填充游标。
 
sp_cursoropen等效于Transact-SQL 语句DECLARE_CURSOR 和OPEN 的组合。
 
此过程通过在表格格式数据流(TDS) 数据包中指定ID = 2 来调用。
 
参数说明：
 
cursor:@P1 游标id SQL Server 生成的游标标识符cursor 是一个具有int返回值的必需参数。
 
stmt:@sqlstr定义游标结果集的必需参数
 
scrollopt:@scrollopt滚动选项。scrollopt是一个可选参数.需要int输入值  0x0001 对应KEYSET
 
ccopt：@ccopt并发控制项0x0001 对应READ_ONLY
 
rowcount：@rowcount要用于AUTO_FETCH 的提取缓冲区行数。默认值为20 行。
 
**/
 
/**
 
sp_cursorfetch：
 
从 数据库中提取由一行或多行组成的缓冲区。此缓冲区中的行组称为游标的“提取缓冲区”。可通过在表格格式数据流(TDS) 包中指定ID = 7 来调用sp_cursorfetch。
 
参数说明：
 
cursor：@P1 游标的ID
 
fetchtype : 16
 
rownum: @currentpage
 
nrows :@pagesize
 
**/
 
/**
 
sp_cursorclose:
 
关闭游标并取消游标分配，以及释放所有关联资源；
 
**/
 
Java代码:
 
package Service;
 
 
 
importjava.sql.*;
 
 
 
public class Procedure {
 
         private Connection con;
 
         publicResultSetrs;
 
         privateCallableStatementcallsta;
 
         private String use = "sa";
 
         private String pwd = "123";
 
 
 
         public Procedure() {
 
                   try {
 
                            // 连接数据库驱动
 
                            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
 
                            String str = "jdbc:sqlserver://localhost:1433;databasename=tutorial";
 
                            con = DriverManager.getConnection(str, use, pwd);
 
                            // 设置存储过程参数
 
                            String st = "{call produce(?,?,?)}";
 
                            callsta = con.prepareCall(st);
 
                            callsta.setString(1, "select * from product");
 
                            callsta.setInt(2, 1);
 
                            callsta.setInt(3, 3);
 
                            // 循环输出调用存储过程的记录结果
 
                            StringBuffersb=new StringBuffer();
 
                            intrsNum=0;//统计结果集的数量
 
                            intupdateCount = -1;
 
                            boolean flag = callsta.execute();// 这个而尔值只说明第一个返回内容是更新计数还是结果集。
 
                            do {
 
                                     updateCount = callsta.getUpdateCount();
 
                                     if (updateCount != -1) {// 说明当前行是一个更新计数
 
                                               // 处理.
 
                                               System.out.println("..说明当前行是一个更新计数..");
 
                                               callsta.getMoreResults();
 
                                               continue;// 已经是更新计数了,处理完成后应该移动到下一行
 
                                               // 不再判断是否是ResultSet
 
                                     }
 
                                     rs = callsta.getResultSet();
 
                                     if (rs != null) {// 如果到了这里,说明updateCount == -1
 
                                               // 处理rs
 
                                               rsNum++;
 
                                               System.out.println("统计结果集的数量:"+rsNum);
 
                                               if (rs != null) {
 
                                                        ResultSetMetaDatarsmd = rs.getMetaData(); // 获取字段名
 
                                                        intnumberOfColumns = rsmd.getColumnCount(); // 获取字段数
 
                                                        inti = 0;
 
                                                        while (rs.next()) { // 将查询结果取出
 
                                                                 for (i = 1; i<= numberOfColumns; i++) {
 
//                                                                        System.out.println(rs.getInt("总页数"));
 
                                                                           String date = rs.getString(i);
 
                                                                           sb.append(date+" ");
 
                                                                 }
 
                                                        }
 
                                                        rs.close();
 
                                               }
 
                                               callsta.getMoreResults();
 
                                               continue;
 
                                               // 是结果集,处理完成后应该移动到下一行
 
                                     }
 
                                     // 如果到了这里,说明updateCount == -1 &&rs == null,什么也没的了
 
                                     System.out.println(sb.toString());
 
                            } while (!(updateCount == -1 &&rs == null));
 
                            // callsta.getXXX(int);//获取输出参数
 
                   } catch (Exception e) {
 
                            e.printStackTrace();
 
                   }
 
         }
 
         public static void main(String[] age) {
 
                   Procedure pro = new Procedure();
 
         }
 
}