CODEWITHSUNDEEP
springoracle-databasejdbc
Ravi
Ravi

Reputation: 137

How to close refcursor in spring when using simplejdbccall

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.

Upvotes: 3

Views: 5087

Answers (2)

Victor Marrerp
Victor Marrerp

Reputation: 185

I use CallableStatement directly and I can release statements and connections quickly and safely, try both methods and measure memory consumption, it worked perfectly for me to solve memory consumption and connection retention problems that proved many waiting and rejection of connections the applications.

try {
        log.info("**** RepositoryPSostgres.getAllProducts ******** ");
        Connection conn = jdbcTemplate.getDataSource().getConnection();
        conn.setAutoCommit(false);
        // Procedure call.
        CallableStatement proc = conn.prepareCall("{? = call get_all_products() }");
        proc.registerOutParameter(1, Types.OTHER);
        proc.execute();
        ResultSet results = (ResultSet) proc.getObject(1);
        **proc.close();
        proc.isClosed();
        conn.close();**
        ArrayList <Products> resp = new ArrayList <Products>();

        while (results.next()) {
            Products resp1 = new Products();
            resp1.setId(results.getInt("id"));
            resp1.setName((String) results.getString("name"));
            resp1.setPrice((BigDecimal) results.getBigDecimal("price"));
            resp.add(resp1);
            log.info("***" + results.getInt("id") + "***** ");
            log.info("***" + results.getString("name") + "***** ");
            log.info("***" + results.getBigDecimal("price") + "***** ");
        }
    results.close();
    return resp;
    } catch (Exception e) {
        e.printStackTrace();
        log.error(new StringBuffer("Error en transaccion en saldo CashPooling : ").append(e.getLocalizedMessage()).toString());
        return null;
    }

Upvotes: 0

Artem Bilan
Artem Bilan

Reputation: 121442

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

Upvotes: 1

Related Questions