SpringBoot+Mybatis实现父子节点递归查询，细到极致

大家晚上好，我是专业过硬，从未翻车的Jessica老哥。最近有个需求，需要实现一个类似父子节点查询的功能，我找了找网上的文章，很难找到一篇实用的父子查询。
  所以，老哥站出来，用最简单的方式，实现一次父子节点的递归查询。
  话不多说，开始编码
1、常规操作，导入依赖，编写配置
有些年轻人，看我写这些简单的，觉得没意思，老哥认为你千万不要这样想，简单的事情重复做，是非常有用的，这些会让你以后得心应手做这些基础的工作。

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-autoconfigure</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        
        <dependency>
          <groupId>cn.hutool</groupId>
          <artifactId>hutool-all</artifactId>
          <version>5.6.5</version>
       </dependency>
        

spring:
  web:
    resources:
      static-locations: classpath:/META-INF/resources/, classpath:/static/,classpath:/templates/
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
    username: root
    password: root

#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.dowhere.pojo

2、生成表
DROP TABLE IF EXISTS tree_table;
CREATE TABLE tree_table (
id int(0) NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
code varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘编码’,
name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘名称’,
parent_code varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘父级编码’,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘树形结构测试表’ ROW_FORMAT = Dynamic;

INSERT INTO tree_table VALUES (1, ‘10000’, ‘电脑’, ‘0’);
INSERT INTO tree_table VALUES (2, ‘10001’, ‘华为笔记本’, ‘10000’);
INSERT INTO tree_table VALUES (3, ‘10002’, ‘惠普笔记本’, ‘10000’);
INSERT INTO tree_table VALUES (4, ‘1000101’, ‘标准旗舰系列’, ‘10001’);
INSERT INTO tree_table VALUES (5, ‘1000102’, ‘高端商务系列’, ‘10001’);

3、实体类

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("tree_table")
public class TreeTable implements Serializable {

    /**
     * 主键ID
     */
    @TableId(type = IdType.AUTO)
    private Integer id;

    /**
     * 编码
     */
    private String code;

    /**
     * 名称
     */
    private String name;

    /**
     * 父级编码
     */
    private String parentCode;

    /**
     * 子节点
     */
    @TableField(exist = false)
    private List<TreeTable> childNode;
}

3A、mapper接口

@Mapper
public interface TreeTableMapper extends BaseMapper<TreeTable> {
    List<TreeTable> noteTree();
}

5、在resources文件夹下，新建一个mapper包，然后写个 TreeTableMapper.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.dowhere.mapper.TreeTableMapper">

    <resultMap id="BaseResultMap" type="com.dowhere.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.dowhere.entity.TreeTable" extends="BaseResultMap">
<!--        ofType：返回类型-->
        <collection property="childNode" column="code" ofType="com.dowhere.entity.TreeTable"
                    javaType="java.util.ArrayList" select="nextNoteTree">
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        id,
        code,
        name,
        parent_code
    </sql>

<!--    nextNoteTree：循环获取子节点数据，知道叶子节点结束；-->
    <select id="nextNoteTree" resultMap="NodeTreeResult">
        select
        <include refid="Base_Column_List"/>
        from tree_table
        where parent_code=#{code}
    </select>

<!--    noteTree ：获取所有父级节点数据-->
    <select id="noteTree" resultMap="NodeTreeResult">
        select
        <include refid="Base_Column_List"/>
        from tree_table
        where parent_code='0'
    </select>

</mapper>

6、新建一个测试类 ApplicationTests

@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTests {
    @Autowired
    private TreeTableMapper treeTableMapper;
 
   @Test
    public void testTreeNode(){
        List<TreeTable> treeTables = treeTableMapper.noteTree();
        String s = JSONUtil.toJsonPrettyStr(treeTables);
        System.out.println(s);
    }
    
}

7、启动单元测试，成功打印

8、写到这里，就默默的说一句，屏幕前的各位大帅逼，还有大漂亮，看到这里，麻烦给老哥一个点赞、关注、收藏三连好吗，你的支持是老哥更新最大的动力，谢谢！