在Java中执行Oracle分页的存储过程

package com.ztf.test;


import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;


import oracle.jdbc.driver.OracleTypes;


public class TestProcedure {


/**

* @param args

*/

public static void main(String[] args) {

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

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

String user = "scott";

String pass="tiger";

Connection conn=null;


try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}


try {

conn = DriverManager.getConnection(url, user, pass);

} catch (SQLException e) {

e.printStackTrace();

}

//String sql = "{call pro_emp(?,?)}";

//try {

//CallableStatement cst =conn.prepareCall(sql);

//cst.setString(1, "SCOTT");

//cst.registerOutParameter(2, OracleTypes.INTEGER);

//cst.execute();

//System.out.println(cst.getInt(2));

//

//} catch (SQLException e) {

//e.printStackTrace();

//}


// 执行 带游标返回值的存储过程

//String sql ="{call pro_emp2(?)}";

//try {

//CallableStatement cst = conn.prepareCall(sql);

//cst.registerOutParameter(1, OracleTypes.CURSOR);

//cst.execute();

//ResultSet rs =(ResultSet) cst.getObject(1);

//while(rs.next()){

//System.out.print(rs.getString("DEPTNO")+" ");

//System.out.print(rs.getString("SAL")+" ");

//System.out.print(rs.getString("ENAME")+" ");

//System.out.print(rs.getString("EMPNO")+" ");

//System.out.println();

//}

//

//} catch (SQLException e) {

//e.printStackTrace();

//}


//

以分页的方式显示数据

//int page =2;

//int size=3;

//String sql="{call pro_emp3(?,?,?,?)}";

//try {

//CallableStatement cst = conn.prepareCall(sql);

//cst.setInt(1, page);//设置显示的页数；

//cst.setInt(2, size);//设置每页显示的条数；

//cst.registerOutParameter(3,OracleTypes.CURSOR);

//cst.registerOutParameter(4, OracleTypes.INTEGER);

//cst.execute();

//ResultSet rs =(ResultSet)cst.getObject(3);

//int content = cst.getInt(4);

//System.out.println("当前结果共有："+content+"条");

//System.out.println("当前页为："+page);

//while(rs.next()){

//System.out.print(rs.getString("RN")+" ");

//System.out.print(rs.getString("DEPTNO")+" ");

//System.out.print(rs.getString("ENAME")+" ");

//System.out.print(rs.getString("SAL")+" ");

//System.out.print(rs.getString("EMPNO")+" ");

//System.out.print(rs.getString("COMM")+" ");

//System.out.println("");

//}

//

//} catch (SQLException e) {

//e.printStackTrace();

//}


//根据不同的表进行分页；

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

int page=1;

int sizes=3;

String tableName="EMP";

try {

CallableStatement cst =conn.prepareCall(sql);

cst.setInt(1, page);

cst.setInt(2, sizes);

cst.setString(3, tableName);

cst.registerOutParameter(4,OracleTypes.INTEGER);

cst.registerOutParameter(5,OracleTypes.INTEGER);

cst.registerOutParameter(6,OracleTypes.CURSOR);

cst.execute();

ResultSet rst = (ResultSet) cst.getObject(6);

System.out.println("总记录数 ："+cst.getInt(4));

System.out.println("总页数："+cst.getInt(5));

System.out.println("当前页数为："+page);

int totle = (rst.getMetaData().getColumnCount());


while(rst.next()){

for(int i=1;i<=totle;i++){

System.out.print(rst.getString(i)+" ");

if(i==totle){

System.out.println("");

}

}

}

} catch (SQLException e) {

e.printStackTrace();

}

}


}