How to implement reader for multiple queries but same output item?

Question

For a Spring batch job, we have 2 different queries on the same table. The requirement is to have a reader that execute two queries to read data from the same table.

One way could be :

<batch:step id="firstStep" next="secondStep">
       <batch:tasklet>
          <batch:chunk reader="firstReader" writer="firstWriter" commit-        interval="2">
          </batch:chunk>
       </batch:tasklet>
    </batch:step>
    <batch:step id="secondStep" next="thirdStep">
       <batch:tasklet>
          <batch:chunk reader="secondReader" writer="secondWriter"
           commit-interval="2">
          </batch:chunk>
       </batch:tasklet>
    </batch:step>

But this demands totally another step to be defined which is a copy of the first. Is there any other way to achieve the same ? I am looking for something like MultiResourceItemReader for DB based readers that aggregates the data together.

Solution

You can create one view in database for different queries and call it as you call in a JdbcPagingItemReader .If thats not an option then there are different ways , but one way i have worked is as given below.Spring has other option as well, but as per developer stand point following is definitely an option.

Create two item reader ...first one is below

<!--use org.springframework.batch.item.database.JdbcCursorItemReader for  simple queries-->
<bean id="itemReader1"
    class="org.springframework.batch.item.database.JdbcPagingItemReader"
 <property name="sql"
    value=" FROM   table1" />
    .......
    <property name="rowMapper">
        <bean class="com.sjena.AccountApplicationMapper" />
    </property>
</bean>

then another reader from table 2

<bean id="itemReader2"
    class="org.springframework.batch.item.database.JdbcCursorItemReader"
<property name="sql"
    value="FROM   table2" />
    .......
    <property name="rowMapper">
        <bean class="com.sjena.AccountApplicationMapper" />
    </property>
</bean>

then delegate to your custom reader

<bean id="customItemReader" class="com.sjena.spring.reader.MyCustomReader"
    scope="step">
    <property name="itemReader1" ref="itemReader1" />
    <property name="itemReader2" ref="itemReader2" />
    <property name="pageSize" value="5" />

</bean>

And eventually use this custom reader

<job id="testJob" xmlns="http://www.springframework.org/schema/batch">
    <step id="step1">
        <tasklet>
            <chunk reader="itemReader" writer="itemWriter"
                commit-interval="1" />
        </tasklet>
    </step>
</job>

Then your class is as given below

public class MyCustomReader implements ItemReader<AccountApplicationSummary> {

int pagesize;// you may have diff pagesize for diff item readers
ItemReader<AccountApplication>  itemReader1;
ItemReader<AccountApplication>  itemReader2;


@Override
public AccountApplicationSummary read()
        throws Exception, UnexpectedInputException, ParseException, NonTransientResourceException {

    // itemReader1.setPageSize(pageSize),Be sure, itemReader is   JdbcPagingItemReader type and better to do these initiatlization in a init method (implement InitializingBean and use afterpropertyset to set them..).. 
    //Like pageSize, you can set anyproperty that you may need

    AccountApplication application1 = itemReader1.read();
    AccountApplication application2 = itemReader2.read();
    //And you have results from both tables and now you can play with it 

    AccountApplicationSummary summary = new AccountApplicationSummary();

    return summary;
}

}

Comments

[skpraveen]

thanks ! Java based config seems to be very flexible. Even in that the approach seems to be to create multiple steps & execute the flow in parallel. So like MultiResourceItemReader, can't we just consolidate the reader alone ( for multiple queries) ?

[Sander_M]

I have looked into this myself, but found it to cumbersome (to much outside the box coding, customization). It would require you to create a custom JdbcCursorItemReader, say MultiJdbcCursorItemReader that will delegate the actual reading of the database to two individual JdbcCursorItemReaders. You can then provide each reader with a query. However, you will still have to configure two reader implementations with exactly the same settings...

[Sander_M]

This is exactly the option I was describing (without code tho), great example surya. The only issue I would have with this solution is a use case where you will have many different queries to be executed. In a large application that needs to be able to scale to that easily, adding readers for every different query will be cumbersome.

[skpraveen]

Thanks Surya !. Here in this example, data from both the readers are read at same time. However, how do we merge the data from itemReaders in each Read-Process iteration,. i,e, once the data is read from Reader1, the data has to be read from Reader2. Also, will it work with pagination in place ?

[surya]

@Sander_M agreed. This one is a simplest one, anyone would understand , i believe. But in a large application , may be a view can be created for multiple sql queries or if queries produce different result, spring has different options, though i have to test if there are separate database calls ??

[surya]

@user3673059 - yes you can set pagesize, its just a property you can set in your reader...here instead of JdbcCursorItemReader, use JdbcPagingItemReader for reading your actuatl queries (itemReader1 and itemReader2)...and with little bit customization you can set paging size as well (i have edited my answer to give you those hints)..And please note -if both queries give same result, if you can create a view in database and instead of all what i mentioned, using simple JdbcPagingItemReader with new view would be a good solution.