java存儲過程分頁

import Java.sql.CallableStatement;
import Java.sql.Connection;
import Java.sql.DriverManager;
import Java.sql.ResultSet;
import Java.sql.Types;
import Java.util.ArrayList;
/**
 * @author chengtai.he
 * @version:1.0
 * @describe: We can go forward together.
 */
public class Procedure_Fenye {
 private static Connection conn = null;
 private static CallableStatement  csta = null;
 private static ResultSet rs = null;
 private static final String  DRIVERSTRING2005 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 private static final String  URL2005 ="jdbc:sqlserver://localhost:1433;databasename=pubs";
 
 /**
  * 獲得數據庫的連接
  */
 public void myConn()
 {
  try {
   Class.forName(DRIVERSTRING2005);
   conn = DriverManager.getConnection(URL2005, "sa", "sa");
     }
     catch (Exception ex) {
      System.out.println ("數據庫連接失敗!");
     }
 }

 /**
  * @param  每頁的記錄數  parPageSize
  * @return 總頁數 pages ,總記錄數 record
  */
 public int[] getPage(int parPageSize)
 {
  myConn();
  int pages=0;
  int record=0;
  int[] pageAndRecord = new int[2];
  try {
   //調用存儲過程
   csta=conn.prepareCall("{call proc_GetPagesAndRecord(?,?,?)}");
   //給輸入參數賦值
   csta.setInt(1,parPageSize);
   //指定輸出參數類型
   csta.registerOutParameter(2,Types.INTEGER);
   csta.registerOutParameter(3, Types.INTEGER);
   //執行存儲過程
   csta.execute();
   pages=csta.getInt(2);
   record=csta.getInt(3);
   pageAndRecord[0]=pages;
   pageAndRecord[1]=record;
   return pageAndRecord;
     }
     catch (Exception ex) {
      System.out.println("調用分頁存儲過程出錯!");
      return pageAndRecord;
     }
 }
 
 /**
  * 獲取結果
  * @param parPageSize 頁面大小
  * @param parCurrentPage 當前頁
  * @return 查詢結果
  */
 public ArrayList getRecord(int parPageSize,int parCurrentPage) {
  try {
   myConn();
   csta = conn.prepareCall("{call proc_FenYe(?,?)}");
   csta.setInt(1,parPageSize);
   csta.setInt(2,parCurrentPage);
   rs=csta.executeQuery();
   
   ArrayList products = new ArrayList();
   while (rs.next()) {
    ArrayList row = new ArrayList();
    row.add(rs.getString(1));
    row.add(rs.getString(2));
    row.add(rs.getString(3));
    products.add(row);
   }
   return products;
  } catch (Exception e) {
   System.out.println(e.getMessage());
  }

  return null;
 }

 public static void main(String[] args) {
  Procedure_Fenye pf = new Procedure_Fenye();
  int pageSize=5;
  ArrayList aa = pf.getRecord(pageSize, 1);
  for (Object each : aa) {
   System.out.println(((ArrayList) each).get(0) + " || "+ ((ArrayList) each).get(1) + " || "+ ((ArrayList) each).get(2));
  }
  int[] pagesAndRecord = pf.getPage(pageSize);
  System.out.println("每頁"+pageSize+"條記錄.一共可以分"+pagesAndRecord[0]+"頁.共"+pagesAndRecord[1]+"條記錄");
 }
}

/*附:存儲過程

create proc proc_GetPagesAndRecord
@pagesize int,@n int output, @total int output
as
select @total=count(*)
from titles
select @n=ceiling(cast(@total as float)/@pagesize)

create proc proc_FenYe
@pagesize int,@currentpage int
as
declare @str varchar(1000)
declare @rowcount int
declare @totalpage int
select @rowcount=count(*) from titles
select @totalpage=ceiling(cast(@rowcount as float)/@pagesize)
if @currentpage>1
begin
 if @currentpage>@totalpage
 begin
  set @currentpage=@totalpage
 end
 set @str='select top '+cast(@pagesize as varchar(5))+' * from titles where title_id not in(select top '+cast(@pagesize*(@currentpage-1) as varchar(5))+' title_id from titles order by title_id) order by title_id'
end
else
begin
set @str='select top '+cast(@pagesize as varchar(5))+' * from titles order by title_id'
end
exec(@str)
go
*/