如何在Spring Batch的JdbcCursorItemReader中设置游标为TYPE_SCROLL_SENSITIVE实现回退？

Great question! When you need to scroll backward in a ResultSet with JdbcCursorItemReader, you have a couple of straightforward ways to adjust the ResultSet type from the default TYPE_FORWARD_ONLY to TYPE_SCROLL_SENSITIVE. Let's walk through the options, including adapting your existing XML configuration.

Option 1: Directly Configure ResultSet Type in XML #

The JdbcCursorItemReader actually exposes properties to set the ResultSet type and concurrency directly. You can add these properties to your bean definition using the integer constants corresponding to JDBC ResultSet types:

• TYPE_SCROLL_SENSITIVE = 1004
• CONCUR_READ_ONLY (the default concurrency, but good to explicitly set for clarity) = 1007

Here's how to update your existing XML configuration:

<bean id="databaseItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
    <property name="dataSource" ref="dataSource" />
    <property name="sql" value="select * from document udd, field uff where uff.docid = udd.docid AND uff.field_name IN ('address','contractNb','city','locale','login','mobile','name','phone') ORDER BY udd.docid, uff.field_name ASC" />
    <property name="rowMapper">
        <bean class="com.migration.springbatch.UDocumentResultRowMapper" />
    </property>
    <property name="verifyCursorPosition" value="false"/>
    <!-- Add these two properties to enable scrollable ResultSet -->
    <property name="resultSetType" value="1004"/>
    <property name="resultSetConcurrency" value="1007"/>
</bean>

Important note: You already have verifyCursorPosition="false" set, which is critical here. The default verification checks that the cursor is at the first row, which will fail if you've scrolled backward, so keeping this disabled is necessary.

Option 2: Extend JdbcCursorItemReader for Type Safety #

If you prefer to avoid magic integer constants in your XML, you can create a custom subclass of JdbcCursorItemReader that sets the ResultSet type programmatically:

package com.migration.springbatch;

import org.springframework.batch.item.database.JdbcCursorItemReader;
import java.sql.ResultSet;

public class ScrollableJdbcCursorItemReader<T> extends JdbcCursorItemReader<T> {
    public ScrollableJdbcCursorItemReader() {
        super();
        setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
        setResultSetConcurrency(ResultSet.CONCUR_READ_ONLY);
    }
}

Then update your XML to use this custom class instead:

<bean id="databaseItemReader" class="com.migration.springbatch.ScrollableJdbcCursorItemReader">
    <property name="dataSource" ref="dataSource" />
    <property name="sql" value="select * from document udd, field uff where uff.docid = udd.docid AND uff.field_name IN ('address','contractNb','city','locale','login','mobile','name','phone') ORDER BY udd.docid, uff.field_name ASC" />
    <property name="rowMapper">
        <bean class="com.migration.springbatch.UDocumentResultRowMapper" />
    </property>
    <property name="verifyCursorPosition" value="false"/>
</bean>

Alternative Approach: Avoid Cursor Scrolling Altogether #

Before diving into scrollable ResultSets, consider if your use case can be adjusted to avoid needing to backtrack:

• Cache Previous Row: If you only need to access the prior row during processing, cache the last processed item in your ItemProcessor or ItemWriter instead of manipulating the cursor. This is often more efficient and avoids potential JDBC driver compatibility issues with scrollable ResultSets.
• Retry Mechanism: If you need to reprocess the current row after a failure, use Spring Batch's built-in retry capabilities (via RetryTemplate or annotation-based retries) instead of rolling back the cursor.

Key Considerations #

• Driver Compatibility: Not all JDBC drivers support TYPE_SCROLL_SENSITIVE equally. Test your configuration with your specific database driver to ensure scroll functionality works as expected.
• Performance: Scrollable ResultSets can have performance overhead compared to forward-only cursors, especially with large datasets. Make sure this approach is necessary for your business logic before implementing it.

内容的提问来源于stack exchange，提问作者Ravish