关于 "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)issql_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;// 4if(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();}}}}