sql - Recursion On Database Query to get hierarchical result using Hibernate - Java

Question

I have a table in my Oracle database with child parent relationship like -

What I need is to access the list of child in hierarchical manner in Hibernate.

• When Father logs in - he gets Son as child.
• When Grandfather logs in - he gets Son, Father, Uncle.
• When Super Grandfather logs in - he gets Son, Father, Uncle and Grandfather.

I have a java entity for same as well.

public class relations {
    private String child;
    private String parent;
    public getChild();
    public getParent();
    public setChild();
    public setParent();
}

How to run a recursion over this?

Should I be doing it by writing a named query in SQL for getting the list or it can be implemented in java hibernate?

What I am looking for is to write a recursive code in java. Thanks in advance.

Answer 1

Don't do the recursive lookup in Java. That won't scale because you will be sending lots of queries to the database. Use a (single) recursive query directly on the database that will perform and scale much better.

You didn't specify your DBMS but recursive queries are supported by all modern databases. The following is standard ANSI SQL:

with recursive ancestry as (
   select child, parent, 1 as level
   from users
   where parent = 'Grandfather' -- this is the one who logs in
   union all
   select c.child, c.parent, p.level + 1
   from users c
     join ancestry p on p.child = c.parent
)
select child, level
from ancestry
order by level desc;

Example: http://rextester.com/TJGTJ95905

---

Edit after the real database was disclosed.

In Oracle you have two ways of doing that.

The "traditional" way is to use connect by which is a much more compact form of a recursive query then what the SQL standard came up with:

select child, level
from users
start with parent = 'Grandfather'
connect by prior child = parent
order by level desc;

You could use a common table expression in Oracle as well. However even though the SQL standard requires the keyword recursive to be mandatory, Oracle chose to ignore that part of the standard, so you have to remove it. LEVEL is a pseudo-column in Oracle that can only be used together with connect by so this can't be used in the CTE solution:

with ancestry (child, parent, lvl) as (
   select child, parent, 1 as lvl
   from users
   where parent = 'Grandfather'
   union all
   select c.child, c.parent, p.lvl + 1
   from users c
     join ancestry p on p.child = c.parent
)
select child, lvl
from ancestry
order by lvl desc