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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With