关于mybatis-plus:Jimmer-VS-MyBatisPlus查询自关联表

50次阅读

共计 8637 个字符，预计需要花费 22 分钟才能阅读完成。

首发于 Enaium 的集体博客

本文是对 Jimmer 文档中对象抓取器 - 自关联递归抓取局部的介绍, 之后会比照 MyBatisPlus 的查问自关联表的能力。

对象抓取器是 jimmer-sql 一个十分弱小的特色，具备可媲美 GraphQL 的能力。
即便用户不采纳任何 GraphQL 相干的技术栈，也能在 SQL 查问层面失去和 GraphQL 类似的对象图查问能力。

筹备数据库和实体类

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)
;
@Entity
public interface TreeNode {

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

    String name();

    @Null
    @ManyToOne
    TreeNode parent();

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

指定查问的深度

咱们能够看到，这是一个自关联的表，每个节点都有一个父节点，也能够有多个子节点。

应用 Jimmer 的 Fetcher 性能，咱们能够很容易的查问出这个表的所有节点, 并且能够很容易的管制查问的深度, 还有条件查问。

TreeNodeTable node = TreeNodeTable.$;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)// 创立一个查问
    .where(node.parent().isNull())// 查问条件, 这里查问出所有的根节点, 也就是 parent_id 为 null 的节点
    .select(// 查问的字段
        node.fetch(
            TreeNodeFetcher.$
                .name()// 查问节点的名称
                .childNodes(TreeNodeFetcher.$.name(),// 查问子节点的名称
                    it -> it.depth(2)// 查问子节点的深度, 这里查问 2 层
                )
        )
    )
    .execute();

如果你应用Kotlin, 那么你能够这样写

val treeNodes = sqlClient
    .createQuery(TreeNode::class) {where(table.parent.isNull())// 查问条件, 这里查问出所有的根节点, 也就是 parent_id 为 null 的节点
        select(
            table.fetchBy {allScalarFields()// 查问节点的所有字段
                childNodes({depth(2)// 查问子节点的深度, 这里查问 2 层
                }) {allScalarFields()// 查问子节点的所有字段
                }
            }
        )
    }
    .execute()

生成的 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"}
      ]
    }
  ]
}

查问有限层级的树

如果你想查问有限层级的树, 那么你能够这样写

TreeNodeTable node = TreeNodeTable.$;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)
    .where(node.parent().isNull())
    .select(
        node.fetch(
            TreeNodeFetcher.$
                .name()
                .childNodes(TreeNodeFetcher.$.name(),
                    it -> it.recursive()// 查问有限层级的树, 这里不须要指定深度, 也就是把 depth()办法去掉换成 recursive()办法)
        )
    )
    .execute();
val treeNodes = sqlClient
    .createQuery(TreeNode::class) {where(table.parent.isNull())
        select(
            table.fetchBy {allScalarFields()
                childNodes({recursive()
                }) {allScalarFields()
                }
            }
        )
    }
    .execute()

生成的 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 (?, ?)

第 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": [] }
          ]
        }
      ]
    }
  ]
}

每个查问的节点是否递归

如果你想每个查问的节点是否递归, 那么你能够这样写

TreeNodeTable node = TreeNodeTable.$;

List<TreeNode> treeNodes = sqlClient
    .createQuery(node)
    .where(node.parent().isNull())
    .select(
        node.fetch(
            TreeNodeFetcher.$
                .name()
                .childNodes(TreeNodeFetcher.$.name(),
                    it -> it.recursive(args ->
                        !args.getEntity().name().equals("Clothing")// 每个查问的节点是否递归, 这里能够依据实体的属性来判断是否递归
                    )
                )
        )
    )
    .execute();
val treeNodes = sqlClient
    .createQuery(TreeNode::class) {where(table.parent.isNull())
        select(

            table.fetchBy {allScalarFields()
                childNodes({
                    recursive {entity.name != "Clothing"// 每个查问的节点是否递归, 这里能够依据实体的属性来判断是否递归}
                }) {allScalarFields()
                }
            }
        )
    }
    .execute()

这样就能够实现每个查问的节点是否递归了

应用 MybatisPlus 来查问树形构造

定义实体

@Data
@TableName("tree_node")
public class TreeNode {
    @TableId
    private Long nodeId;
    private String name;
    @TableField(exist = false)
    private List<TreeNode> childNodes;
}

定义 Mapper

@Mapper
public interface TreeNodeMapper extends BaseMapper<TreeNode> {}

查问树形构造的 Service

@Service
@AllArgsConstructor
public class TreeNodeService {
    private final TreeNodeMapper treeNodeMapper;

    public List<TreeNode> getTree() {
        // 查问根节点列表
        List<TreeNode> rootNodes = selectRoots();

        // 遍历根节点，递归查问每个节点的子孙节点
        for (TreeNode rootNode : rootNodes) {this.getChildren(rootNode);
        }

        return rootNodes;
    }

    private void getChildren(TreeNode node) {
        // 查问子节点
        List<TreeNode> children = selectByParentId(node.getNodeId());
        // 遍历子节点，递归查问子节点的子孙节点
        for (TreeNode child : children) {this.getChildren(child);
        }
        node.setChildNodes(children);
    }
    public List<TreeNode> selectRoots() {QueryWrapper<TreeNode> wrapper = new QueryWrapper<>();
        wrapper.isNull("parent_id");// 查问根节点，parent_id 为 null
        return treeNodeMapper.selectList(wrapper);
    }

    public List<TreeNode> selectByParentId(Long parentId) {QueryWrapper<TreeNode> wrapper = new QueryWrapper<>();
        wrapper.eq("parent_id", parentId);// 查问子节点，parent_id 为以后节点的 id
        return treeNodeMapper.selectList(wrapper);
    }
}

查问后果

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

查问树形构造的 Service 并指定查问深度

@Service
@AllArgsConstructor
public class TreeNodeService {
    private final TreeNodeMapper treeNodeMapper;

    public List<TreeNode> getTree(int depth) {
        // 查问根节点列表
        List<TreeNode> rootNodes = selectRoots();

        // 遍历根节点，递归查问每个节点的子孙节点
        for (TreeNode rootNode : rootNodes) {this.getChildren(rootNode, depth, 0);
        }

        return rootNodes;
    }

    private void getChildren(TreeNode node, int maxDepth, int currentDepth) {if (currentDepth >= maxDepth) {
            // 以后深度达到最大深度，终止递归并返回后果
            node.setChildNodes(Collections.emptyList());
            return;
        }

        // 查问子节点
        List<TreeNode> children = selectByParentId(node.getNodeId());
        // 遍历子节点，递归查问子节点的子孙节点
        for (TreeNode child : children) {this.getChildren(child, maxDepth, currentDepth + 1);
        }
        node.setChildNodes(children);
    }

    public List<TreeNode> selectRoots() {QueryWrapper<TreeNode> wrapper = new QueryWrapper<>();
        wrapper.isNull("parent_id");
        return treeNodeMapper.selectList(wrapper);
    }

    public List<TreeNode> selectByParentId(Long parentId) {QueryWrapper<TreeNode> wrapper = new QueryWrapper<>();
        wrapper.eq("parent_id", parentId);
        return treeNodeMapper.selectList(wrapper);
    }
}

查问后果

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

查问树形构造的 Service 并指定查问深度和查问条件

不好意思，这个性能我还没想好怎么用 MybatisPlus 实现，所以这里就不写了。

总结

这么一比照，应用 MybatisPlus 的代码量的确多了不少并且很简单, 又是递归又是递归计数等等, 而 Jimmer 应用了 Fetcher 就会更容易的查出所有多层节点, 并且代码量也非常少

正文完
mybatis-plus
发表至： mybatis-plus
2023-05-30
 0
关于mybatis-plus:聊聊mybatisplus的sql加载顺序
关于mybatis-plus:Mybatisplus分页
关于mybatis-plus:MyBatis-进阶MyBatisPlus基于-Springboot-演示
关于mybatis-plus:MybatisX工具快速代码生成