Efficient way to query database from a program processing big list of items

1
votes

I have a spring batch program with itemReader, itemProcessor and itemWriter.

Say I have 10,000 records to process. For each item I want to fetch data from several database table for deciding some conditions and adding some data.

I believe this will be done during processing.

Question: What is the best design to do this ? I am bit skeptic about running several Select queries for each item injecting SimpleJdbcTemplate in itemProcessor. Is there any other efficient way to do this ??

Thanks in advance!! Nik

1
databasespringdependency-injectionspring-batch
What about using a set of joins to bring in the data from the other tables that you need to consider? – cdhowie
@cdhowie: Yes. But here I will have to run that join query for each of the 10000 records. Right ? Can you give some example. – Vicky
You would run ONE query that fetches the 10,000 records and joins them to the other tables you need to consider. So you should be able to get all of the data you need in one query. I can't really provide examples without more information about your tables and the queries involved. – cdhowie
@cdhowie: Say I have list of 10000 POJOs with 4 properties A, B, C, D. A, B, C values I am reading using itemReader. However, the value in D is set in itemProcessor. How ? Through complex logic which requires querying 2, 3 tables in different databases. This step I will have to perform for all 10000 POJOs. How is it possible with one query ? – Vicky

1 Answers

0
votes

Perhaps you should insert the 10,000 "POJOs" into a table in the datanbase, and then run a query that joins that to the other tables?