Springboot + MyBatis plus + mysql tree query

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 node. The operations of obtaining the node tree are recorded as follows in case of emergency.

Usage scenarios

It can be used in the data structure with hierarchical relationship, such as department organization, commodity classification, city relationship, etc.

Design ideas

Recursive model

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

The implementation code

Table structure

CREATE TABLE 'tree_table' (' id 'int NOT NULL AUTO_INCREMENT COMMENT' primary key ID', 'code' VARCHar (10) NOT NULL COMMENT 'primary key ID', '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 test table ';Copy the code

Table data

INSERT INTO 'tree_table' (' code ', 'parent_code ') VALUES ('10000',' PC ', '0'); INSERT INTO 'tree_table' (' code ', 'parent_code ') VALUES ('10001',' lenovo ', '10000'); INSERT INTO 'tree_table' VALUES (' name ', 'parent_code '); INSERT INTO' tree_table 'VALUES (' name ',' parent_code '); INSERT INTO 'tree_table' VALUES ('1000101', 'name ',' parent_code '); INSERT INTO 'tree_table' VALUES ('1000102', 'name ',' parent_code ');Copy the code

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; /** * name */ private String name; /** * parentCode */ private String parentCode; /** * childNode */ @tablefield (exist = false) private List<TreeTable> childNode; }Copy the code

mybatis

mapper

Public interface TreeTableMapper extends BaseMapper<TreeTable> {/** * get tree data ** @return tree data */ public List<TreeTable> noteTree(); }Copy the code

xml

<? The 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>Copy the code

NoteTree: Obtain data of all parent nodes;

NextNoteTree: Loop through child node data until the end of the leaf node;

Column: specifies the column name of the associated table.

OfType: return type

Start the 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())); }}Copy the code

The final result

[ { "code": "10000", "childNode": [ { "code": "10001", "childNode": [ { "code": "1000101", "childNode": [], "parentCode" : "10001", "name" : "lenovo savior", "id" : 5}, {" code ":" 1000102 ", "childNode" : [], "parentCode" : "10001", "name" : "small new lenovo series", "id" : 6}], "parentCode" : "10000", "name" : "lenovo notebook", "id", 3}, {" code ": "10002", "childNode" : [], "parentCode" : "10000", "name" : "HP notebook", "id" : 4}], "parentCode" : "0", "name" : "computer", "id" : 1}]Copy the code

Matters needing attention

When using Mybatis, if mapper XML cannot be loaded, add the following configuration 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>
Copy the code

conclusion

Recursion is a common method, which has the advantages of simple implementation and intuitive representation of the hierarchical relationship. However, the efficiency will be slightly lower when the amount of data is large. Others are welcome to share their ideas.