Dynamic SQL query with JdbcCursorItemReader

Question

I'm using java configuration (spring-boot) for spring batch. I have a list of Employee Ids and for each Id, I need to run a query (like below) and then process the data.

select * from history where employee_id = ?

I understand we can use reader.setPreparedStatementSetter to dynamically set the parameter in the above SQL. However, I'm not sure how I can repeat the batch process for each of the employee id in the list. Even if I mark the reader() as @StepScope, the reader is called only once. (i.e.), the batch runs only once. Any help is appreciated.

    List employeeIds = new ArrayList();
    employeeIds.add(1);
    employeeIds.add(2);

    @Bean
    @StepScope
    public ItemReader<History> reader() {
        JdbcCursorItemReader<History> databaseReader = new JdbcCursorItemReader<>();
        databaseReader.setSql("select * from history where employee_id = ?");
        databaseReader.setPreparedStatementSetter(..);
        ....

        return databaseReader;
    }


    @Bean
    public Step step(StepBuilder stepBuilder){
        return stepBuilderFactory.get("sample").
                .reader(reader())
                .processor(processor())
                .writer(writer())
                .build();
    }

This is a common pattern (docs.spring.io/spring-batch/reference/html/…). Your reader returns ID from the id-list and your current reader must be converted to a processor — Luca Basso Ricci
– Luca Basso Ricci, Commented Mar 20, 2017 at 20:43
Hmm, can one ItemReader be mapped to multiple ItemProcessors? Because, I want the ItemProcessor to run for every ID in the original list. — saravana_pc
– saravana_pc, Commented Mar 20, 2017 at 21:42
Yes, use a composite item processor (docs.spring.io/spring-batch/apidocs/org/springframework/batch/…) — Luca Basso Ricci
– Luca Basso Ricci, Commented Mar 20, 2017 at 22:14

Dean Clark · Accepted Answer · 2017-03-20 17:41:05Z

3

So first and foremost, I don't recommend you do this. In theory, and almost always in practice, opening a single cursor is FAR more efficient than querying separately for each time.

The "better" way would be to insert the ID list into a staging/driving table (typically in an earlier step, whenever you get that list of IDs from the original source) and then change your query to be something like:

select * from history where employee_id in (select id from driving_table)

Alternatively, you could, at a bare minimum, at least change your query to:

select * from history where employee_id in ( ? )

and pass in a list of IDs to it (be careful here as some databases limit the # of parameters in a query). If your list threatens to exceed that limit, you'd need to open a new cursor, effectively paginating over the list.

answered Mar 20, 2017 at 17:41

Dean Clark

3,8981 gold badge13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saravana_pc Over a year ago

Thanks. The list contains about 200000 records - so "IN" wouldn't work. I could use JOIN instead.

Dean Clark Over a year ago

Absolutely do the join. It will perform far better than pulling the data back just to query with it again and will reduce unnecessary I/O.

Collectives™ on Stack Overflow

Dynamic SQL query with JdbcCursorItemReader

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related