Populating a tree structure from a recursive query

[madiraluap]

Hi Everyone,

I am trying to emulate a tree structure like this:

A
A.1
A.1.1
A.2
B
C
C.1
C.1.1

and so on.

I have the sql query figured out.
It returns the parent id, folder name, category code, the path, level
and the leaf indicator.

Now I am trying to put it through a recursive function but i am not
getting it correctly.

I am sure someone here has run into the same troubles and was wondering
if you could suggest anything. Any help would be appreciated.

This is my query and code:

QUERY

SELECT Folder.FOLDER_ID,
Folder.FOLDER_NAME,
Folder.CATEGORY_CD,
Folder.PARENT_FOLDER_ID,
CodeValue.CODE_VALUE AS CODE_VALUE1,
CodeValue.DISPLAY,
LPAD(' ', 2*level-1)|| decode (category_cd,-1,'
','\'||category_cd) || SYS_CONNECT_BY_PATH( folder_name, '\') "Path",
(level+1) L,
CONNECT_BY_ISLEAF "IsLeaf"
FROM FOLDER Folder, CODE_VALUE CodeValue
WHERE Folder.CATEGORY_CD = CodeValue.CODE_VALUE
connect by Folder.parent_folder_id = prior Folder.folder_id
start with parent_folder_ID is null
ORDER SIBLINGS BY folder_name

I am using oracle adf and view objects to do this:

public DefaultMutableTreeNode getCompleteTree()
{
DocumentTreeViewImpl dtv = getDocumentTreeView();
DefaultMutableTreeNode root = new
DefaultMutableTreeNode("ROOT");

//retrieving the categories and the level 1 folders first.
dtv.executeQuery();
dtv.reset();

TreeMap catfolders = new TreeMap();

while (dtv.hasNext())
{
DocumentTreeViewRowImpl dtvrow = (DocumentTreeViewRowImpl)
dtv.next();

if (dtvrow != null)
{
if ((dtvrow.getCategoryCd().intValue() != -1) &&
(dtvrow.getIsleaf().intValue() == 1))
{
String currentCategory = dtvrow.getDisplay();
DefaultMutableTreeNode currentCat = new
DefaultMutableTreeNode(currentCategory);

if (!catfolders.containsKey(currentCategory))
{
ArrayList folders = new ArrayList();
String firstFolder = dtvrow.getFolderName();

DefaultMutableTreeNode ff = new
DefaultMutableTreeNode(firstFolder);
System.out.println("Adding new category " +
currentCategory);
System.out.println("First folder: " + ff);

folders.add(ff);
catfolders.put(currentCategory, folders);
}
else
{
ArrayList fl = ((ArrayList)
(catfolders.get(currentCategory)));
System.out.println("Consequent folder: " +
dtvrow.getFolderName());

if (fl.contains(new
DefaultMutableTreeNode(dtvrow.getFolderName())) == false)
{
((ArrayList)
(catfolders.get(currentCategory))).add(new
DefaultMutableTreeNode(dtvrow.getFolderName()));
}
}
}
else if ((dtvrow.getCategoryCd().intValue() != -1) &&
(dtvrow.getIsleaf().intValue() == 0))
{
String currentCategory = dtvrow.getDisplay();

DefaultMutableTreeNode rnode = getChildren(new
DefaultMutableTreeNode(dtvrow.getFolderName()));
((ArrayList)
(catfolders.get(currentCategory))).add(rnode);
}
}
}

Set keys = catfolders.keySet();

for (Iterator it = keys.iterator(); it.hasNext()
{
String currentKey = it.next().toString();
ArrayList currentFolderList = ((ArrayList)
(catfolders.get(currentKey)));
DefaultMutableTreeNode currentCat = new
DefaultMutableTreeNode(currentKey);

for (int i = 0; i < currentFolderList.size(); i++)
{
currentCat.add((DefaultMutableTreeNode)
currentFolderList.get(i));
}

root.add(currentCat);
}

return root;
}

public DefaultMutableTreeNode getChildren(DefaultMutableTreeNode
parent)
{
DocumentTreeViewImpl dtv = getDocumentTreeView();

while (dtv.hasNext())
{
DocumentTreeViewRowImpl dtvrow = (DocumentTreeViewRowImpl)
dtv.next();

if (dtvrow.getCategoryCd().intValue() == -1) // as long as
its a child folder keep going
{
if (dtvrow.getIsleaf().intValue() == 0) //not leaf
{
DefaultMutableTreeNode childNode = new
DefaultMutableTreeNode(dtvrow.getFolderName());


getChildren(childNode);

}
else if (dtvrow.getIsleaf().intValue() == 1) //leaf
{
DefaultMutableTreeNode childNode = new
DefaultMutableTreeNode(dtvrow.getFolderName(), false);
parent.add(childNode);
}
}
else
{
//parent.add(child);
break; //return parent;
}
}

return parent;
}


It seems that at a particular point it just keeps on adding to the
child of a child instead of returning back to the last parent. I have
no clue how to "rewind" it back to the last parent.

 

[Roedy Green]

A
A.1
A.1.1
A.2
B
C
C.1

if you named your nodes instead:


A.0.0
A.1.0
A.1.1
A.2.0
B.0.0
C.0.0
C.1.0

Then the SQL part would be very easy. Just get the lot in order.
Each record has three fields and a composite key on all three.

leafiness is field > zero.

Now you only have to deal with the tree structure in Java.

 

[madiraluap]

Thank you for replying.
Its not that the path where im really facing a problem. I can't seem to
find a way to traverse through the subnodes and then be able to return
to the parent after all the nodes are done. For some reason, it keeps
on adding to the child node.

Eg:

Applications
A
B
C
D
Dean Batten
E
F
G

I can tell if the node is a leaf or not so i know when to call the
recursive function but I can't seem to figure out how to get back to
say the applications node to keep adding the alphabet nodes.

The reason I couldn't pick out the paths and construct this tree is
because the nodes filter an adjacent table on select. so i need to have
the tree in memory in either datasets or treemaps etc.

Im probably missing some little piece in this puzzle but i think ive
been looking at it for too long.
Thanks for all your help

 

[madiraluap]

Thank you for replying.
Its not that the path where im really facing a problem. I can't seem to
find a way to traverse through the subnodes and then be able to return
to the parent after all the nodes are done. For some reason, it keeps
on adding to the child node.

Eg:

Applications
A
B
C
D
Dean Batten
E
F
G

I can tell if the node is a leaf or not so i know when to call the
recursive function but I can't seem to figure out how to get back to
say the applications node to keep adding the alphabet nodes.

The reason I couldn't pick out the paths and construct this tree is
because the nodes filter an adjacent table on select. so i need to have
the tree in memory in either datasets or treemaps etc.

Im probably missing some little piece in this puzzle but i think ive
been looking at it for too long.
Thanks for all your help

 

[Roedy Green]

I can tell if the node is a leaf or not so i know when to call the
recursive function but I can't seem to figure out how to get back to
say the applications node to keep adding the alphabet nodes.

your logic could work something like this:

does this new node belong to me? If so add it and return

does this new node belong to my parent (or ancestor or sibling or
niece ...)? if so RETURN it as a value for my parent to deal with.

does this new node belong to one of my children? If so fob it off on
them end let them recursively deal with it.

A child may pass a new node up to me to iteratively deal with I just
start at the top for your node..

Whenever you insert a node, get another and continue from when you
left off start at the top for your node.

All this is in ram. You can simply things by always starting at the
root and working your way down to find the insertion point, rather
than clambering alternately up and down the tree, though the
clambering should be faster just like adding loops of toilet paper to
a real tree.

 

[philip]

Hi.. I use this:

// set-up Nodes and return the top most Node
public DefaultMutableTreeNode setupNodes ()
{
// get the data
OracleResultSet ors;
ors = (OracleResultSet) dcon.getQuery(DML[0]);

DefaultMutableTreeNode top = null;
int ParentNodeId = 0;
NodeBean ne;

try {

while (ors.next())
{
ne = new NodeBean();
ne.setNodeId(ors.getInt("NODE_ID"));
ne.setNodeName(ors.getString("NODE_NAME"));
ne.setNodeParent(ors.getInt("NODE_PARENT"));
ne.setNodeCreated(ors.getString("NODE_CREATED"));
ne.setNodeParentName(ors.getString("NODE_PARENT_NAME"));

// System.out.println(ne.getNodeName());
if (ne.getNodeParent() == ParentNodeId)
{
top = new DefaultMutableTreeNode ();
top.setUserObject(ne);

}
else buildNodes (top, ne);
}

} catch (Exception exc) {
exc.printStackTrace();
return null;
}

return top;
}

// pass in the top node and return once done
public DefaultMutableTreeNode buildNodes (DefaultMutableTreeNode rootN,
NodeBean ne)
{
try {
if
(((NodeBean)rootN.getUserObject()).getNodeName().equals(ne.getNodeParentName()))
{
DefaultMutableTreeNode dmtn = new DefaultMutableTreeNode();
dmtn.setUserObject(ne);
rootN.add (dmtn);
}
else
{
for (int i=0; i<=rootN.getChildCount()-1; i++)
buildNodes ((DefaultMutableTreeNode)rootN.getChildAt(i), ne);
}
}
catch (Exception exc)
{
exc.printStackTrace();
}
return rootN;
}

With this
1. Make sure the nodes are in sequence
2. I am using JDBC - but ADF is simple to adapt for this.

 

[Fez]

Thanks ! It worked !!!!! Really appreciate all the help