advise on spring db query

[kalyancbs]

I am looking to use Spring Batch to read Flat file, one of the column values in each line in the file will be used to fetch additional data from the db tables similar to sql join between two tables except in this case one table is a flat file. Please advise how I can do.

[Dave Syer]

An ItemProcessor, maybe?

[aks]

Dave,
Do you advice to fire a database query for each record read from that file ? That will be expensive operation on database side.
I have a similar requirement where-in I am trying to read data from db and for each record read I have to do additional looks ups in db tables for getting some information.
Based on the information I got I will have to decide how many records i need to create for the output.I am not interested in writing the same records that i read from input; rather
the source record will be used for additional lookups in the table and I will create new records based on the result of the lookup.
Do you think spring batch could help me in this regard ?

[Dave Syer]

You are correct that an ItemProcessor is not a good place to do batching for performance improvement, but I wouldn't write it off until you have tried it. ItemWriter is probably the best place for batching, since it has a signature that was designed for these enhancements, or I guess you could do it in a complicated ItemReader (c.f. the existing JDBC readers).

[aks]

I was looking at the existing jdbc readers but dont see a functionality I am looking for(doing additional lookups for each source record read).The other idea I have is,In stead of doing additional look ups for each record, I can cache the input data as they are read using ehcache and process them only when all the desired records are available in the cache .that means,the item writer need not be called for each item read through the reader (becos I may not be able to process that input item immediately as additional information is required from other records and those records will be available in subsequent read ).Is there a option in spring batch where i could disable the call to item writer for each item reader call.I would like to call the item writer only when I finished processing of the input data and this processing of input data may happen long after it was read from the database.

Thaks
aks

[Dave Syer]

It seems perfectly reasonable to store the items temporarily in some back end (a cache if you like, or just the database). You would be using the ItemWriter to insert the items in temporary storage. If your input stream is not ordered in such a way that data downstream might be needed to process the current item, I don't see how you have a choice but to adopt this approach anyway.

I'm not really sure what you mean be "disable the call to the ItemWriter". Note that the ItemWriter is not normally called for every item read, it is called in batches. You can opt out of a particular item by returning null from an ItemProcessor, or you can leave the ItemWriter, or ItemProcessor, out of the chain completely. Don't know how that helps though.