updatetable JdbcCursorItemReader

[docstrange]

Hello,

we are converting programs with a complex business logic, we didn't want to change. In this programs updateable cursors are used for tables with no primary key.

In JDBC 2.0 API there is a posibility to update, delete and insert rows in a Resultset.

Can this be done with Classes from Spring Batch too, or do I have to write my own extension of the JdbcCursorItemReader?

I'm looking for something like this:

// open connection, prepare Reader ...
itemReader.open(executionContext);
ResultBean resultBean = null;
while ((resultBean = itemReader.read()) != null)
{
if(condition)
{
itemReader.deleteRow();
// or
resultBean.setField1(newValue);
itemReader.updateRow(resultBean);
}
}
//cleanup ...

[Dave Syer]

The ItemReader is not the right place to make updates. I would probably use a regular Jdbc*ItemReader to generate primary keys and then use the updateable cursor code in an ItemProcessor.

[docstrange]

The ItemReader is not the right place to make updates. I would probably use a regular Jdbc*ItemReader to generate primary keys and then use the updateable cursor code in an ItemProcessor.

Like I mentioned in my post, there are tables with NO primary keys (ugly I know, but existing and won't change in near future). Imagine you want to eliminate duplicate rows. With a key existing of ALL columns, you would destroy all equal columns, but you want only to delete all but the first column.

e.g.
before
Name Prename ExPresident
Reagan Ronald 1
Reagan Ronald 1
Reagan Ronald 1
Denver John 0
Denver John 0

after
Name Prename ExPresident
Reagan Ronald 1
Denver John 0

The reader, processor, writer steps won't work in this case.-(
For 'normal' batchprocessing this approach looks elegant, but in our case we need something like a updateable resultset.

[Dave Syer]

Yes that is ugly - and hard to believe that it doesn't cause other major problems, but that's another discussion I guess with your DBA. Maybe you can make the "item" the resultset and pass it down to the processor for updates. Transactions are going to be a challenge, unless you can process the whole lot in one chunk (in which case reader/processor/writer is sort of irrelevant and you might as well just write a tasklet that manipulates the cursor directly like in your first post).

[ahh1479]

I have a very similar process where I have to scan through a number of records at a time, compare them, decide which one of those should be used and produce an output with that record. Can you elaborate on how to write a tasklet to do this? The size of the data I have to read is large (millions of rows). I would like to use the existing partition and JdbcPagingItemReader functionalities provided. Can I inject the reader into a tasklet?

Thanks.