reading tables at a time

[Laxman]

Hi.

Please help to find the suitable solution for this problem


I have five tables in a database. One master table and four child tables.

I need to read all records from the master table and for each record returned, then go to the first child table and pick matching records after that i go to the second child table and fetch matching records, like this i need to match all five tables and pick the matching records

as we are using db2, we cannot use join query or Stored Procedure.

is it possible to read each table have a list outside the Processors process() method so that i can iterate each list and have corresponding record.

Thanks in Advance

[Marten Deinum]

Ehrm why couldn't use join on a db2 database... That works even in simple databases like H2 etc. so it definitly works on db2.

Next to that what you are doing isn't very efficient that is a classic example of a 1+N (in your case 1+ (4*N) ) query issue... (Execute 1 query to retrieve the root another query for each element of that query to retrieve the remainder).

[bwawok]

We needed to do something similar.

ItemReader is a really simple interface. What we did was:


1) Build up a composite reader that implemented the ItemReader interface. It had 1 reader to each table injected.

2) On a read, it would read 1 row from the master table.. and 0 - x rows from the child table. It would then list up the child results and populate them on the parent object, and return the parent Object. So your ItemReader<Parent> would return a parent with fully populated children.


The only trick was to make sure your main reader and child readers are sorted by the same thing (UID of parent is an easy one, as it will be on all 5 tables).

[Laxman]

Hi Thanks for the reply

Hi bwawok as you said......On a read, it would read 1 row from the master table.. and 0 - x rows from the child table.


I have used CompositeItemReader and inject 3 readers into it
but here how to define which reader when to read


Example i have 3 readers reader1, reader2, reader3



// let's say batch process has to call reader1
if( true )
{
return read1.read();
}
//let's say batch process has to call reader2
else if(true)
{
return read2.read();
}
else //let's say batch process has to call reader3
{
return read3.read();
}

[bwawok]

This is your own class you are writing?

I would just

a) Read 1 item from main reader
b) Keep reading items on the child reader until we don't match anymore (i.e. the FK key changed). Put the list of matches on the main item.
c) Keep the 1 extra guy we read too far as a class level variable to pick up next time.
d) Repeat b-c for each reader.