Child parent problem with database and Jtree

I got a lazy-loading tree and a table in a DB called Folders, it contains a folderId and a parentFolderId.

So, I have all the parent folders at start, and if I want the childs of a certain folder I just expa开发者_如何学Pythonnd the node and a

Select * from Folders where parentFolderId=?

will get me the next nodes and so on, all pretty.

PROBLEM: My app is meant to copy the structure from the table to the FileSystem. If someone selects a node it will expand its immediate childrens but not the grandchildrens.

Querying each folder to see if it has childrens recursively was my only idea, but given that my table has 10000 rows I'm making 10000 queries... thats wrong, how can I do this properly?

EDIT:

I could reformulate this to: whats the best way to obtain a full tree given the parent node and all the folders in a same table, other than querying for childrens recursively, because otherwise I'm querying the DB 10000 times

EDIT:

As found on this article http://threebit.net/tutorials/nestedset/tutorial1.html, my situation is this:

User searches all nodes beneath a single node.

This is a big problem, as most users expect to get search information back quickly. If you are running some sort of EIS system where people are constantly searching through deeply nested set of relational information you will soon be fired. This means we have to find an O(x) or quicker algorithm, or rethink the data schema. As very few DBA's have the luxury of rewriting their schemas on a whim, you have to think up a quick and easy way to improve the performace of the system without messing up your legacy stuff. This is why you want to use the Nested Set Model.

Your approach is fine for small data sets; but if you want to go large you need to look at the nested set model for this sort of problem. Mysql.com had a good article on it, but it's gone now. Here is an alternative: http://www.sitepoint.com/hierarchical-data-database-2/

Here is a sample query using MS SQL Server that may help you

With Folders As
(
    Select 1 as FolderId, NULL as ParentFolderId UNION
    Select  2, 1 UNION
    Select  3, 1 UNION
    Select  4, 2 UNION
    Select  5, 2
)
Select Folders.FolderId, ISNULL (Children.NumChildFolders, 0) NumChildFolders
From Folders
Left Join
(
    Select ParentFolderId, Count (*) NumChildFolders
    From Folders
    Group By ParentFolderId
) Children
On Folders.FolderId = Children.ParentFolderId