Search code examples
javasqlhierarchyderbyjtree

Derby Database - Determine Levels in a Hierarchy to convert a DB -> DefaultMutableTreeNode Java object

I've got a table in Derby:

CREATE TABLE IMPORT_RULES (
   RULEID    INTEGER NOT NULL,
   PARENTID  INTEGER,
   NAME      VARCHAR(25) NOT NULL,
   PRIMARY KEY (RULEID)
)

PARENTID is the RULEID of the parent, but the depth of the parent/child relationship is unknown.

My goal is to create a DefaultMutableTreeNode that represents the hierarchical data.

My thought was that the best way to do this would be to have a SQL query that returned the "levels" of every item in the database and cycle backwards from the lowest level to the highest.

My problem is that I can't seem to come up with said query for Derby.

Any ideas on how to generate this query, or otherwise achieve the goal?

Solution

  • If H2 Database is an acceptable alternative, it supports Recursive Queries. See also this answer regarding custom tree models.