How to close refcursor in spring when using simplejdbccall

Question

I am using spring simpleJdbcCall to call oracle stored procedure and i am using oracle 11g .

I stumbled on a couple of posts which suggests there might be memory leak as the ref cursors are not properly closed by spring.

Is there anyway to explicitly close cursor while using spring simplejdbccall? or is increasing the oracle OPEN_CURSOR the only way out?.

I am planning to scale up my application to handle around one million transactions every hour .Any suggestions will be helpful.

Answer 1

Actually there is no such an issue with Spring JDBC. It closes all resources within finally after all execute. SimpleJdbcCall uses JdbcTemplate:

public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action)
            throws DataAccessException {
     try {
         ...
     }
     catch (SQLException ex) {
         ...
     }
     finally {
        if (csc instanceof ParameterDisposer) {
          ((ParameterDisposer) csc).cleanupParameters();
        }
        JdbcUtils.closeStatement(cs);
        DataSourceUtils.releaseConnection(con, getDataSource());
     }

}

The same for ResultSet OUT parameters:

protected Map<String, Object> processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) throws SQLException {
   ....
   finally {
       JdbcUtils.closeResultSet(rs);
   }
   return returnedResults;
}

From other side I have a big experience with Spring JDBC and Oracle in high-loaded systems and want to say that we noticed enough open resources on Oracle with at peak loads, but they have been released properly after that.

Although we used JBOSS Pooled DataSource and its TransactionMaanger