JPA query for getting the whole tree

Question

I have a class which models all categories and they can be ordered hierarchically.

@Entity
@Table(name="categories")
public class Category {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequence")
    @SequenceGenerator(name="sequence", sequenceName="categories_pk_seq", allocationSize=1)
    @Column(name="id")
    private Long id;

    @Column
    private String name;

    @OneToOne
    @JoinColumn(name="idfather")
    private Category father;

}

I need to get all categories ordered hierarchically (I mean every father followed by its children and fathers ordered alphabetically on each level) as they could be made for example with PRIOR in oracle. Is it possible to do this with a JPA Query (not a SQL one)?

Thanks.

In your case I suppose you need parent-to-children relation. So something like `@OneToMany @JoinColumn(name="idfather") @OrderBy(value="name") private List children` will help you. And then fetching a root category will load the whole tree. — dma_k, Apr 08 '10 at 09:07
@dma_k I had already had the same Idea and after knowing it was not passible the JPA query I was doing in this way thanks — Javi, Apr 08 '10 at 09:42
Can you please share with us also the Java code, that actually loads the tree? I wonder how you construct/execute JPA query. Also I think that if persistence layer loads tree by executing a query for a tree node, it should know how to fetch children. I wonder, how Oracle's `CONNECT BY PRIOR` extension can help you here. — dma_k, Apr 08 '10 at 22:35
@dma_k The sentence it's a very simple one, I just search for all the categories which have father = null and ordered by name. I also have the List children annotated with @OrderBy("name") so when I do getChildren() I get the list of the children of this node ordered. The CONNECTED WITH PRIOR was for getting the whole list ordered without having to iterate in java, thouhg I think that using this List is a cleaner way of doing this. — Javi, Apr 09 '10 at 17:32

score 10 · Accepted Answer · edited Apr 08 '10 at 12:00

10

The short answer is; no there isn't a standard way to do this.

You have to use native sql.

You may be able to extend the Oracle Hibernate Dialect and add some user function/extension to get hibernate to generate PRIOR or CONNECT BY clauses, but this will prevent your app from being strict JPA and database independent.

edited Apr 08 '10 at 12:00

Péter Török

114,404
31
268
329

answered Apr 08 '10 at 08:55

Gareth Davis

27,701
12
73
106

I extended Oracle Hibernate Dialect, but don't know how to add my functions to it. could you introduce me a sample? – Rasool Ghafari Feb 08 '15 at 08:23
1

@RasoolGhafari sorry I can't. It's been so long since I messed with Hibernate dialects that I've more or less forgotten everything that I knew. But you might find http://stackoverflow.com/questions/12346845/registering-a-sql-function-with-jpa-hibernate a good starting point. – Gareth Davis Feb 08 '15 at 10:36

score 2 · Answer 2 · answered Mar 03 '11 at 16:22

First of all, assuming in this hierarchy a "father" can have more than one child, then the father field should be annotated as @ManyToOne.

If you have a field that all the members of a tree share, or if the tree contains the entire table, then it is possible to do it with JPA in an efficient way, though not through a single JPA query.

You simply need to prefetch all the members of the tree, and then traverse the tree:

@Entity
@Table(name="categories")
public class Category {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequence")
    @SequenceGenerator(name="sequence", sequenceName="categories_pk_seq", allocationSize=1)
    @Column(name="id")
    private Long id;

    @Column
    private String name;

    @ManyToOne
    @JoinColumn(name="idfather")
    private Category father;

    @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, 
               fetch = FetchType.LAZY, 
               mappedBy = "idfather")
    @OrderBy("name")
    private List<Category> subCategories;
}

Notice the @OrderedBy annotation on the subCategories field.

Now you can get the entire tree by first loading all the categories into a jumbled list, just so that they'd all be in memory, and then traverse the tree.

public List<Category> getTree() {
    List<Category> jumbled = 
        entityManager.createQuery("from Category", Category.class).getResultList();

    Category root = null;
    for(Category category : jumbled) {
        if(category.getFather() == null) {
            root = category;
            break;
        }
    }

    List<Category> ordered = new ArratList<Category>();
    ordered.add(root);
    getTreeInner(root, ordered);
}

private void getTreeInner(Category father, List<Category> ordered) {
    for(Category child : father.getSubCategories()) {
        ordered.add(child);
        getTreeInner(child, ordered);
    }
}

I'm only learning JPA myself right now, so I may be missing something crucial, but this approach seems to work for me.

your solution is not going to answer the problem, consider having thousands of categories and the amount of memory it will take to do this, plus we need a solution to use it in other queries and joins!! — azerafati, Jan 02 '15 at 13:48

JPA query for getting the whole tree

2 Answers2

Linked