Spring JdbcCursorItemReader closed resultset

[xsunil kumar]

Hi ,

I am using spring JdbcCursorItemReader to read item and then process it.There are several records present in the database. After processing around 200 records, application is giving me below error.

org.springframework.jdbc.UncategorizedSQLException: Attempt to process next row failed;
uncategorized SQLException for SQL [SELECT * FROM T_TRANSACTIONS WHERE T_PROCESS_STATUS = 'W' ORDER BY T_TRAN_DATE, T_BUY_SELL, T_TRANSACTION_TYPE];
SQL state [null]; error code [17010]; Closed Resultset: next; nested exception is java.sql.SQLException: Closed Resultset: next
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.batch.item.database.JdbcCursorItemReader.doRead(JdbcCursorItemReader.java:427)
at org.springframework.batch.item.support.AbstractBufferedItemReaderItemStream.read(AbstractBufferedItemReaderItemStream.java:92)
at org.springframework.batch.item.support.DelegatingItemReader.read(DelegatingItemReader.java:61)
at org.springframework.batch.core.step.item.BatchListenerFactoryHelper$1.read(BatchListenerFactoryHelper.java:67)
at org.springframework.batch.core.step.item.SimpleItemHandler.doRead(SimpleItemHandler.java:88)
at org.springframework.batch.core.step.item.SkipLimitStepFactoryBean$StatefulRetryItemHandler.read(SkipLimitStepFactoryBean.java:378)
at org.springframework.batch.core.step.item.SimpleItemHandler.handle(SimpleItemHandler.java:66)
at org.springframework.batch.core.step.item.ItemOrientedStep$2.doInIteration(ItemOrientedStep.java:390)
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)
at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
at org.springframework.batch.core.step.item.ItemOrientedStep.processChunk(ItemOrientedStep.java:383)
at org.springframework.batch.core.step.item.ItemOrientedStep$1.doInIteration(ItemOrientedStep.java:251)
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:346)
at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:212)

It seems that resultset opened by spring framework at the begining is closed and it is not processing further. Please advice,

-Sunil

[Jayesh A Lalwani]

Most databases have timeouts and they will close a connection after it has been open for a while. It's probably that the underlying database is closing the connection. You might have to increase the timeout

Having said that... how long is it taking to process 200 records. You shouldn't be keeping conenctions open for long anyways. It might be better if you read a batch of records, close the connection, process the records, and then open it to get the next batch.