Announcement Announcement Module
Collapse
No announcement yet.
updatetable JdbcCursorItemReader Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • updatetable JdbcCursorItemReader

    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 ...

  • #2
    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.

    Comment


    • #3
      Originally posted by Dave Syer View Post
      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.

      Comment


      • #4
        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).

        Comment


        • #5
          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.

          Comment

          Working...
          X