Closing Oracle cursors from JDBC
We have a class DB for connecting to an Oracle database with the following method:
public ResultSet execQuery(String sqlQryStr)throws SQLException{
Statement stmt =null ;
ResultSet rs =null ;
try{
stmt = theConn.createStatement ();
rs = stmt.executeQuery(sqlQryStr);
}catch (Exception e){
thrownew SQLException ( e.toString() ) ;
}
return rs ;
}
Hey I didn抰 write this, be nice.. As you can imagine this leave open cursors, consuming 30 an hour. Quick fix was perform a getDBConnection() and create the Statement and ResultSet local and run the query from there, cleaning up in thefinally
. So much for encapsulation; but for a quick fix it is doing the job.
I thought maybe declaring a Global Statement and ResultSet in the DB.java. But then I抦 limited to 1 query at a time, no good.
Since I have returned the ResultSet I thought maybe in the localised code,
try{
rs.close();
}catch (SQLException e){
logger.warn("Error closing ResultSet: " + e.toString()) ;
}
try{
rs.getStatement().close();
}catch (SQLException e){
logger.warn("Error closing Statement: " + e.toString()) ;
}
This also seems a bit messy? But maybe it is the correct way of achieving this.
What is considered a neat Object Oriented approach to this problem?
Thanks in advance for any replies.
