How to read multiple tables using Spring Batch

Question

I am looking to read data from multiple tables (different database tables) and aggregate and create final result set. In my case, each query will return the List of object. I went through web many times, I found no link other than - Spring Batch How to read multiple table (queries) as Reader and write it as flat file write, but it returns only single object.

Is there any way if we can do this ? Any working sample example would help a lot.

Example -

One query gives List of Departments - from Oracle DB
One query gives List of Employee - from Postgres

Now I want to build Employee and Department relationship and send final object to processor to further lookup against MongoDB and send the final object to reader.

Do you mean different tables in different database servers/schemas? Is doing a single query that joins the tables an option for you? Can you give an example of input/output? — Mahmoud Ben Hassine
Yes different DB servers and schemas. No we cant do that using join its purely different DB like Oracle and Postgres. — Jeff Cook
I think you need to design a solution to this requirement before looking for how to implement it with Spring Batch. How would you implement this without Spring Batch? send the final object to reader.: I guess you mean "send the final object to the writer" here, is that correct? — Mahmoud Ben Hassine
Yes I would have to query multiple times to gather info and then create final object — Jeff Cook

Mahmoud Ben Hassine Mahmoud Ben Hassine · Accepted Answer · 2020-12-21T08:33:05

The question should rather be "how to join three tables from three different databases and write the result in a file". There is no built-in reader in Spring Batch that reads from multiple tables. You either need to create a custom reader, or decompose the problem at hand into tasks that can be implemented using Spring Batch tasklet/chunk-oriented steps.

I believe you can use the driving query pattern in a single chunk-oriented step. The reader reads employee items, then a processor enrich items with 1) department from postgres and 2) other info from mongo. This should work for small/medium datasets. If you have a lot of data, you can use partitioning to parallelize things and improve performance.

Another option if you want to avoid a query per item is to load all departments in a cache for example (I guess there should be less departments than employees) and enrich items from the cache rather than with individual queries to the db.

How to read multiple tables using Spring Batch

1 Answers