Tree like structure from SQL to HTML UL

Question

I have a tree like structure in database.

I have a query that returns my records sorted just the way I want them displayed.

SELECT branch_id, branch_name, parent_branch_id, level FROM dw_branches
start WITH parent_branch_id IS NULL
connect BY PRIOR branch_id = parent_branch_id

Which returns something like this

| branch_id | branch_name | parent_branch_id | level|
|         1 | one         |                  |     1|
|         2 | two         |                1 |     2|
|         3 | three       |                1 |     2|
|         4 | four        |                2 |     3|
|         5 | five        |                1 |     2|
|         6 | six         |                5 |     3|

In Java, I have a Branch object which has all these parameters (id, name, parent_id, level).

I want to do an output in JSP which would end up looking like this:

<ul>
    <li>one
        <ul>
            <li>two
                <ul>
                    <li>four</li>        
                </ul>
            </li>
            <li>three</li>
            <li>five
                <ul>
                    <li>six</li>        
                </ul>
            </li>           
        </ul>
    </li>
</ul>

Basically, I want to nest ul/li in order to display data using http://bassistance.de/jquery-plugins/jquery-plugin-treeview/

Any suggestions on how to do so?

How big a tree are you talking, in terms of nodes and average depth? — Dilum Ranatunga, Sep 19 '11 at 14:20
Have a look at this: http://stackoverflow.com/questions/1356401/generic-tree-implementation-in-java — cuh, Sep 19 '11 at 14:43

score 1 · Answer 1 · answered Sep 19 '11 at 15:00

Define a Node data structure which has a name children.
Make a Map<Integer,Node> where you can look up a data structure by id.
Iterate over your SQL ResulSet, looking up or creating the parent node for each record, and adding the child node to the parent.
Get the root-level node and output it to a String using recursion.

Rough code example:

Node {
  String name;
  List<Node> children = new LinkedList<Node>();

  void appendHTML(StringBuilder toAppendTo) {
    toAppendTo.append("<li>").append(name);
    if (!children.isEmpty()) {
      toAppendTo.append("<ul>");
      for ( Node child : children ) {
        child.appendHTML(toAppendTo);
      }
      toAppendTo.append("</ul>");
    }
    toAppendTo.append("</li>");
  }
}

Thanks, I'll take a look at it. However, appending HTML is not the solution I was going for, I'd like iterate over elements in JSP using c:forEach. — eagerMoose, Sep 20 '11 at 07:50

Tree like structure from SQL to HTML UL

1 Answers1