How to retrieve/set SQL query for an ItemReader from database?

0
votes

I have spring batch program which reads the data from DB and process it and inserts(using ItemWriter) in to other table in the database. Here i am using a bunch of SQL queries for ItemReader,ItemProcessor and ItemWriter.

my requirement is store all these queries in a table with parameter and value format and retrieve it with a single DB call and pass it to ItemReader or ItemProcessor or ItemrWriter. So that if there is any change in the queries in future, we will end up in doing only DB updates and the code will be untouched.

I tried to do in beforeJob section but i am facing error saying "java.lang.IllegalArgumentException: The SQL query must be provided". But i can do this successfully by making an DB call inside the ItemReader method. Iam trying to avoid this way of approach because i need to make db call for each ItemReader,ItemProcessor and ItemWriter. Please let me know how to achieve this ?

2
spring-batch

2 Answers

1
votes

You can create a step with a tasklet that reads the query from the database and adds it to the execution context under some key, then configure the reader of your chunk-oriented step with the query from the execution context. Here is a quick example:

1. Retrieve the query and put it in the execution context:

@Bean
public Tasklet queryRetrievalTasklet() {
    return (contribution, chunkContext) -> {
        String query = ""; // retrieve query from db (using a JdbcTemplate for example)
        chunkContext.getStepContext().getJobExecutionContext().put("query", query);
        return RepeatStatus.FINISHED;
    };
}

2. Configure the reader with the query from the execution context

@Bean
@StepScope
public ItemReader<Integer> itemReader(@Value("#{jobExecutionContext['query']}") String query) {
    // return your reader configured with the query
    return null;
}

Hope this helps.

0
votes

In my opinion, such configuration is usually done storing queries in properties not in database. Like :

batch.query.unload=SELECT ...