CODEWITHSUNDEEP
spring-batch
Vicky
Vicky

Reputation: 17425

Running a database item reader for a list of parameters

I have a spring batch database item reader as below:

<beans:bean id="MyItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
         <beans:property name="dataSource" ref="MyDataSource" />
         <beans:property name="sql" value="SELECT MY_DATE, COL1, COL2 FROM MYSCHEMA.MYTABLE WHERE MY_DATE = ?" />
         <beans:property name="rowMapper">
             <beans:bean name="mapper" class="com.mypackage.MyRowMapper" />
         </beans:property>
</beans:bean>

As clear, the reader needs a parameter, which I can provide using a preparedStatementSetter.

However, the problem is, I have a list of parameters i.e. ten dates.

I want to run my reader for all the ten dates one by one for 10 times and then run the writer just once to write all the read data for all the dates.

I CAN NOT CHANGE BY QUERY'S WHERE CONDITION TO INCLUDE "IN" CLAUSE. My requirement is to pass the dates one by one only.

I can make available the 10 dates to the reader in any form required using a promotion listener. For e.g. a map of 10 dates with key value pairs or a list of ten dates, etc.

How can I achieve this ?

If someone can point me out to a custom database item reader example, that would be great.

Thanks for reading!

Upvotes: 1

Views: 4504

Answers (2)

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18413

You can't use a strategy like MultiResourceItemReader paired with a custom PreparedStatementSetter with late-binding for your parameter because 'MyItemReader' is evaluated once for step.
My advice is to create a custom ItemReader holds List<Date> and loop through this list until it is exhausted; reading is performed using delegation and ? substitution is done manually.

public class DateListPreparedStatementSetter implements PreparedStatementSetter {
    private Date date;
    public void setDate(Date date) {
        this.date = date;
    }
    public void setValues(PreparedStatement ps) throws SQLException {
        ps.setDate(0,  date);
    }
}

public class DateListItemReader<T> extends AbstractItemStreamItemReader<T>
{
    List<Date> dateList;
    int listIndex = -1;
    DateListPreparedStatementSetter pss;
    ItemReader<T> delegate;

    public void setDateList(List<Date> dateList) {
        this.dateList = dateList;
    }

    public void setDelegate(ItemReader<T> delegate) {
        this.delegate = delegate;
    }

    public void setPss(DateListPreparedStatementSetter pss) {
        this.pss = pss;
    }

    public T read() throws Exception, UnexpectedInputException, ParseException, NonTransientResourceException {
        T next = delegate.read();
        //  No more elements...
        if(null == next)
        {
            //  Close delegate
            ++listIndex;
            //  ...but more dates!
            if(listIndex < dateList.size())
            {
                pss.setDate(dateList.get(listIndex));
                //  Open delegate
                //  Recursive read
                next = read();
            }
        }
        return next;
    }

    @Override
    public void open(ExecutionContext executionContext) {
        //  Restore last index
        listIndex = executionContext.getInt("dateListIndex",-1);
        ((ItemStream)delegate).open(executionContext);
        super.open(executionContext);
    }

    @Override
    public void update(ExecutionContext executionContext) {
        //  Save last index
        executionContext.putInt("dateListIndex", listIndex);
        ((ItemStream)delegate).update(executionContext);
        super.update(executionContext);
    }

    @Override
    public void close() {
        ((ItemStream)delegate).close();
        super.close();
    }
}

<bean name="myPSS" class="DateListPreparedStatementSetter" />
<bean name="myReader" class="DateListItemReader">
  <property name="dateList">
    <list>
      <value>01/01/2010</value>
      <value>01/01/2011</value>
    </list>
  </property>
</bean>
<bean name="MyItemReader">
  <!-- Other props...  ->
  <property name="preparedStatementSetter" ref="myPSS" />
</bean>

I haven't tested code,but the idea should be clear.

Upvotes: 2

Bob Rivers
Bob Rivers

Reputation: 5495

One way is to develop a composite itemreader. Take a look at these posts. The downside of the composite approach is that you'll need to define (based on your example) 10 itemreaders (one for each date). More dates you have, worse it gets.

I don't know if it's acceptable, but another way is to encapsulate you unchangeable query inside a stored procedure. Into the stored procedure you could do a loop, calling the unchangeable query, passing a single date to it. And in spring batch, you could use StoredProcedureItemReader.

You could also try to extend JdbcCursorItemReader, iterating over the dates in doRead method, but I was looking into the source code and I didn't understood how spring handles the cursor opening and closing... So I'm afraid that it's not as simple as it sounds...

Upvotes: 1

Related Questions