Java调用Oracle的分页存储过程

水深无声 2022-01-16 00:51 192阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

1. 使用PL/SQL写分页存储过程  
[http://my.oschina.net/belinwu/blog/112817][http_my.oschina.net_belinwu_blog_112817]  
2. 使用Java程序调用该存储过程

package com.wujilin.procedure;
    
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    
    public class ProcedureTest {
    	public static void main(String[] args) {
    		String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    		String username = "scott";
    		String password = "tiger";
    		String driver = "oracle.jdbc.driver.OracleDriver";
    		
    		try {
    			Class.forName(driver);
    			Connection conn = DriverManager.getConnection(url, username, password);
    			String sql = "{call pro(?,?,?,?,?,?)}";
    			CallableStatement cstmt = conn.prepareCall(sql);
    			// 设置表名
    			cstmt.setString(1, "emp");
    			// 设置每一页的记录数
    			cstmt.setInt(2, 4);
    			// 设置当前页
    			cstmt.setInt(3, 2);
    			// 注册总共的页数
    			cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
    			// 注册总共的记录数
    			cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
    			// 注册结果集
    			cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
    			// 直接执行：execute()方法即可。
    			cstmt.execute();
    			// 将游标强制转换成ResultSet类型。
    			ResultSet rs = (ResultSet) cstmt.getObject(6);
    			int pageCount = cstmt.getInt(4);
    			System.out.println("页数：" + pageCount);
    			int rowCount = cstmt.getInt(5);
    			System.out.println("记录数：" + rowCount);
    			ResultSetMetaData rsmd = rs.getMetaData();
    			while (rs.next()) {
    				for (int i = 0; i < rsmd.getColumnCount(); i++) {
    					System.out.print(rsmd.getColumnLabel(i + 1) + ":" + rs.getObject(i + 1) + ", ");
    				}
    				System.out.println();
    			}
    		} catch (Exception e) {
    			
    		}
    	}
    }

3. 运行结果  
页数：4  
记录数：14  
EMPNO:7654, ENAME:MARTIN, JOB:SALESMAN, MGR:7698, HIREDATE:1981-09-28, SAL:1250, COMM:1400, DEPTNO:30, RN:5,  
EMPNO:7698, ENAME:BLAKE, JOB:MANAGER, MGR:7839, HIREDATE:1981-05-01, SAL:2850, COMM:null, DEPTNO:30, RN:6,  
EMPNO:7782, ENAME:CLARK, JOB:MANAGER, MGR:7839, HIREDATE:1981-06-09, SAL:26950, COMM:null, DEPTNO:10, RN:7,  
EMPNO:7788, ENAME:SCOTT, JOB:ANALYST, MGR:7566, HIREDATE:1987-04-19, SAL:3000, COMM:null, DEPTNO:20, RN:8,

转载于:https://my.oschina.net/belinwu/blog/112731

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220115/137f4af12a904ceda5ae550ea07c68f3.png
[http_my.oschina.net_belinwu_blog_112817]: http://my.oschina.net/belinwu/blog/112817