动态

详情 返回 返回

Jimmer VS MyBatisPlus查詢自關聯表 - 动态 详情

首發於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 , orm , JAVA
user avatar u_16502039 头像 u_13529088 头像 debuginn 头像 seazhan 头像 u_11365552 头像 boxuegu 头像 chaochenyinshi 头像 chengxy 头像 dengjijie 头像 k8scat 头像 pottercoding 头像 ruozxby 头像
点赞 23 用户, 点赞了这篇动态!
点赞

Add a new 评论

Some HTML is okay.