背景
实际开发过程中经常需要查询节点树,根据指定节点获取子节点列表,以下记录了获取节点树的操作,以备不时之需。
使用场景
可以用于系统部门组织机构、商品分类、城市关系等带有层级关系的数据结构；
设计思路
递归模型
即根节点、枝干节点、叶子节点，数据模型如下：
idcodenameparent_code
1 10000 电脑 0
2 20000 手机 0
3 10001 联想笔记本 10000
4 10002 惠普笔记本 10000
5 1000101 联想拯救者 10001
6 1000102 联想小新系列 10001
实现代码
表结构
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@componentpublic 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实现树形结构查询的详细内容。