File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Building a query for Tree Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Building a query for Tree" Watch "Building a query for Tree" New topic
Author

Building a query for Tree

sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
Hi

In my application i have a folder tree where its stored in the Db in one table which maintains the parent id , folderid and the name of the folder

For example i have a folder structure like

a
--b
--c
--d

and the above will be stored in DB as


foldername folderid parentid

a 1 0(This is dummy id for the root folder)
b 2 1
c 3 2
d 4 1


and it goes on like that .......

Now given one folder id , i want to track the entire path from the root in single query .....

For eg given folderid 3 (folder is c) ....... i need to get that path as
a/b/c

is that possible to be achived in single query ?? its okay even if it can be achieved in 2-3 queries
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

It would be easy to form a query to get desired result.A folder can have any number of child folders hence one query would not suffice.

you can go for self join and in oracle connect by prior clause to form a query but You will have to play with java and queries both .


I haven't tried this but this is first thought in my mind

If I were to solve this problem I would have added one more column to database let say depth of folder 0 for root folder 1 for first child 2 for next and so on ....then I would have form a binary tree with delp of folder depth then a infix notation of binary tree would result in a path of folder.

can you tell me which database are you using ?

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415
Hi sailesh

Thanks for ur reply.....i was knowing this connect by clause in Oracle 9..but i am using some DB(HSQL.Its a light weight JAVA DB) which supports only basic things (joins are also supported)

i think there is no way to do this with this DB in single query ...i need to club my javalogic with multiple DB queries
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I think then you should go for couple of queries and take the advantages of maps in java and then you can form the folder path

Shailesh
[ May 25, 2005: Message edited by: Shailesh Chandra ]
Derek Clarkson
Greenhorn

Joined: Mar 04, 2004
Posts: 25
I have done something like this before. Firstly you have to design the data so that you can return it in the order in which you need to add the nodes.
So if you have

Then you could assign a sort value which brings them back in the correct order.

You need to get the data back using the sort. Then you simply add the nodes in that order, each time adding to the appropriate parent. because the sort ensures that each node appears after it's parent, the parents will always be ready regardless of the depth.
Derek Clarkson
Greenhorn

Joined: Mar 04, 2004
Posts: 25
Oh hang it. I just re-read your question. Are you talking about querying through the data ? By this a mean taking a specific node as a parameter to the sql and them tracing back to it's root, display each node in turn. If so then there is no way to do this in a single query that I can think of.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Building a query for Tree