Spring Batch Dynamic Query JdbcPagingItemReader and Pass Data Between Steps

0
votes

I have a requirement using Spring Boot and Spring Batch where i need to read data from one database which returns the records like below

Id1  Select * from Table1
Id2  Select * from Table2

Each query will be executed in different database and will return over 100k records Below are the queries i am stuck with

  1. Pass the query to the reader (i.e from one step to other step)

  2. Dynamically read the queries and fetch the records using JdbcPagingItemReader, as each query would return > 100k records and write with item writer.

All the queries will return the same item/pojo.

2
spring-bootspring-batch
@MahmoudBenHassine Can you Please throw some light on this. - Sonu Agarwal
Sure! However, the question is not clear to me. You need to read the same data from two different tables and write them in another one? Is that correct? - Mahmoud Ben Hassine
@MahmoudBenHassine Thanks for reply.Let me be clear on my question. First query returns a column value which is again a sql query,which need to be executed in another database and read by pagination and insert them into a first database.Let me know if you need additional details - Sonu Agarwal

2 Answers

0
votes

First query returns a column value which is again a sql query,which need to be executed in another database and read by pagination and insert them into a first database

In this case, you can proceed in two steps:

  • Step 1 (tasklet): executes the first query and puts its result (the query to be executed by the reader) in the execution context
  • Step 2 (chunk-oriented): Reads the query from the execution context and passes the query dynamically to the reader
0
votes

Have to create query using Placeholder(?)

*EX Query-SELECT * FROM PRODUCT WHERE status=? AND name=?*

Create ItemStreamReader :-

@Bean
public ItemStreamReader<Product> reader() {
    JdbcCursorItemReader<Product> reader = new JdbcCursorItemReader<>();
    reader.setDataSource(DataSource);
    reader.setSql(SELECT_QUERY);
    reader.setPreparedStatementSetter(new InputSetter());
    reader.setRowMapper(new BeanPropertyRowMapper<>(Product.class));
    return reader;
}

Create one Implementer class for PreparedStatementSetter interface

public class InputSetter implements PreparedStatementSetter {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
    ps.setString(1, "status");
    ps.setString(2,"Name");

}

}