0

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...

milind_db
  • 1,274
  • 5
  • 34
  • 56

2 Answers2

1

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.

Andreas Fester
  • 36,091
  • 7
  • 95
  • 123
1

First of all, form your question it is not clear if you need help on SQL side or java data structure side.

I assume both i.e. you need to fetch data from database & then populate in appropriate java data structure.

So there is no direct way for this. Your data is mainly TREE STRUCTURE, so mainly you need to focus on java data structure for tree. So below are the steps that you need to take.

1) First create java class structure for tree structure. Refer below links for that.

Java tree data-structure?

http://www.java2s.com/Code/Java/Collections-Data-Structure/Yourowntreewithgenericuserobject.htm

http://www.codeproject.com/Articles/14799/Populating-a-TreeView-Control-from-the-Database

2) Then you don't need much from sql side if you finally need java objects. SO you can do plain select query to fetch data.

3) Now you need to have a helper method for preparing tree for you data using class structure form step 1. If you do better design, you tree class structure can be made self constructing using SQL data.

Community
  • 1
  • 1
Ravi K
  • 976
  • 7
  • 9