Generate xml dynamically in java using data from a table in database

Question

Here is the problem that i am currently stuck with for the past few days. And I am looking for guidance / approaches on how to handle.Hints & suggestions welcome. so here is the problem.The database has a table "group" which has two columns : group_id on parent_group_id.group_id is the primary key for the table .All entries in this table represent groups/sub-groups.If one adds a sub-group from the front end ,then an entry gets inserted in to the group table with an auto-generated group_id which MySQL generates.the parent_group_id corresponds to the group_id of the group on which a sub-group was added.So in essence it's acting like a foreign key to the group_id column.My task cut out here is to generate an XML in java using the data from the group table. So this is where i am stuck.I know it's gonna be a recursive function which needs to be written but cant figure out a way how to dynamically create the nodes and fill the data from the Db at the same time.The end XML needs to be sent as json data to the front end.

A group can have n-sub groups and the hierarchy can go on.For ex- Say Vehicle is root node with group_id =1.It can have cars & bikes as sub-groups.so the parent_group_id will be 1 for car and bike and group id say will be 2& 3 respectively.

P.S: this is the first time i am posting here having had used this site for the past one year.Please let me know if any more info is needed or whether you are able to comprehend my problem.

3

please post your code as well – Satya Oct 22 '12 at 15:25

score 1 · Answer 1 · edited May 23 '17 at 12:18

1

If you split the task into two, it will be more manageable.

Here are some useful links on querying hierarchical data in relational databases and specifically in MySQL:

What are the options for storing hierarchical data in a relational database?

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

http://www.slideshare.net/billkarwin/models-for-hierarchical-data

http://en.wikipedia.org/wiki/Common_table_expressions#Common_table_expression

As long as you have the query result properly sorted, you will be able to traverse it recursively, building the XML tree step by step.

edited May 23 '17 at 12:18

Community

1
1

answered Oct 22 '12 at 15:54

full.stack.ex

1,747
2
11
13

Was able to solve it by using recursive functions :). Thanks for the assistance – mohit Taparia Oct 29 '12 at 16:36

score 1 · Answer 2 · answered Oct 29 '12 at 16:37

1

Was able to solve it by using recursive functions :). Loaded all the data using the entity class and then iterated over the data using recursive functions to build the tree like structure.I didn't try and take the sql way.

answered Oct 29 '12 at 16:37

mohit Taparia

31
6

Generate xml dynamically in java using data from a table in database

2 Answers2