递归查询

有一种常见的需求: 自关联。

从数据库角度讲，自关联表示一张表的外键引用其自身；从对象模型角度讲，自关联表示一颗树。

自关联的难点在于，对象深度无法控制，理论上讲，可以无限深。对此，jimmer-sql提供了良好的的支持。

模型和数据准备

定义实体接口

Java
Kotlin

@Entity
public interface TreeNode {

    @Id
    @Column(name = "NODE_ID")
    long id();

    String name();

    @Null
    @ManyToOne
    TreeNode parent();

    @OneToMany(mappedBy = "parent")
    List<TreeNode> childNodes();
}

@Entity
interface TreeNode {

    @Id
    @Column(name = "NODE_ID")
    val id: Long

    val name: String

    @ManyToOne
    val parent: TreeNode?

    @OneToMany(mappedBy = "parent")
    val childNodes: List<TreeNode>
}

准备数据库

create table tree_node(
    node_id bigint not null,
    name varchar(20) not null,
    parent_id bigint
);
alter table tree_node
    add constraint pk_tree_node
        primary key(node_id);
alter table tree_node
    add constraint uq_tree_node
        unique(parent_id, name);
alter table tree_node
    add constraint fk_tree_node__parent
        foreign key(parent_id)
            references tree_node(node_id);

insert into tree_node(
    node_id, name, parent_id
) values
    (1, 'Home', null),
        (2, 'Food', 1),
            (3, 'Drinks', 2),
                (4, 'Coca Cola', 3),
                (5, 'Fanta', 3),
            (6, 'Bread', 2),
                (7, 'Baguette', 6),
                (8, 'Ciabatta', 6),
        (9, 'Clothing', 1),
            (10, 'Woman', 9),
                (11, 'Casual wear', 10),
                    (12, 'Dress', 11),
                    (13, 'Miniskirt', 11),
                    (14, 'Jeans', 11),
                (15, 'Formal wear', 10),
                    (16, 'Suit', 15),
                    (17, 'Shirt', 15),
            (18, 'Man', 9),
                (19, 'Casual wear', 18),
                    (20, 'Jacket', 19),
                    (21, 'Jeans', 19),
                (22, 'Formal wear', 18),
                    (23, 'Suit', 22),
                    (24, 'Shirt', 22)
;

无限递归

自顶向下，抓取无穷层

Java
Kotlin

TreeNodeTable table = Tables.TREE_NODE_TABLE;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)
    .where(table.parentId().isNull())
    .select(
        table.fetch(
            Fetchers.TREE_NODE_FETCHER
                .name()
                .recursiveChildNodes()
        )
    )
    .execute();

val treeNodes = sqlClient
    .createQuery(TreeNode::class) {
        where(table.parentId.isNull())
        select(
            table.fetchBy {
                allScalarFields()
                `childNodes*`()
            }
        )
    }
    .execute()

信息

这里，Java代码中的recursiveChildNodes和kotlin代码中的childNodes*表示递归查询。

Jimmer会自动发现实体中的自关联属性，并在编译时生成这种方法以供用户调用。
递归查询无需指定关联对象格式，因为递归查询非常特殊，关联对象格式就是当前对象格式本身。

生成6条SQL

查询根节点

select
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID is null

抓取第1层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?)

抓取第2层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?)

抓取第3层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?)

抓取第4层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?)

抓取第5层 (这个步骤查询不到任何数据，递归结束)

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?)

打印的结果如下（原输出是紧凑的，为了方便阅读，这里进行了格式化）:

{
    "id": 1,
    "name": "Home",
    "childNodes": [
        {
            "id": 9,
            "name": "Clothing",
            "childNodes": [
                {
                    "id": 18,
                    "name": "Man",
                    "childNodes": [
                        {
                            "id": 19,
                            "name": "Casual wear",
                            "childNodes": [
                                {
                                    "id": 20,
                                    "name": "Jacket",
                                    "childNodes": []
                                },
                                {
                                    "id": 21,
                                    "name": "Jeans",
                                    "childNodes": []
                                }
                            ]
                        },
                        {
                            "id": 22,
                            "name": "Formal wear",
                            "childNodes": [
                                {
                                    "id": 24,
                                    "name": "Shirt",
                                    "childNodes": []
                                },
                                {
                                    "id": 23,
                                    "name": "Suit",
                                    "childNodes": []
                                }
                            ]
                        }
                    ]
                },
                {
                    "id": 10,
                    "name": "Woman",
                    "childNodes": [
                        {
                            "id": 11,
                            "name": "Casual wear",
                            "childNodes": [
                                {
                                    "id": 12,
                                    "name": "Dress",
                                    "childNodes": []
                                },
                                {
                                    "id": 14,
                                    "name": "Jeans",
                                    "childNodes": []
                                },
                                {
                                    "id": 13,
                                    "name": "Miniskirt",
                                    "childNodes": []
                                }
                            ]
                        },
                        {
                            "id": 15,
                            "name": "Formal wear",
                            "childNodes": [
                                {
                                    "id": 17,
                                    "name": "Shirt",
                                    "childNodes": []
                                },
                                {
                                    "id": 16,
                                    "name": "Suit",
                                    "childNodes": []
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        {
            "id": 2,
            "name": "Food",
            "childNodes": [
                {
                    "id": 6,
                    "name": "Bread",
                    "childNodes": [
                        {
                            "id": 7,
                            "name": "Baguette",
                            "childNodes": []
                        },
                        {
                            "id": 8,
                            "name": "Ciabatta",
                            "childNodes": []
                        }
                    ]
                },
                {
                    "id": 3,
                    "name": "Drinks",
                    "childNodes": [
                        {
                            "id": 4,
                            "name": "Coca Cola",
                            "childNodes": []
                        },
                        {
                            "id": 5,
                            "name": "Fanta",
                            "childNodes": []
                        }
                    ]
                }
            ]
        }
    ]
}

有限深度

自顶向下，抓取两层

Java
Kotlin

TreeNodeTable table = Tables.TREE_NODE_TABLE;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)
    .where(table.parentId().isNull())
    .select(
        table.fetch(
            Fetchers.TREE_NODE_FETCHER
                .name()
                .recursiveChildNodes(
                    it -> it.depth(2)
                )
        )
    )
    .execute();

val treeNodes = sqlClient
    .createQuery(TreeNode::class) {
        where(table.parentId.isNull())
        select(
            table.fetchBy {
                allScalarFields()
                `childNodes*`{
                    depth(2)
                }
            }
        )
    }
    .execute()

生成3条SQL

主查询获取根节点（第0层）

select
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID is null

抓取第1层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?)

抓取第2层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?)

打印的结果如下（原输出是紧凑的，为了方便阅读，这里进行了格式化）:

{
    "id":1,
    "name":"Home",
    "childNodes":[
        {
            "id":9,
            "name":"Clothing",
            "childNodes":[
                {"id":18,"name":"Man"},
                {"id":10,"name":"Woman"} 
            ]
        },{
            "id":2,
            "name":"Food",
            "childNodes":[
                {"id":6,"name":"Bread"},   
                {"id":3,"name":"Drinks"}
            ]
        }
    ]
}

信息

注意，被标记的4个对象并非表现为childNodes属性为[]，而是缺少childNodes属性。

这表示根本不知道是否有更深的子节点，因为递归过程被人为提前终止。

控制每个节点是否递归

自顶向下，抓取无穷层。但是，对名称为“Clothing”的节点，放弃递归

Java
Kotlin

TreeNodeTable table = Tables.TREE_NODE_TABLE;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)
    .where(table.parentId().isNull())
    .select(
        table.fetch(
            Fetchers.TREE_NODE_FETCHER
            .name()
            .childNodes(
                Fetchers.TREE_NODE_FETCHER.name(),
                it -> it.recursive(args ->
                    !args.getEntity().name().equals("Clothing")
                )
            )
        )
    )
    .execute();

val treeNodes = sqlClient
    .createQuery(TreeNode::class) {
        where(table.parentId.isNull())
        select(
            table.fetchBy {
                allScalarFields()
                childNodes{
                    recursive {
                        entity.name != "Clothing"
                    }
                } 
            }
        )
    }
    .execute()

上述代码中，it.recursive(args)的参数是一个lambda表达式，其参数args是一个对象，提供两个属性

args.getEntity()：当前节点对象。
args.getDepth()：当前节点深度。对于通过主查询直接得到的节点而言是0，随着递归的深入不断增大。
用户决定的返回值：一个boolean变量，决定当前是否对当前节点进行递归抓取。

上述代码的含义是，除了Clothing节点外，其余节都会被递归抓取。

生成了5条SQL

查询根对象

select
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where tb_1_.PARENT_ID is null

抓取第1层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?)

抓取第2层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
/*
* Home node has two child nodes:
*      "Food" and "Clothing",
*
* However, "Clothing is excluded by user,
* so `in(?)` is used here, not `in(?, ?)`
*/
tb_1_.PARENT_ID in (?)

抓取第3层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?)

抓取第4层

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE as tb_1_
where
tb_1_.PARENT_ID in (?, ?, ?, ?)

打印的结果如下（原输出是紧凑的，为了方便阅读，这里进行了格式化）:

{
    "id":1,
    "name":"Home",
    "childNodes":[
        {"id":9,"name":"Clothing"},
        {
            "id":2,
            "name":"Food",
            "childNodes":[
                {
                    "id":6,
                    "name":"Bread",
                    "childNodes":[
                        {"id":7,"name":"Baguette","childNodes":[]},
                        {"id":8,"name":"Ciabatta","childNodes":[]}
                    ]
                },{
                    "id":3,
                    "name":"Drinks",
                    "childNodes":[
                        {"id":4,"name":"Coca Cola","childNodes":[]},
                        {"id":5,"name":"Fanta","childNodes":[]}
                    ]
                }
            ]
        }
    ]
}

信息

注意，被标记的对象并非表现为childNodes属性为[]，而是缺少childNodes属性。

这表示根本不知道是否有更深的子节点，因为递归过程被人为提前终止。

多属性递归

Jimmer支持对递归查询多个字关联属性，如下

Java
Kotlin

Tree treeNode = sqlClient
    .findById(
        Fetchers.TREE_NODE_FETCHER
                .name()
                .recursiveParent()
                .recursiveChildNodes(),
        10L
    );

val treeNodes = sqlClient
    .findById(
        newFetcher(TreeNode::class) {
            name()
            `parent*`()
            `childNodes*`()
        }
    )

这个例子中，对两个关联属性进行递归查询

parent: 从当前节点出发，不断向上查询父节点，直到查询到根节点为止
childNodes: 从当前节点出发，不断向下查询子节点，直到查询到最深的子节点为止

执行，生成如下6条SQL

查询当前节点

select
    tb_1_.NODE_ID,
    tb_1_.NAME,
    tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
    tb_1_.NODE_ID = ? /* 10 */

向上查询第一层父节点

select
    tb_1_.NODE_ID,
    tb_1_.NAME,
    tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
    tb_1_.NODE_ID = ? /* 9 */

向上查询第二层父节点 (这个步骤会查询到根节点，向上递归结束)

select
    tb_1_.NODE_ID,
    tb_1_.NAME,
    tb_1_.PARENT_ID
from TREE_NODE tb_1_
where
    tb_1_.NODE_ID = ? /* 1 */

向下查询第一层子节点

select
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE tb_1_
where
    tb_1_.PARENT_ID = ? /* 10 */

向下查询第二层子节点

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE tb_1_
where
    tb_1_.PARENT_ID in (
        ? /* 11 */, ? /* 15 */
    )

向下查询第三层子节点 (这个步骤查询不到任何数据，向下递归结束)

select
    tb_1_.PARENT_ID,
    tb_1_.NODE_ID,
    tb_1_.NAME
from TREE_NODE tb_1_
where
    tb_1_.PARENT_ID in (
        ? /* 12 */, ? /* 13 */, ? /* 14 */, ? /* 16 */, ? /* 17 */
    )

最终，查询结果为

{
    "id":10,
    "name":"Woman",
    "parent":{ // 向上递归
        "id":9,
        "name":"Clothing",
        "parent":{
            "id":1,
            "name":"Home",
            "parent":null
        }
    },
    "childNodes":[ // 向下递归
        {
            "id":11,
            "name":"Casual wear",
            "childNodes":[
                {
                    "id":12,
                    "name":"Dress",
                    "childNodes":[]
                },
                {
                    "id":13,
                    "name":"Miniskirt",
                    "childNodes":[]
                },
                {
                    "id":14,
                    "name":"Jeans",
                    "childNodes":[]
                }
            ]
        },
        {
            "id":15,
            "name":"Formal wear",
            "childNodes":[
                {
                    "id":16,
                    "name":"Suit",
                    "childNodes":[]
                },
                {
                    "id":17,
                    "name":"Shirt",
                    "childNodes":[]
                }
            ]
        }
    ]
}

这个例子正确运行了，不难看出，parent方向的递归路径和childNodes方向的递归路径是完全独立的，向上递归和向下递归不会混合和交替。

因此，我们可以稍微修正一下前面讨论过的为什么递归查询无需指定关联对象格式：

信息

关联对象格式 = 当前对象格式中所有没有被递归查询的属性属性 (无论是否为关联属性) + 当前被递归查询的属性 (排除其他被递归查询的属性)

模型和数据准备​

无限递归​

有限深度​

控制每个节点是否递归​

多属性递归​

模型和数据准备

无限递归

有限深度

控制每个节点是否递归

多属性递归