HQL recursion, how do I do this?

niklassaers picture niklassaers · Mar 21, 2010 · Viewed 14.7k times · Source

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

Bozho picture Bozho · Mar 21, 2010

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.