5

I have tree structures that are stored in a DB table. The table can store multiple trees. I need a query that will return all nodes in a single tree. I've used the following sites as resources but the queries in them will load all nodes for all trees into PersistenceContext (isn't that loading the entire table?). I don't want to do that, I only want to load one tree. How do I achieve that??

I am using JPA 2 with Hibernate as the provider.

OpenJPA 1.2.x select tree structure using JPQL
http://www.tikalk.com/java/load-a-tree-with-jpa-and-hibernate#comment-1821

[UPDATE] Based upon a suggestion from @bennidi, I wonder if I can use something like this:

@Entity
public class Node {

    private String name;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "rootId")
    private Node root;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "parentId")
    private Node parent;

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @OrderBy("name")
    private List<Node> children = new LinkedList<Node>();
}

JPQL query:

select distinct n from Node n left join fetch n.children where n.rootId = ROOT_ID

I do have a question though, should I create a second table to maintain the Parent-Child relationships like in this article, or will it be fine if I just used a self-referencing table. Going with the former seems to require a bit more maintenance and probably a little more complex queries. Is it worth it? I'm not sure what problems the article is addressing.

Community
  • 1
  • 1
citress
  • 889
  • 3
  • 13
  • 35

1 Answers1

0

I think it is not natural to store recursive structures in relational databases. Also the answer to your question would depend on the schema you choose to model your nodes and trees. If you have a self referencing node table that holds the parent child relations as foreign key there is no way to select all nodes that are children of a particular node with one query. I think you should have a table for each tree and have each node reference its tree. Then you can simply select all nodes of a particular tree and created the tree structure by analysing the nodes.

Have a look at this post: http://www.codeproject.com/Articles/8355/Trees-in-SQL-databases

bennidi
  • 2,092
  • 21
  • 27
  • Thanks for the article. This gave me an idea, I'm going to update my original question. – citress Apr 12 '12 at 14:29
  • I think the proposed code/mapping looks very promising. Does it work as expected? – bennidi Apr 16 '12 at 10:30
  • So far in my integration tests, it looks like everything is working correctly. – citress Apr 16 '12 at 16:27
  • The only query my structure will not support is querying for subtrees within a tree - however I don't have that requirement in my application. So I'm okay with that limitation. – citress Apr 16 '12 at 16:28
  • Great, I think the solution is quite elegant. For loading sub trees it might be possible to use additional attributes and filter on them. For example if the tree structure grows without rebalancing to frequently u could store the level of each node and then filter by level to avoid loading the whole tree. – bennidi Apr 27 '12 at 20:53
  • "*if you have a self referencing node table that holds the parent child relations as foreign key there is no way to select all nodes that are children of a particular node with one query.*" - that's plain wrong. Lookup recursive queries, e.g. here: http://www.postgresql.org/docs/current/static/queries-with.html –  Aug 29 '14 at 18:40
  • 1
    We are talking about JPQL here, not vendor specific SQL extensions. But thanks for the link, it's an interesting feature – bennidi Sep 02 '14 at 15:27