Multiple SELECT SQLs for Spring Batch Reader - reader chaining

Question

I have a requirement to execute eleven SQL SELECT queries sequentially in Spring Batch reader. These SELECT SQLs do INNER JOIN on quite big tables and diff from each other by a single column name in WHERE clause.

Output object type by all these readers would be same, lets say VO.

So how do I achieve that?

I can pass where clause String in reader which would further set in query provider.

@Bean
public ItemReader<VO> reader(String whereClause, @Value("#{stepExecutionContext[partitionNumber]}") String partitionNumber){

}

I am not sure as how to construct Spring Batch step which sets up these eleven SQLs in readers sequentially and executes them sequentially too. There would be single processor and single writer since output type of all readers is same.

Source of all these readers would be same DB tables and I would like to use JdbcPagingItemReader due to paging functionality.

My current reader is part of a partitioned step where String partitionNumber is the partitioning criteria.

What I meant to ask is , can I chain readers if their output type is common? I don't have a problem if these readers get kicked sequentially but can I define a step consisting of a chain of readers for a single processor and writer?

Please suggest for solution or better strategies.

Possible duplicate of Spring batch Job read from multiple database — Luca Basso Ricci
That is not a duplicate of my question even though I get the point that I might have to write my own reader.I want reader to not execute a single select but a list of selects. My question is mainly to ask if I can fit it into any of spring batch readers, preferably JdbcPagingItemReader. — Sabir Khan
This looks a duplicate question but I don't see any accepted answer. — Sabir Khan
You may use a UNION to create a single big query and use just a single reader. — Luca Basso Ricci
Query Providers for readers require select, from and where clauses separately. Unionized query will not have separate where clauses even though my SELECT and FROM remains same across all queries.Also trying to not get too much data in one go which would become a possibility in big query. — Sabir Khan

Nghia Do Nghia Do · Accepted Answer · 2016-09-09T09:19:41

This is an approach

Partitioner level This will be the top source which will drive your Reader. We will set Grid-Size = 1 in this case and perform the task as SyncTask (not AsyncTask).

Below is code snippet

<job id="exampleJob" xmlns="http://www.springframework.org/schema/batch">
              <step id="stepId">
                     <partition step="partitionerStepId" partitioner="examplePartitioner">
                            <handler grid-size="1" task-executor="syncTaskExecutor" />
                     </partition>
              </step>
       </job>

       <step id="partitionerStepId" xmlns="http://www.springframework.org/schema/batch">
              <tasklet>
                     <chunk reader="exampleReader" writer="exampleWriter" processor="exampleProcessor" commit-interval="1"/>
              </tasklet>
       </step>

public class ExamplePartitioner implements Partitioner {

@Override
public Map<String, ExecutionContext> partition(int gridSize) {
    Map<String, ExecutionContext> queue = new HashMap<String, ExecutionContext>();

    for (int i=0; i<NUMBER_QUERIES; i++) {
        ExecutionContext ec = new ExecutionContext();
        **ec.put("sql", [your query]);**

        queue.put("item"+i, ec);
    }

    return queue;
}

}

Note: NUMBER_QUERIES as 11 for your case. Note: The content of query you can store in a secure place or you can keep as constants in a class. I'm not quite sure the queries have parameters or not because you have not mentioned yet.

Reader Note: Please build 'exampleRowMapper' to map to VO objects.

To summarize, the Partitioner will build a list queries and put them into ExecutionContext and transfer them via "#{stepExecutionContext['sql']} to Reader each by each by sequence.

Thanks, Nghia

Note: I have a problem to format the Reader, that is why I put as an image. Note: For more information, please refer link from here

Multiple SELECT SQLs for Spring Batch Reader - reader chaining

1 Answers