JPA/Hibernate map @OneToMany for Oracle hierarchical data

Question

Say I have parent-child info in table Organization as followed:

id name parent_id
1  A    1
2  A1   1
3  A2   1
4  A11  2

With Oracle, I can get all descendants or ancestors of an organization using "start with/connect by". For example, the following sql will get all the subtree under "A" include itself (i.e. A, A1, A2, A11)

select * from Organization start with id=1 connect by nocycle prior id=parent_id;

Or this sql will get all ancestors of A11 including itself (i.e. A11, A1, A)

with o_hier as (select o.id, o.parent_id, CONNECT_BY_ISCYCLE as lvl from Organization o start with id=4 connect by nocycle prior parent_id = id) select o.* from Organization o, o_hier where o.id = o_hier.id union all select o.* from Organization o, o_hier where o.id = o_hier.parent_id and o_hier.lvl = 1;

Now I want to map this table into OrganizationEntity like this:

@Entity
@Table(name = "Organization")
public class OrganizationEntity {

    //getter/setter omitted for readability

    @Id
    @Column(name = "ID")
    private String id;

    @Column(name = "NAME")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @???
    List<OrganizationEntity> descendants = new ArrayList<>();

    @ManyToOne(fetch = FetchType.LAZY)
    @???
    List<OrganizationEntity> ancestors= new ArrayList<>();
}

I'm aware of possible performance issue, but can we map something like this using Hibernate/JPA?

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

This is a tricky one. You can use standard parent and childern mappings.

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ID")
OrganizationEntity parent;

@OneToMany(fetch = FetchType.LAZY)
@mappedBy(mappedBy="parent")
List<OrganizationEntity> childern;

And then use standard tree taversals algorithms to get all the ancestors (simple while loop) or all the descendants (some DFS variant, usually preorder).

Performace wise this wound be very slow.

Other, and better idea is just do the traversals within the database with CONNECT BY and then map the result set to objects. You can do that with pure JPA calls or Hibernate specific calls.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 14 '15 at 12:05

dimm

1,792
11
15

I'm interested in doing recursive calls in Oracle like your second idea, but can't seem to get JoinColumnsOrFormula and/or JoinFormula, JoinColumn to get it work. Any black-belt Hibernate/JPA around here? – Luan Nguyen Feb 14 '15 at 23:48
@LuanNguyen just use Native SQL Query as described [here (pure JPA)](http://stackoverflow.com/questions/17708946/jpa-native-query-select-and-cast-object) or [here (hibernate specific)](http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/querysql.html). I'll update my answer. – dimm Feb 15 '15 at 04:02
It can be done as queries but I'm interested in a solution using only the mapping. – Luan Nguyen Feb 16 '15 at 03:03

JPA/Hibernate map @OneToMany for Oracle hierarchical data

1 Answers1