springboot+mybatis plus Implementation of Tree Structure Query
- 2021-11-01 03:36:17
- OfStack
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.