java調用Oracle分頁存儲過程

Java程式

package com.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import com.jdbc.BaseJdbcDAO;
import oracle.jdbc.OracleTypes;
public class Test {
/**
* @param args
*/
public static void main(String[] args) {
Connection conn = BaseJdbcDAO.getConnection2("com");
String sqlSel = "SELECT * FROM POSITION";
String sqlCount = "SELECT COUNT(*) FROM POSITION";
String sql = "{ call SP_PAGE(?,?,?,?,?,?,?) }";
ResultSet rs = null;
try {
CallableStatement call = conn.prepareCall(sql);
call.setInt(1, 20);
call.setInt(2, 100);
call.setString(3, sqlSel);
call.setString(4, sqlCount);
call.registerOutParameter(5, Types.INTEGER);
call.registerOutParameter(6, Types.INTEGER);
call.registerOutParameter(7, OracleTypes.CURSOR);
call.execute();
// 取出結果集
int pageCount = call.getInt(5);//總頁數
int total = call.getInt(6);//總記錄數


rs = (ResultSet) call.getObject(7);//信息列表集
System.out.println("頁總數：" + pageCount);
System.out.println("記錄總數：" + total);
int i = 1;
while (rs.next()) {
System.out.println(">>" + i + "號碼：" + rs.getString(2) + "名稱："
+ rs.getString(3) + "序號:" + rs.getInt(1));
i++;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}

}/* 何問起 hovertree.com */

--創建游標包

create or replace PACKAGE "PKG_QUERY" AS
type refCursorType is REF CURSOR; --游標類型定義，用於返回數據集
END;/* 何問起 hovertree.com */

--存儲過程

create or replace procedure sp_Page(p_PageSize int, --每頁記錄數
p_PageNo int, --當前頁碼,從 1 開始
p_SqlSelect varchar2, --查詢語句,含排序部分
p_SqlCount varchar2, --獲取記錄總數的查詢語句
p_pageCount out int,--總共多少頁
p_OutRecordCount out int,--返回總記錄數
p_OutCursor out PKG_QUERY.refCursorType)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
  begin
  ----取記錄總數
      execute immediate p_SqlCount into v_count;
      p_OutRecordCount := v_count;
      --計算mypageCount--
    if mod(v_count,p_PageSize)=0 then
        p_pageCount:=v_count/p_PageSize;
    else
        p_pageCount:=v_count/p_PageSize+1;
    end if;


     
    ----執行分頁查詢
      v_heiRownum := p_PageNo * p_PageSize;
      v_lowRownum := v_heiRownum - p_PageSize + 1;


      v_sql := 'SELECT B.* FROM (SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) || ' ) B WHERE rn >= ' || to_char(v_lowRownum) ;
    --註意對rownum別名的使用,第一次直接用rownum,第二次一定要用別名rn


OPEN p_OutCursor FOR v_sql;


end sp_Page;/* 何問起 hovertree.com */

推薦:http://www.cnblogs.com/roucheng/p/3504465.html