Spring Batch ResultSet got closed by other before all data being fetched

Question

I am trying to setup the DB2 source as the persistence for the Batch meta data. I am getting this stacktrace:

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from rhall.BATCH_JOB_EXECUTION where JOB_INSTANCE_ID = ? order by JOB_EXECUTION_ID desc]; SQL state [null]; error code [-4470]; [jcc][t4][10120][10898][3.57.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null; nested exception is com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.57.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
   at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:777)
   at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao.findJobExecutions(JdbcJobExecutionDao.java:131)
   at org.springframework.batch.core.repository.support.SimpleJobRepository.getStepExecutionCount(SimpleJobRepository.java:253)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
   at java.lang.reflect.Method.invoke(Method.java:611)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
   at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
   at $Proxy32.getStepExecutionCount(Unknown Source)
   at org.springframework.batch.core.job.flow.JobFlowExecutor.isStepRestart(JobFlowExecutor.java:82)
   at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:63)
   at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67)
   at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) ... 22 more
Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.57.82] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
   at com.ibm.db2.jcc.am.bd.a(bd.java:660)
   at com.ibm.db2.jcc.am.bd.a(bd.java:60)
   at com.ibm.db2.jcc.am.bd.a(bd.java:103)
   at com.ibm.db2.jcc.am.zl.Db(zl.java:4219)
   at com.ibm.db2.jcc.am.zl.q(zl.java:4180)
   at com.ibm.db2.jcc.am.zl.c(zl.java:1009)
   at com.ibm.db2.jcc.am.zl.getTimestamp(zl.java:985)
   at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getTimestamp(WSJdbcResultSet.java:2607)
   at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao$JobExecutionRowMapper.mapRow(JdbcJobExecutionDao.java:425)
   at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao$JobExecutionRowMapper.mapRow(JdbcJobExecutionDao.java:396)
   at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
   at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
   at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:693)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) 
  ... 45 more 

I trace the code, and found the problem around this method: JdbcJobExecutionDao.mapRow(ResultSet rs, int rowNum)

(I am using Spring-batch version 3.0.6) List paste the method here for your convenience,

public JobExecution mapRow(ResultSet rs, int rowNum)
 throws SQLException {          
 Long id = rs.getLong(1);           
 String jobConfigurationLocation = rs.getString(10);            
 JobExecution jobExecution;             
 if (jobParameters == null) {
    jobParameters = getJobParameters(id);           
 }
 if (jobInstance == null) {
    jobExecution = new JobExecution(id, jobParameters, jobConfigurationLocation);           
 } else {
    jobExecution = new JobExecution(jobInstance, id, jobParameters, jobConfigurationLocation);          
 }
 jobExecution.setStartTime(rs.getTimestamp(2));
 jobExecution.setEndTime(rs.getTimestamp(3));
 jobExecution.setStatus(BatchStatus.valueOf(rs.getString(4)));
 jobExecution.setExitStatus(new ExitStatus(rs.getString(5), rs.getString(6)));             
 jobExecution.setCreateTime(rs.getTimestamp(7));
 jobExecution.setLastUpdated(rs.getTimestamp(8));
 jobExecution.setVersion(rs.getInt(9));          
 return jobExecution;        
}

As I trace it, I notice that the problem is in the getJobParameters(id) method. This method performs another query to the JOB_EXECUTION_PARAMS table for paramaters for the given job id. But within this method, the getConnection method returns the same connection as in the current context. After the query, the finally block closes the resultSet. So when the control gets back to the mapRow method, it failed at this line:

jobExecution.setStartTime(rs.getTimestamp(2));  

It is because the rs has already been closed by the getJobParameters(id) method.

Wondering if I did wrong? Please point me out. Many thanks.

Answer 1

removing @Transactional from my method that uses the Batch Infrastructure classes, ie, JobExplorer, solves this issues around the closed ResultSet.