Search code examples
javamysqlstructureparent-childhierarchy

How to retrieve hierarchical items in one go from SQL table in any java structure for comparison?

I want to read items from SQL table which are in Hierarchical manner. e.g. My table having Parent-Child relations. Following is my table structure:

id    name    parentId

1     abc      null

2     xyz        1

3     lmn        1

4     qwe       null

5     asd        4

So I want to load all the Item hierarchically in one go, How can I do that? I tried HashMap but it feels very complex.... thanks in advance...

Solution

  • You can retrieve the whole hierarchy through one SQL query, but the query depends on the database system - for instance, Oracle supports a "CONNECT BY" query for hierarchical queries. MySQL does not support this directly - but there are some very good approaches explained at Hierachical Queries in MySQL. I suggest that you try one of these. The basic approach is to create a function which imitates the CONNECT BY.