org.springframework.batch.item.database
Class JdbcCursorItemReader<T>

java.lang.Object
  org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader<T>
      org.springframework.batch.item.database.JdbcCursorItemReader<T>

All Implemented Interfaces:

ItemReader<T>, ItemStream, InitializingBean

public class JdbcCursorItemReader<T>
extends AbstractItemCountingItemStreamItemReader<T>
implements InitializingBean

Simple item reader that opens a JDBC cursor and continually retrieves the next row in the ResultSet.

By default the cursor will be opened using a separate connection. The ResultSet for the cursor is held open regardless of commits or roll backs in a surrounding transaction. Clients of this reader are responsible for buffering the items in the case that they need to be re-presented on a rollback. This buffering is handled by the step implementations provided and is only a concern for anyone writing their own step implementations.

The statement used to open the cursor is created with the 'READ_ONLY' option since a non read-only cursor may unnecessarily lock tables or rows. It is also opened with 'TYPE_FORWARD_ONLY' option. By default the cursor will be opened using a separate connection which means that it will not participate in any transactions created as part of the step processing.

There is an option (setUseSharedExtendedConnection(boolean) that will share the connection used for the cursor with the rest of the step processing. If you set this flag to true then you must wrap the DataSource in a ExtendedConnectionDataSourceProxy to prevent the connection from being closed and released after each commit performed as part of the step processing. You must also use a JDBC driver supporting JDBC 3.0 or later since the cursor will be opened with the additional option of 'HOLD_CUSORS_OVER_COMMIT' enabled.

Each call to AbstractItemCountingItemStreamItemReader.read() will call the provided RowMapper, passing in the ResultSet. There is currently no wrapping of the ResultSet to suppress calls to next(). However, if the RowMapper (mistakenly) increments the current row, the next call to read will verify that the current row is at the expected position and throw a DataAccessException if it is not. The reason for such strictness on the ResultSet is due to the need to maintain control for transactions and restartability. This ensures that each call to AbstractItemCountingItemStreamItemReader.read() returns the ResultSet at the correct row, regardless of rollbacks or restarts.

ExecutionContext: The current row is returned as restart data, and when restored from that same data, the cursor is opened and the current row set to the value within the restart data. See setDriverSupportsAbsolute(boolean) for improving restart performance.

Calling close on this ItemStream will cause all resources it is currently using to be freed. (Connection, ResultSet, etc). It is then illegal to call AbstractItemCountingItemStreamItemReader.read() again until it has been re-opened.

Known limitation: when used with Derby setVerifyCursorPosition(boolean) needs to be false because ResultSet.getRow() call used for cursor position verification is not available for 'TYPE_FORWARD_ONLY' result sets.

Author:

Lucas Ward, Peter Zozom, Robert Kasanicky, Thomas Risberg

Field Summary

protected ResultSet	rs
static int	VALUE_NOT_SET

Constructor Summary

JdbcCursorItemReader()

Method Summary

void	afterPropertiesSet() Assert that mandatory properties are set.
protected void	doClose() Close the cursor and database connection.
protected void	doOpen() Execute the setSql(String) query.
protected T	doRead() Read next row and map it to item, verify cursor position if setVerifyCursorPosition(boolean) is true.
protected SQLExceptionTranslator	getExceptionTranslator() Return the exception translator for this instance.
protected void	jumpToItem(int itemIndex) Use ResultSet.absolute(int) if possible, otherwise scroll by calling ResultSet.next().
void	setDataSource(DataSource dataSource) Public setter for the data source for injection purposes.
void	setDriverSupportsAbsolute(boolean driverSupportsAbsolute) Indicate whether the JDBC driver supports setting the absolute row on a ResultSet.
void	setFetchSize(int fetchSize) Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object.
void	setIgnoreWarnings(boolean ignoreWarnings) Set whether SQLWarnings should be ignored (only logged) or exception should be thrown.
void	setMaxRows(int maxRows) Sets the limit for the maximum number of rows that any ResultSet object can contain to the given number.
void	setPreparedStatementSetter(PreparedStatementSetter preparedStatementSetter) Set the PreparedStatementSetter to use if any parameter values that need to be set in the supplied query.
void	setQueryTimeout(int queryTimeout) Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.
void	setRowMapper(RowMapper rowMapper) Set the RowMapper to be used for all calls to read().
void	setSql(String sql) Set the SQL statement to be used when creating the cursor.
void	setUseSharedExtendedConnection(boolean useSharedExtendedConnection) Indicate whether the connection used for the cursor should be used by all other processing thus sharing the same transaction.
void	setVerifyCursorPosition(boolean verifyCursorPosition) Allow verification of cursor position after current row is processed by RowMapper or RowCallbackHandler.

Methods inherited from class org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader

close, getCurrentItemCount, open, read, setCurrentItemCount, setMaxItemCount, setName, setSaveState, update

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

VALUE_NOT_SET

public static final int VALUE_NOT_SET

See Also:

Constant Field Values

rs

protected ResultSet rs

Constructor Detail

JdbcCursorItemReader

public JdbcCursorItemReader()

Method Detail

afterPropertiesSet

public void afterPropertiesSet()
                        throws Exception

Assert that mandatory properties are set.

Specified by:

afterPropertiesSet in interface InitializingBean

Throws:

IllegalArgumentException - if either data source or sql properties not set.

Exception

setDataSource

public void setDataSource(DataSource dataSource)

Public setter for the data source for injection purposes.

Parameters:

dataSource -

getExceptionTranslator

protected SQLExceptionTranslator getExceptionTranslator()

Return the exception translator for this instance. Creates a default SQLErrorCodeSQLExceptionTranslator for the specified DataSource if none is set.

setFetchSize

public void setFetchSize(int fetchSize)

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value.

Parameters:

fetchSize - the number of rows to fetch

See Also:

ResultSet.setFetchSize(int)

setMaxRows

public void setMaxRows(int maxRows)

Sets the limit for the maximum number of rows that any ResultSet object can contain to the given number.

Parameters:

maxRows - the new max rows limit; zero means there is no limit

See Also:

Statement.setMaxRows(int)

setQueryTimeout

public void setQueryTimeout(int queryTimeout)

Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds. If the limit is exceeded, an SQLException is thrown.

Parameters:

queryTimeout - seconds the new query timeout limit in seconds; zero means there is no limit

See Also:

Statement.setQueryTimeout(int)

setIgnoreWarnings

public void setIgnoreWarnings(boolean ignoreWarnings)

Set whether SQLWarnings should be ignored (only logged) or exception should be thrown.

Parameters:

ignoreWarnings - if TRUE, warnings are ignored

setVerifyCursorPosition

public void setVerifyCursorPosition(boolean verifyCursorPosition)

Allow verification of cursor position after current row is processed by RowMapper or RowCallbackHandler. Default value is TRUE.

Parameters:

verifyCursorPosition - if true, cursor position is verified

setRowMapper

public void setRowMapper(RowMapper rowMapper)

Set the RowMapper to be used for all calls to read().

Parameters:

rowMapper -

setSql

public void setSql(String sql)

Set the SQL statement to be used when creating the cursor. This statement should be a complete and valid SQL statement, as it will be run directly without any modification.

Parameters:

sql -

setPreparedStatementSetter

public void setPreparedStatementSetter(PreparedStatementSetter preparedStatementSetter)

Set the PreparedStatementSetter to use if any parameter values that need to be set in the supplied query.

Parameters:

preparedStatementSetter -

setDriverSupportsAbsolute

public void setDriverSupportsAbsolute(boolean driverSupportsAbsolute)

Indicate whether the JDBC driver supports setting the absolute row on a ResultSet. It is recommended that this is set to true for JDBC drivers that supports ResultSet.absolute() as it may improve performance, especially if a step fails while working with a large data set.

Parameters:

driverSupportsAbsolute - false by default

See Also:

ResultSet.absolute(int)

setUseSharedExtendedConnection

public void setUseSharedExtendedConnection(boolean useSharedExtendedConnection)

Indicate whether the connection used for the cursor should be used by all other processing thus sharing the same transaction. If this is set to false, which is the default, then the cursor will be opened using in its connection and will not participate in any transactions started for the rest of the step processing. If you set this flag to true then you must wrap the DataSource in a ExtendedConnectionDataSourceProxy to prevent the connection from being closed and released after each commit. When you set this option to true then the statement used to open the cursor will be created with both 'READ_ONLY' and 'HOLD_CUSORS_OVER_COMMIT' options. This allows holding the cursor open over transaction start and commits performed in the step processing. To use this feature you need a database that supports this and a JDBC driver supporting JDBC 3.0 or later.

Parameters:

useSharedExtendedConnection - false by default

doClose

protected void doClose()
                throws Exception

Close the cursor and database connection.

Specified by:

doClose in class AbstractItemCountingItemStreamItemReader<T>

Throws:

Exception

doOpen

protected void doOpen()
               throws Exception

Execute the setSql(String) query.

Specified by:

doOpen in class AbstractItemCountingItemStreamItemReader<T>

Throws:

Exception

doRead

protected T doRead()
            throws Exception

Read next row and map it to item, verify cursor position if setVerifyCursorPosition(boolean) is true.

Specified by:

doRead in class AbstractItemCountingItemStreamItemReader<T>

Returns:

item

Throws:

Exception

jumpToItem

protected void jumpToItem(int itemIndex)
                   throws Exception

Use ResultSet.absolute(int) if possible, otherwise scroll by calling ResultSet.next().

Overrides:

jumpToItem in class AbstractItemCountingItemStreamItemReader<T>

Throws:

Exception