springboot+mybatis plus Implementation of Tree Structure Query

Directory background
Usage scenario
Design thought
Recursive model
Implementation code
Matters needing attention
Summarize

Background

In the actual development process, it is often necessary to query the node tree and obtain the list of child nodes according to the specified nodes. The following records the operation of obtaining the node tree in case of emergency.

Usage scenario

It can be used for data structures with hierarchical relations such as system department organization, commodity classification and city relationship;

Design thought

Recursive model

That is, root node, branch node and leaf node. The data model is as follows:

id	code	name	parent_code
1	10000	电脑	0
2	20000	手机	0
3	10001	联想笔记本	10000
4	10002	惠普笔记本	10000
5	1000101	联想拯救者	10001
6	1000102	联想小新系列	10001

Implementation code

Table structure

CREATE TABLE `tree_table` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT ' Primary key ID',
  `code` varchar(10) NOT NULL COMMENT ' Code ',
  `name` varchar(20) NOT NULL COMMENT ' Name ',
  `parent_code` varchar(10)  NOT NULL COMMENT ' Parent code ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=' Tree structure test table ';

Table data

INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ('10000', ' Computer ', '0');
INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ('10001', ' Lenovo Notebook ', '10000');
INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ('10002', ' Hewlett-Packard Notebook ', '10000');
INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ('1000101', ' Association Saver ', '10001');
INSERT INTO `tree_table`(`code`, `name`, `parent_code`) VALUES ('1000102', ' Lenovo Xiaoxin series ', '10001');

Entity

@Data
@TableName("tree_table")
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class TreeTable {
​
    /**
     *  Primary key ID
     */
    @TableId(type = IdType.AUTO)
    private Integer id;
    /**
     *  Code 
     */
    private String code;
    /**
     *  Name 
     */
    private String name;
    /**
     *  Parent code 
     */
    private String parentCode;
​
    /**
     *  Child node 
     */
    @TableField(exist = false)
    private List<TreeTable> childNode;
}

mybatis

mapper

public interface TreeTableMapper extends BaseMapper<TreeTable> {
    /**
     *  Get tree structure data 
     *
     * @return  Tree structure 
     */
    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: Get all parent node data; nextNoteTree: Loop to obtain child node data until the leaf node ends; column: The column name of the associated table; ofType: Return type

Startup class

@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()));
    }
}

Final effect

[
    {
        "code": "10000",
        "childNode": [
            {
                "code": "10001",
                "childNode": [
                    {
                        "code": "1000101",
                        "childNode": [
                        ],
                        "parentCode": "10001",
                        "name": " Association Saver ",
                        "id": 5
                    },
                    {
                        "code": "1000102",
                        "childNode": [
                        ],
                        "parentCode": "10001",
                        "name": " Lenovo Xiaoxin series ",
                        "id": 6
                    }
                ],
                "parentCode": "10000",
                "name": " Lenovo Notebook ",
                "id": 3
            },
            {
                "code": "10002",
                "childNode": [
                ],
                "parentCode": "10000",
                "name": " Hewlett-Packard Notebook ",
                "id": 4
            }
        ],
        "parentCode": "0",
        "name": " Computer ",
        "id": 1
    }
]

Matters needing attention

If mapper xml cannot be loaded when using mybatis, the following configuration should be added to 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>

Summarize

Using recursive method is a common way, which has the advantage of simple and intuitive embodiment of hierarchical relationship, but the efficiency will be slightly lower when the amount of data is large; Welcome small partners who use other methods to share their implementation ideas.