Dynamic JPA CriteriaBuilder for hierarchical data

Question

I have a hierarchical data structure as following Where nodes are mapped to the parent node as following:

@Entity
public class Node implements Serializable { 
    @Id
    private long id;

     @Column(name="PARENT_ID")
     private Long parentId;

    @OneToMany    
    @JoinColumn(name="PARENT_ID")    
    public List<Node> children = new LinkedList<Node>();

}

So for example lets say I have the following data:

          [A]
          / \
         /   \
        /     \
     [B]      [C]
     / \        \
    /   \        \
  [D]   [E]      [F]
          \
           \
           [G]

Now I want to build a dynamic query in JPA CriteriaBuilder that can query for any node and return the results of its children as well. For example if I query for B, I get the following results:

• B
• D
• E
• G

And, if I query for E, I get:

-E -G

And so on...

Answer 1

Since I'm using SQL Server 2012 as my database I have used with clause as following:

Assuming [E] node id is 8:

Top to Bottom:

WITH NODE_TREE AS(
     SELECT N.ID, N.PARENT_ID FROM NODE_TABLE N WHERE N.ID = 8
     UNION ALL
     SELECT N.ID, N.PARENT_ID FROM NODE_TABLE N
     INNER JOIN NODE_TREE NT
     ON N.ID = NT.PARENT_ID
)

SELECT * FROM NODE_TREE;

This will return a top-to-bottom list of nodes:

B, E, D, G

Bottom to Top:

WITH NODE_TREE AS(
     SELECT N.ID, N.PARENT_ID FROM NODE_TABLE N WHERE N.ID = 8
     UNION ALL
     SELECT N.ID, N.PARENT_ID FROM NODE_TABLE N
     INNER JOIN NODE_TREE NT
     ON N.PARENT_ID = NT.ID
)

SELECT * FROM NODE_TREE;

This will return a bottom-to-top list of nodes:

B, A