Hibernate ORM 6.2 - CTE支持 - 相关内容

Hibernate ORM版本6.2.0.Final即将发布，以下文章试图介绍该版本引入的新特性之一。

With子句

with子句在SQL:1999中被引入，允许指定公共表表达式（CTE），可以想象成命名的子查询。每个非关联子查询都可以在with子句中分解为CTE。语义是等效的。

with子句提供了一些超出命名子查询的功能

指定材料化提示
递归查询

CTE最重要的部分是支持递归查询，这允许查询数据层次或图。

Hibernate选择支持与SQL标准相同的语法，尽管存在一些差异

不需要recursive关键字 - Hibernate会推断出该信息
指定cte属性名必须通过选择项别名来完成。CTE标题中的名称是不允许的

queryExpression
: withClause? orderedQuery (setOperator orderedQuery)*
;

withClause
: "WITH" cte ("," cte)*
;

cte
: identifier AS ("NOT"? "MATERIALIZED")? "(" queryExpression ")" searchClause? cycleClause?
;

材料化提示

材料化提示MATERIALIZED或NOT MATERIALIZED可以应用于告诉数据库管理系统是否应该或不应将CTE材料化。请参考相应数据库的数据库手册以获取提示的确切含义。

通常，可以预期MATERIALIZED会导致子查询独立执行并保存到临时表中，而NOT MATERIALIZED将导致子查询内联到每个使用点并在优化期间单独考虑。

with data as materialized(
    select p.person as owner, c.payment is not null as payed
    from Call c
    join c.phone p
    where p.number = :phoneNumber
)
select d.owner, d.payed
from data d

递归查询

with子句的主要用途是为子查询定义一个名称，这样这个子查询就可以引用自己，这最终实现了递归查询。

递归CTE必须遵循一个非常特定的形状，即

基本查询部分
union或union all
递归查询部分

with paymentConnectedPersons as(
    -- Base query part
    select a.owner owner
    from Account a where a.id = :startId

    -- union or union all
    union all

    -- Recursive query part
    select a2.owner owner
    from paymentConnectedPersons d
    join Account a on a.owner = d.owner
    join a.payments p
    join Account a2 on a2.owner = p.person
)
select d.owner
from paymentConnectedPersons d

基本查询部分代表初始行集。在获取数据树时，基本查询部分通常是树根。

递归查询部分会反复执行，直到不再产生新行。此类公共表表达式（CTE）的结果是将基本查询部分结果与所有递归查询部分执行的结果合并。根据使用的是union all还是union(distinct)，重复行会被保留或不保留。

递归查询还可以有

一个search子句，提示数据库管理系统使用广度优先还是深度优先搜索
一个cycle子句，提示数据库管理系统如何确定达到循环

定义search子句需要指定一个在set子句中添加的属性名称，这将添加到CTE类型中，并允许根据搜索顺序排序结果。

searchClause
: "SEARCH" ("BREADTH"|"DEPTH") "FIRST BY" searchSpecifications "SET" identifier
;

searchSpecifications
: searchSpecification ("," searchSpecification)*
;

searchSpecification
: identifier sortDirection? nullsPrecedence?
;

数据库管理系统在执行递归查询部分时有两个可能的排序方式

深度优先 - 首先处理递归查询部分生成的最新行
广度优先 - 首先处理递归查询部分生成的最旧行

with paymentConnectedPersons as(
    select a.owner owner
    from Account a where a.id = :startId

    union all

    select a2.owner owner
    from paymentConnectedPersons d
    join Account a on a.owner = d.owner
    join a.payments p
    join Account a2 on a2.owner = p.person
) search breadth first by owner set orderAttr
select d.owner
from paymentConnectedPersons d

递归处理可能导致循环，这可能会导致查询无限期执行。cycle子句提示数据库管理系统跟踪哪些CTE属性用于循环检测。它需要指定一个在set子句中添加的循环标记属性名称，这将添加到CTE类型中，并允许检测结果是否发生循环。

默认情况下，当检测到循环时，循环标记属性将设置为true，否则为false。可以使用to和default子句显式指定要使用的值。可选地，也可以通过using子句指定循环路径属性名称。循环路径属性可以用于理解导致结果的遍历路径。

cycleClause
        : "CYCLE" cteAttributes "SET" identifier ("TO" literal "DEFAULT" literal)? ("USING" identifier)?
        ;

with paymentConnectedPersons as(
    select a.owner owner
    from Account a where a.id = :startId

    union all

    select a2.owner owner
    from paymentConnectedPersons d
    join Account a on a.owner = d.owner
    join a.payments p
    join Account a2 on a2.owner = p.person
) cycle owner set cycleMark
select d.owner, d.cycleMark
from paymentConnectedPersons d

Hibernate仅翻译递归CTE，但并不尝试模拟该特性。因此，此功能仅当数据库支持递归CTE时才能正常工作。尽管如此，Hibernate如果需要会模拟search和cycle子句，所以您可以安全地使用它们。

请注意，大多数现代数据库版本已经支持递归CTE。

展望

正如从问题编号HHH-4030中可以看出，这是一个期待已久的特性，因此Hibernate最终支持with子句真是太好了。

您可能认为我们已经完成了这项工作，但递归查询还有一个很好的补充功能。我们目前正在讨论支持递归连接获取特性，这将使获取数据树变得更加简单。如果您有需求，请告知我们并加入讨论！

相关内容