Java调用Oracle的分页存储过程

1. 使用PL/SQL写分页存储过程

01create orreplace procedure pro(

02    v_in_table_namein varchar2,--表名

03    v_in_page_sizein number,--每页大小

04    v_in_page_nowin number,--当前页

05    v_out_page_countout number,--页数

06    v_out_row_countout number,--记录数

07    v_out_result_setout pkg.page_cursor--结果集

08  ) is

09    v_sql_stmt varchar2(2000);

10    v_start_index number :=v_in_page_size*(v_in_page_now-1)+1;--起始位置

11    v_end_index number :=v_in_page_size*v_in_page_now;--结束位置

12  begin

13    v_sql_stmt:='select * from (select t.*, rownum rn from (select * from '||v_in_table_name||') t where rownum<='||v_end_index||') where rn>='||v_start_index;

14    openv_out_result_set for v_sql_stmt;--打开游标

15    v_sql_stmt:='select count(*) from '||v_in_table_name;

16    executeimmediate v_sql_stmt intov_out_row_count;--查询出记录数

17    --计算页数

18    if mod(v_out_row_count,v_in_page_size)=0then

19      v_out_page_count:=v_out_row_count/v_in_page_size;

20    else

21      v_out_page_count:=v_out_row_count/v_in_page_size+1;

22    endif;

23end;

2. 使用Java程序调用该存储过程

01package com.wujilin.procedure;

02 

03import java.sql.CallableStatement;

04import java.sql.Connection;

05import java.sql.DriverManager;

06import java.sql.ResultSet;

07import java.sql.ResultSetMetaData;

08 

09public classProcedureTest {

10    publicstatic void main(String[] args) {

11        String url ="jdbc:oracle:thin:@127.0.0.1:1521:orcl";

12        String username ="scott";

13        String password ="tiger";

14        String driver ="oracle.jdbc.driver.OracleDriver";

15         

16        try{

17            Class.forName(driver);

18            Connection conn = DriverManager.getConnection(url, username, password);

19            String sql ="{call pro(?,?,?,?,?,?)}";

20            CallableStatement cstmt = conn.prepareCall(sql);

21            // 设置表名

22            cstmt.setString(1,"emp");

23            // 设置每一页的记录数

24            cstmt.setInt(2,4);

25            // 设置当前页

26            cstmt.setInt(3,2);

27            // 注册总共的页数

28            cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

29            // 注册总共的记录数

30            cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

31            // 注册结果集

32            cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

33            // 直接执行：execute()方法即可。

34            cstmt.execute();

35            // 将游标强制转换成ResultSet类型。

36            ResultSet rs = (ResultSet) cstmt.getObject(6);

37            intpageCount = cstmt.getInt(4);

38            System.out.println("页数："+ pageCount);

39            introwCount = cstmt.getInt(5);

40            System.out.println("记录数："+ rowCount);

41            ResultSetMetaData rsmd = rs.getMetaData();

42            while(rs.next()) {

43                for(int i = 0; i < rsmd.getColumnCount(); i++) {

44                    System.out.print(rsmd.getColumnLabel(i +1) + ":"+ rs.getObject(i + 1) +", ");

45                }

46                System.out.println();

47            }

48        } catch (Exception e) {

49             

50        }

51    }

52}