HQL recursion, how do I do this?

Question

I have a tree structure where each Node has a parent and a Set<Node> children. Each Node has a String title, and I want to make a query where I select Set<String> titles, being the title of this node and of all parent nodes. How do I write this query?

The query for a single title is this, but like I said, I'd like it expanded for the entire branch of parents.

SELECT node.title FROM Node node WHERE node.id = :id

Cheers

Nik

Answer 1

While it isn't possible to write the recursive query you're asking for, it is possible to eager fetch the hierarchy with HQL; doing this would at least allow you to walk the tree in memory without hitting the database for each level.

select n from Node n
left join fetch n.Children

Answer 2

You can't do recursive queries with HQL. See this. And as stated there it is not even standard SQL. You have two options:

• write a vendor-specific recursive native SQL query

• make multiple queries. For example:

  // obtain the first node using your query
  while (currentNode.parent != null) {
     Query q = //create the query
     q.setParameter("id", currentNode.getParentId());
     Node currentNode = (Node) q.getSingleResult();
     nodes.add(currentNode); // this is the Set
  }
  

I'd definitely go for the 2nd option.