oracle 分页过程,oracle分页存储过程 oracle存储过程实例

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class TestPage {

public TestPage() {

}

public static void main(String[] args) {

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

String strUrl = "jdbc:oracle:thin:@localhost:1521:xmgl";

String username = "xmgl";

String password = "xmgl";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, username, password);

System.out.println("ok");

CallableStatement cs = null;

//创建CallableStatement

cs = conn.prepareCall("{call page.fenye(?,?,?,?,?,?)}");

cs.setString(1, "mytest");//表名

cs.setInt(2, 3);//显示几条记录

cs.setInt(3, 1);//第几页

//注册总记录数,声明输出参数是什么类型的

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

//注册总页数

cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

//注册返回的结果集

cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

//取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的

int rowNum=cs.getInt(4);//获得输出参数

int pageCount = cs.getInt(5);

rs=(ResultSet)cs.getObject(6);

System.out.println("rowNum="+rowNum);

System.out.println("总页数="+pageCount);

while (rs.next()) {

System.out.println("编号："+rs.getInt(1)+" 名字："+rs.getString(2)+" 密码："+rs.getString(3));

}

} catch (SQLException ex2) {

ex2.printStackTrace();

} catch (Exception ex2) {

ex2.printStackTrace();

} finally {

try {

if (rs != null) {

rs.close();

if (stmt != null) {

stmt.close();

}

if (conn != null) {

conn.close();

}

}

} catch (SQLException ex1) {

}

}

}

}