Search code examples
javamultithreadingjdbcspring-batchjava.util.concurrent

What is the best approach for loading data from DB by multiple threads

I have some data in database in parent child relation, where my table is actually representing a forest of tree data structure. enter image description here

And the table structure is like:

row1  parent: null
row2  parent:row1
row3  parent:row2
row4  parent:row1

Now when I am loading this data from DB to my data structure by JDBC, then what I am doing now is:

1. load all row where parent is null [load all the root of trees]
2. Then for each child of each root load the child in a recursive manner.
3. But here all the trees are loaded one by one.

What I want to do, spawn a thread for each tree, thus all the trees can be loaded concurrently. Any framework I can use with JDBC for the same, or any suggestion, help please.

Solution

  • Whilst this does not directly answer your exact question, I would encourage you to review your data structure. You have used the Adjacency List Model which, whist simple to implement initially, opens a whole ugly can of worms when it comes to querying.

    There are other models to consider, such as Nested Set Model, for which querying of trees and sub-trees is much easier.

    Both are explained well in this article: Managing Hierarchical Data in MySQL

    Although the article is RDBMS vendor specific, if could easily be adjusted to suit your system.