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
You can't do recursive queries with HQL. See this. And as stated there it is not even standard SQL. You have two options:
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.