subquery in spring batch item reader

Question

Let's say I have a person table. A person can have one to three products and it looks like this.

id  person_id  product  price
1   person1    product3   1
2   person1    product2   2
3   person1    product1   10
4   person2    product1   11
5   person2    product2   14

I should get all products for each person, then process it (do mappings to prices, do some logic) and then I write calculated data to the final table, which contains only two fields (person_id and calculated_value, where person_id is a key)

person_id  calculated_value
person1        100   
person2        111   
person3        93

What is the best way to implement item reader in this case ? (get all products for each person and process it) Is it possible to do it in one query in the item reader or I should do additional query in the item processor for each person ?

@GordonLinoff it is a simplified example.. let's say I have mapping tables for each price, and formulas what to do in each case. Based on formulas and mapping tables I can get only one calculated value for each person. — troublemaker33
. . I would recommend storing those as tables in the database and doing the calculation in the database. — Gordon Linoff

Atmas Atmas · Accepted Answer · 2021-04-08T18:44:56

This is more of a design question so might be ill fit for SO, but I will try to assist. In general, you should aim for the step processor itself to retrieve no data and only do processing. Instead, the reader should gather up all data and perform mappings on the data as it reads it and present one calculation-driven unit of work which can then be processed and subsequently outputted to wherever is required.

With that in mind...

Is it possible to do it in one query in the item reader ^-- If this is possible to do all via query, yes you should.

Also, if you need programmatic intervention/lookups/mappings based on data from responses in the query, then you make a compound reader from both programmatic parts AND the existing Sql readers so that you read fields from the SQL reader you need and then convert these into units of works programmatically enriched and then passed on to the processor for work. If you need multiple rows to effectively be combined into one unit of work, look into the aggregating item reader approach. Here is an SO that discusses that: spring batch aggregate records from db as one single record

...or I should do additional query in the item processor for each person ? ^-- You should avoid gathering/querying more data in the processor. While you can do it just fine and it's super convenient, it is not the designated place to get more data per the framework. It's best if the processor is designed as a stateless engine and all the gathering be the responsibility of the reader(s).

subquery in spring batch item reader

2 Answers