Like a picture, an example, is worth a thousand words. The following code retrieves all folders and files belonging to a user from a content management system.

Highlights

1. Pluggable java classes for relational data access

2. Pluggable java classes to construct the hierarchical data set

3. Output could be an object, xml or a just tree data structure

4. lazy loading of children trees

Code

#
# Data definition for a filetree
#
request.filetree.classname=HierarchicalDataComposer
#
#Maindata request
#
request.filetree.maindataRequest.classname=RDBMSReader
request.filetree.maindataRequest.db=reportsDB
request.filetree.maindataRequest.stmt=\
\
select first_name as user_first_name \
	,last_name as user_last_name \
	,email as user_email \
from users \
where user_id = {ownerUserId.quote}

#
#first primary loop
#
request.filetree.FoldersLoop1.class_request.className=RDBMSReaderAdapter
request.filetree.FoldersLoop1.query_request.className=RDBMSReader
request.filetree.FoldersLoop1.query_request.db=reportsDB
request.filetree.FoldersLoop1.query_request.stmt=\
	select * from folders  \
	where 1=1 \
		and parent_folder_id is null \
		and public = 'Y' \
		and owner_user_id = {ownerUserId.quote} \
	order by folder_name 
request.filetree.FoldersLoop1.loopNames=itemsLoop

#
#Child loop 1
#
request.itemsLoop.class_request.classname=RDBMSReaderAdapter
request.itemsLoop.query_request.classname=RDBMSReader
request.itemsLoop.query_request.db=reportsDB
request.itemsLoop.query_request.stmt=\
	select * from filed_items fi, reports r \
	where 1=1 \
		and fi.item_id = r.report_id \
		and fi.folder_id = {folder_id} \
	order by r.report_short_name

Lazy loading (worth mentioning)

Even if there are 100s of main folders, the child sqls are expanded only if the UI opens up that folder and requests for that data.

Caveats

Obviously this may not be ideal way to construct data if your intention is to retrieve all or most of folders and their children all the time. In that case one can write a special handler which will do a table join and then break it up into a tree to save on the multiple sqls that get fired back.

References

Aspire/J2EE home page