CODEWITHSUNDEEP
javaspringspring-bootspring-data-jpaspring-data
Saimuga
Saimuga

Reputation: 415

Spring JDBC template - java.sql.SQLRecoverableException: Closed Statement: next

I am trying to use JDBCTemplate to execute a select query and use the result set to set object values. But when I call jobResults.next(), I get an error saying connection is closed. But I am not closing any connections because Spring manages that. Can you please help me on this.

private JdbcTemplate jdbcTemplate;

public CfPersistenceUtil(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}


public  Vector getItems(CvPrintJob job, CfBatchPrintConfiguration config, String groupId) throws Exception {    

    ResultSet jobResults = jdbcTemplate.execute(JOB_QUERY_GROUPID, new PreparedStatementCallback<ResultSet>() {  
         @Override
         public ResultSet doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {  
             ps.setString(1,job.getPaperColor());
             ps.setString(2,job.getPaperSize());
             ps.setString(3,job.getDeliveryMethod());
             ps.setInt(4,job.getPlex());
             ps.setString(5,groupId);                                                                         
             return ps.executeQuery();  
         }  
     });  

     while(jobResults.next()) {
         CvPrintJobItem item = new CvPrintJobItem();
         item.setAuthorID((jobResults.getBigDecimal("AUTHOR_ID")).toString());
 
     }

}

StackTrace below

2020-06-23 20:37:37.060  WARN 26800 --- [           main] com.zaxxer.hikari.pool.ProxyConnection   : HikariPool-1 - Connection oracle.jdbc.driver.T4CConnection@21ae6e73 marked as broken because of SQLSTATE(08003), ErrorCode(17009)
java.sql.SQLRecoverableException: Closed Statement: next
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:247) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
    at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java) [HikariCP-3.4.2.jar:na]
    at com.acn.abp.printbatch.util.CfPersistenceUtil.getItems(CfPersistenceUtil.java:76) [classes/:na]
    at com.acn.abp.printbatch.util.CfBatchPrintUtil.getJobs(CfBatchPrintUtil.java:222) [classes/:na]
    at com.acn.abp.printbatch.tasklet.ProcessSimplex.execute(ProcessSimplex.java:76) [classes/:na]
java.sql.SQLRecoverableException: Closed Statement: next
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:247)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
    at com.acn.abp.printbatch.util.CfPersistenceUtil.getItems(CfPersistenceUtil.java:76)
    at com.acn.abp.printbatch.util.CfBatchPrintUtil.getJobs(CfBatchPrintUtil.java:222)

Upvotes: 0

Views: 951

Answers (1)

Deadron
Deadron

Reputation: 5279

You can't return a ResultSet from doInPreparedStatement. You have to return a domain object. You can either move your while next loop into the callback or use one of the other more convenient methods such as queryForObject

Upvotes: 2

Related Questions