1

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.

Sabir Khan
  • 9,826
  • 7
  • 45
  • 98
  • Possible duplicate of [Spring batch Job read from multiple database](http://stackoverflow.com/questions/21304364/spring-batch-job-read-from-multiple-database) – Luca Basso Ricci Sep 09 '16 at 09:33
  • 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 Sep 09 '16 at 09:52
  • [This](http://stackoverflow.com/questions/37462534/how-to-implement-reader-for-multiple-queries-but-same-output-item) looks a duplicate question but I don't see any accepted answer. – Sabir Khan Sep 09 '16 at 10:21
  • You may use a UNION to create a single big query and use just a single reader. – Luca Basso Ricci Sep 09 '16 at 10:31
  • 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 Sep 09 '16 at 10:36

1 Answers1

1

This is an approach

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

  1. Reader enter image description here 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

Nghia Do
  • 2,588
  • 2
  • 17
  • 31