怎麼用springboot+mybatis plus實作樹形結構查詢

背景

實際開發過程中經常需要查詢節點樹,根據指定節點取得子節點清單,以下記錄了取得節點樹的操作,以備不時之需。

使用情境

可以用於系統部門組織機構、商品分類、城市關係等帶有層級關係的資料結構；

設計想法

#遞迴模型

即根節點、枝幹節點、葉子節點，資料模型如下：

00#10000100001000110001

id	code	name	parent_code
#1	10000		#電腦
	2	20000	手機
	3	10001	聯想筆記本
	4	#10002	惠普筆記本
	5	1000101	聯想拯救者
	6	1000102	聯想小新系列

#實作程式碼

表結構

CREATE TABLE `tree_table` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT "主键ID",
  `code` varchar(10) NOT NULL COMMENT "编码",
  `name` varchar(20) NOT NULL COMMENT "名称",
  `parent_code` varchar(10)  NOT NULL COMMENT "父级编码",
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT="树形结构测试表";

• 表格資料

  INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ("10000", "电脑", "0");
  INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ("10001", "联想笔记本", "10000");
  INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ("10002", "惠普笔记本", "10000");
  INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ("1000101", "联想拯救者", "10001");
  INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ("1000102", "联想小新系列", "10001");

實體

• @Data
  @TableName("tree_table")
  @EqualsAndHashCode(callSuper = false)
  @Accessors(chain = true)
  public class TreeTable {
  
      /**
       * 主键ID
       */
      @TableId(type = IdType.AUTO)
      private Integer id;
      /**
       * 编码
       */
      private String code;
      /**
       * 名称
       */
      private String name;
      /**
       * 父级编码
       */
      private String parentCode;
  
      /**
       * 子节点
       */
      @TableField(exist = false)
      private List<TreeTable> childNode;
  }

  mybatis

• mapper

public interface TreeTableMapper extends BaseMapper<TreeTable> {
    /**
     * 获取树形结构数据
     *
     * @return 树形结构
     */
    public List<TreeTable> noteTree();
}

• xml

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.springboot.example.mysqltree.mapper.TreeTableMapper">
      <resultMap id="BaseResultMap" type="com.springboot.example.mysqltree.model.entity.TreeTable">
          <result column="id" property="id"/>
          <result column="code" property="code"/>
          <result column="name" property="name"/>
          <result column="parent_code" property="parentCode"/>
      </resultMap>
      <resultMap id="NodeTreeResult" type="com.springboot.example.mysqltree.model.entity.TreeTable"
                 extends="BaseResultMap">
          <collection property="childNode" column="code" ofType="com.springboot.example.mysqltree.model.entity.TreeTable"
                      javaType="java.util.ArrayList" select="nextNoteTree">
  
          </collection>
      </resultMap>
  
      <sql id="Base_Column_List">
                  id,
                  code,
                  `name`,
                  parent_code
      </sql>
     
      <select id="nextNoteTree" resultMap="NodeTreeResult">
          select
          <include refid="Base_Column_List"/>
          from tree_table
          where parent_code=#[code]
      </select>
      <select id="noteTree" resultMap="NodeTreeResult">
          select
          <include refid="Base_Column_List"/>
          from tree_table
          where parent_code="0"
      </select>
  </mapper>

noteTree ：取得所有父級節點資料;

#nextNoteTree：循環取得子節點數據，知道葉子節點結束；

#column：關聯表的列名；

##### #ofType：傳回類型############啟動類別######

@Slf4j
@Component
public class TreeTableCommandLineRunner implements CommandLineRunner {
    @Resource
    private TreeTableMapper treeTableMapper;

    @Override
    public void run(String... args) throws Exception {
        log.info(JSONUtil.toJsonPrettyStr(treeTableMapper.noteTree()));
    }
}

####最終效果######

[
    {
        "code": "10000",
        "childNode": [
            {
                "code": "10001",
                "childNode": [
                    {
                        "code": "1000101",
                        "childNode": [
                        ],
                        "parentCode": "10001",
                        "name": "联想拯救者",
                        "id": 5
                    },
                    {
                        "code": "1000102",
                        "childNode": [
                        ],
                        "parentCode": "10001",
                        "name": "联想小新系列",
                        "id": 6
                    }
                ],
                "parentCode": "10000",
                "name": "联想笔记本",
                "id": 3
            },
            {
                "code": "10002",
                "childNode": [
                ],
                "parentCode": "10000",
                "name": "惠普笔记本",
                "id": 4
            }
        ],
        "parentCode": "0",
        "name": "电脑",
        "id": 1
    }
]

###注意事項####### ##使用mybatis時如載入不到mapper xml需在pom.xml新增以下設定：###

<resources>
    <resource>
        <directory>src/main/resources</directory>
        <filtering>true</filtering>
    </resource>
    <resource>
        <directory>src/main/java</directory>
        <includes>
            <include>**/*.xml</include>
        </includes>
    </resource>
</resources>

以上是怎麼用springboot+mybatis plus實作樹形結構查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！