关于 "java 如何调用Oracle存储过程中的动态refcursor结果集" 问题

关于 "java 如何调用Oracle存储过程中的动态refcursor结果集" 问题 问题追溯到: 上周末一个同事问我Java中一个PreparedStatement构造的一个动态SQL查询，如何释放资源的问题 因为查询sql是动态的，比如外层是一个循环： for (int i=1;i<=3;i++){ String column ="id"+i; sql= "select "+column+" from test"; PreparedStatement pstm= conn.prepareStatement(sql); ResultSet rs = pstm.executeQuery(); while(rs.next()){ .... .... } .... .... } 一般在我们Java程序中，要及时关闭释放连接资源，不然游标cursor就会爆掉，这个跟Oracle中初始化参数open_cursor有关对于上面的查询，由于查询sql是在变化的，PreparedStatement需要每次创建，然后打开一个游标，如果外层for循环很多，如果资源不及时释放，就会有问题。这就涉及到资源PreparedStatement何时关闭的问题。 在我们erp系统平台中，ubc01监控平台可以看到当前系统有哪些cursor正在执行，之前我也有测试过，通过系统组得知，ubc01中的信息来自v$open_cursor，其实是不准确的。 言归正传，回到上面的问题，资源何时关闭？ 由于创建PreparedStatement的sql是动态组出来的，因此不能在创建PreparedStatement的时候先去判断是否为null,这根我们平常写法不同，也就是说不能 PreparedStatement pstm =null; if(pstm == null) pstm = conn.prepareStatement(sql); .... .... 这样虽然只会开启一个cursor，执行的查询SQL不管怎么变，pstm只创建一次，并不是我的实际需求。 针对这个问题，有如下几种方法可以解决： 第一种：一开一闭 也就是说，在循环里面，每次重新创建一个PreparedStatement开启一个cursor，执行完之后马上关闭PreparedStatement 即： for (int i=1;i<=3;i++){ String column ="id"+i; sql= "select "+column+" from test"; PreparedStatement pstm= conn.prepareStatement(sql); ResultSet rs = pstm.executeQuery(); while(rs.next()){ .... .... } rs.close(); rs=null; pstm.close(); pstm=null; } 第二种：只创建一个PreparedStatement开启一个cursor 换个思路，这里将查询的动态sql给写死，即查询所有，这样只需要创建一次PrepareStatement，只是关键在于获取结果集 的时候，执行锁定栏位，即： PreparedStatement pstm=null; ResultSet rs =null; for (int i=1;i<=3;i++){ String column ="id"+i; sql= "select * from test"; if(pstm==null) pstm = conn.prepareStatement(sql); rs = pstm.executeQuery(); while(rs.next()){ String name = rs.getString(column); --关键在这！ .... } .... .... } rs.close(); rs=null; pstm.close(); pstm=null; 这样资源就可以只创建一次，然后在循环外面关闭就可以了，符合我们一般的思路。 第三种：存储过程返回动态结果集 回到文章的标题，也是我真正想记录的东西。 当我们在一个存储过程中返回一个动态结果集的时候，Java端如何调用获取呢？这是我最想关心的 因为我喜欢把很多逻辑写到Oracle端得procedures中，想得到什么结果，直接在procedures中通过refcursor参数返回即可。 针对上面的资源释放问题，我同样可以换个思路，将动态获取的查询放到Oracle的procedure中，Java端只需调用一次我的procedure这样资源的开启释放都和我们一般写法一样的 首先写我想要的procedure: create or replace procedure p (cur out sys_refcursor,colname varchar2) is sql_str varchar2(3000); begin sql_str:='select '||colname||' from test'; open cur for sql_str; end; 然后在Java端调用这个过程： CallableStatement cs =null; ResultSet rs =null; for (int i=1;i<=3;i++){ String column ="id"+i; if(cs==null) cs=conn.prepareCall("{call p(?,?)}"); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.setString(2,column); cs.execute(); rs=(ResultSet)cs.getObject(1); while(rs.next()){ System.out.println(rs.getString(1)); System.out.println(rs.getString(column)); } .... .... } rs.close(); rs=null; cs.close(); cs=null; 综上所述，对于上面三种方法，最好的是第二种，因为从始至终只会开启一个cursor！ 第一种虽然还是节省资源，但每次都会开启然后又关闭又重新开启，比较麻烦。 最差的是第三种！ 为什么这么说呢?我通过调试发现，Java端prepareCall第一次会开启一个plsql块的cursor ：begin p(?,?); end; 然后每次cs.execute()得时候，在过程中会每次开启一个cursor: select id1/id2/id3 from test; 也就是说，每次cs.execute()执行完后，未及时将open cursor关闭！ 通过程序可以看到，procedure中开启了动态refcursor：open cur for .... 这样该游标是开启了，然而在Java端接收的游标 OracleTypes.CURSOR并没有在循环中每次调用procedure完就关闭因此cursor数在一次次增加。 看上去我们在Java端将CallableStatement和ResultSet关闭了，但实质上procedure开启的cursor我们未关闭！ 现修改第三种方法： 我们采用JDBC提供的Java和Oracle连接的另外一个接口OracleCallableStatement来创建Statement调用Oracle的procedure然后直接获取OracleType.CURSOR这样我们就可以在循环中colse cursor了 具体这样做： OracleCallableStatement ocs = null; for (int i=1;i<=3;i++){ String column ="id"+i; if(ocs==null) ocs=(OracleCallableStatement) m_Conn.prepareCall("{call p(?,?)}"); ocs.registerOutParameter(1, OracleTypes.CURSOR); ocs.setString(2,column); ocs.execute(); ResultSet cur = ocs.getCursor(1); while(cur.next()){ System.out.println(column+"="+cur.getString(1)); } cur.close(); cur=null; .... .... } ocs.close(); ocs=null; 这样，程序中最多只会开启两个cursor： (1).begin p(?,?); end; 最后关闭 和 (2).select id1/id2/id3 from test; 这个每次开了每次都关闭了 其实，第三种方法直接在循环里关闭rs就够了，即： if(cs==null) cs=m_Conn.prepareCall("{call p(?,?)}"); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cs.setString(2,column); cs.execute(); rs=(ResultSet)cs.getObject(1); while(rs.next()){ System.out.println(column+"="+rs.getString(1)); } rs.close(); rs=null; 问题到此已结束！ 最后附上全部Java代码： package com.fpg.p.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; public class Test2 { public static void main(String[] args) { Connection m_Conn = null; PreparedStatement pstm=null; ResultSet rs = null; CallableStatement cs =null; OracleCallableStatement ocs = null; try { //1.构造驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.创建连接 m_Conn = DriverManager.getConnection( "jdbc:oracle:thin:@***.***.***.***:1521:****", "sys as sysdba", "****"); String column=null; String sql = null; for(int i=1;i<=3;i++){ column="id"+i; // 1 // sql = "select "+column+" from test"; // pstm = m_Conn.prepareStatement(sql); // rs = pstm.executeQuery(); // while(rs.next()){ // System.out.println(rs.getString(column)); // } // rs.close(); // rs=null; // pstm.close(); // pstm=null; //2 // sql = "select * from test"; // if (pstm==null) // pstm = m_Conn.prepareStatement(sql); // rs = pstm.executeQuery(); // while(rs.next()){ // System.out.println(rs.getString(column)); // } // 3 // if(cs==null) // cs=m_Conn.prepareCall("{call p(?,?)}"); // cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); // cs.setString(2,column); // cs.execute(); // rs=(ResultSet)cs.getObject(1); // while(rs.next()){ // System.out.println(column+"="+rs.getString(1)); // } // rs.close(); // rs=null; // 4 if(ocs==null) ocs=(OracleCallableStatement) m_Conn.prepareCall("{call p(?,?)}"); ocs.registerOutParameter(1, OracleTypes.CURSOR); ocs.setString(2,column); ocs.execute(); ResultSet cur = ocs.getCursor(1); while (cur.next()){ System.out.println(column+"="+cur.getString(1)); } cur.close(); cur=null; } } catch (Exception e) { e.printStackTrace(); }finally{ try { if(pstm!=null){ pstm.close(); pstm=null; } if(rs!=null){ rs.close(); rs=null; } if(cs!=null){ cs.close(); cs=null; } if(ocs!=null){ ocs.close(); ocs=null; } if(m_Conn!=null){ m_Conn.close(); m_Conn=null; } } catch (Exception e1) { // e1.printStackTrace(); } } } }