Oracle通用分页存储过程JDBC调用

转自：http://www.cnblogs.com/hnsdwhl/archive/2011/09/04/2166522.html

这个我试过，好使，贴出来，分享一下，

首先是分页存储过程

--创建包定义
create or replace package pack_page is
type t_cursor is ref cursor;
procedure GetDataByPage(
          p_tableName varchar2,
          p_fields varchar2,
          p_filter varchar2,
          p_sort varchar2,
          p_currentPage number,
          p_pageSize number,
          p_cursor out t_cursor,
          p_totalRecords out number
);
end pack_page;
--创建包体
create or replace package body pack_page is
procedure GetDataByPage(
          p_tableName varchar2, --要查询的表名
          p_fields varchar2,    --要查询的字段
          p_filter varchar2,    --过滤条件
          p_sort varchar2,      --排序字段以及方向
          p_currentPage number, --当前页
          p_pageSize number,    --每页长度
          p_cursor out t_cursor,--分页结果集
          p_totalRecords out number      --总记录数       
)
is
          v_sql varchar2(1000):='';    --执行的sql语句
          v_startRecord number(4);     --每页起始位置
          v_endRecord number(4);       --每页结束位置
begin
          v_sql:=' select to_number(count(*)) from '||p_tableName;
          --如果有过滤条件
          if p_filter is not null then
             v_sql:=v_sql||' where 1=1 and '|| p_filter;
          end if;
         
          execute immediate v_sql into p_totalRecords;  --设置总记录数
          
          v_startRecord:=(p_currentPage-1)*p_pageSize;  --设置当前页起始位置
          v_endRecord:=p_currentPage*p_pageSize;        --设置当前页结束位置
          
          v_sql:=' select ' ||p_fields||' from(select '||p_fields||' ,rownum r from '||
                 ' (select '||p_fields||' from '||p_tableName;
          --如果有过滤条件
          if p_filter is not null then
             v_sql:=v_sql||' where 1=1 and '|| p_filter;
          end if;
          --如果有排序条件
          if p_sort is not null then
             v_sql:=v_sql||' order by '||p_sort;
          end if;          
          v_sql:=v_sql||' ) A where rownum<= ' ||to_char(v_endRecord)||' )B where 
          r> '|| to_char(v_startRecord);
          --打开游标
          open p_cursor for v_sql;          
end GetDataByPage;
end pack_page;

--测试查询
declare
v_cur pack_page.t_cursor;
one_emp emp%rowtype;
v_totalRecords number;
begin
pack_page.GetDataByPage('emp','empno,ename,job,mgr,hiredate,sal,comm,deptno','sal>1100','sal desc',1,10,v_cur,v_totalRecords);
--fetch v_cur into one_emp;
while v_cur%found 
loop
      dbms_output.put_line(one_emp.empno||','||one_emp.ename);
      fetch v_cur into one_emp;
end loop;
dbms_output.put_line('总记录数为'||v_totalRecords);
end;      

通过JDBC调用测试，

package com.accp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class Test {
	public static void main(String[] args) {
		Test test = new Test();
		List<Employee> list = test.getData();
		for (Employee employee : list) {
			System.out.println(employee);
		}
	}

	private List<Employee> getData() {
		Connection con = DBConnect.getConnect();
		CallableStatement  cs = null;
		ResultSet rs = null;
		String sql = "{call pack_page.GetDataByPage(?,?,?,?,?,?,?,?)}";
		List<Employee> list = new ArrayList<Employee>();
		try {
			cs = con.prepareCall(sql);
			
			cs.setString(1, "emp");
			cs.setString(2, "empno,ename,job,mgr,hiredate,sal,comm,deptno");
			cs.setString(3, "sal>1100");
			cs.setString(4, "sal desc");
			cs.setInt(5, 1);
			cs.setInt(6, 10);
			cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
			cs.registerOutParameter(8, Types.INTEGER);
			cs.executeQuery();
			
			int totalCount = cs.getInt(8); 

		    System.out.println("共有" + totalCount + "条"); 

		    rs = (ResultSet) cs.getObject(7); 
			
			while(rs.next()){
				Employee emp = new Employee();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				
				Date date = rs.getDate(5);
				Date hiredate = new Date(date.getTime());
				emp.setHiredate(hiredate);
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			DBConnect.closeAll(con, cs, rs);
		}
		return list;
	}
}

测试结果：

共有11条
Employee [comm=0, deptno=10, empno=7839, ename=KING, hiredate=1981-11-17, job=PRESIDENT, mgr=0, sal=5000]
Employee [comm=0, deptno=20, empno=7788, ename=SCOTT, hiredate=1987-04-19, job=ANALYST, mgr=7566, sal=3000]
Employee [comm=0, deptno=20, empno=7902, ename=FORD, hiredate=1981-12-03, job=ANALYST, mgr=7566, sal=3000]
Employee [comm=0, deptno=20, empno=7566, ename=JONES, hiredate=1981-04-02, job=MANAGER, mgr=7839, sal=2975]
Employee [comm=0, deptno=30, empno=7698, ename=BLAKE, hiredate=1981-05-01, job=MANAGER, mgr=7839, sal=2850]
Employee [comm=0, deptno=10, empno=7782, ename=CLARK, hiredate=1981-06-09, job=MANAGER, mgr=7839, sal=2450]
Employee [comm=300, deptno=30, empno=7499, ename=ALLEN, hiredate=1981-02-20, job=SALESMAN, mgr=7698, sal=1600]
Employee [comm=0, deptno=30, empno=7844, ename=TURNER, hiredate=1981-09-08, job=SALESMAN, mgr=7698, sal=1500]
Employee [comm=0, deptno=10, empno=7934, ename=MILLER, hiredate=1982-01-23, job=CLERK, mgr=7782, sal=1300]
Employee [comm=500, deptno=30, empno=7521, ename=WARD, hiredate=1981-02-22, job=SALESMAN, mgr=7698, sal=1250]