递归在数据库查询中使用Hibernate - Java获得分层结果 [英] Recursion On Database Query to get hierarchical result using Hibernate - Java

问题描述

我在我的Oracle数据库中有一个与孩子父母关系相似的表 -

我需要的是在Hibernate中以层级方式访问子列表。

• 在他把儿子当成小孩的时候。


• 当祖父登录时 - 他得到了儿子，父亲，叔叔。


• 当超级祖父登录时 - 获得儿子，父亲，叔叔和祖父。

我也有一个java实体。

 公共类关系{
 private String child; 
私人字符串父; 
 public getChild（）; 
 public getParent（）; 
 public setChild（）; 
 public setParent（）; 
} 
  

如何对此递归执行？

我应该通过在SQL中编写一个命名查询来获取列表，或者它可以在java hibernate中实现？

我是什么寻找的是在java中编写递归代码。
在此先感谢。

解决方案

不要在Java中执行递归查找。这不会扩展，因为您将向数据库发送大量查询。直接在数据库上使用（单一）递归查询将会执行和扩展更好。

您没有指定DBMS，但所有现代数据库都支持递归查询。以下是标准的ANSI SQL：

 带递归祖先as（
 select child，parent，1 as level 
来自用户
其中parent ='祖父' - 这是登录
的所有人
选择c.child，c.parent，p.level + 1 
从用户c 
在p.child上加入祖先p .child = c.parent 
）
从祖先中选择孩子，等级
 
按等级降序排列; 
  

示例： http://rextester.com/TJGTJ95905

---

修改后真实的数据库被披露。

在Oracle中，您有两种方法可以做到这一点。

传统的方法是使用 connect by ，这是递归查询的一种更紧凑的形式，然后什么是SQL标准：

 选择子级，级别
来自用户
以parent = '祖父'
由以前的孩子=父母
连接按等级desc排序; 
  

You could 也可以在Oracle中使用公用表表达式。但是，即使SQL标准要求关键字递归是强制性的，Oracle选择忽略该部分标准，因此您必须将其删除。 LEVEL 是Oracle中的一个伪列，只能与 connect by 一起使用，因此无法使用在CTE解决方案中：

 带祖先（child，parent，lvl）as（
 select child，parent，1作为lvl 
来自用户
其中parent ='祖父'
 union全部
从用户选择c.child，c.parent，p.lvl + 1 
 c 
加入祖先p.child = c.parent 
）
从祖先选择子女，lvl 
 
按lvl desc 
  

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.

解决方案

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

这篇关于递归在数据库查询中使用Hibernate - Java获得分层结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！