怎么使用SpringBoot+MyBatisPlus+MySQL8实现树形结构查询(mybatisplus,mysql,springboot,编程语言)

场景:

实现权限功能模块时，需要将查询的权限数据，以树形结构的方式返回给前端。

功能实现:

第一步:权限表结构定义及其功能演示数据。

DROPTABLEIFEXISTS`baoan_privilege`;CREATETABLE`baoan_privilege`(`id`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT'主键',`privilege_name`varchar(100)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'权限名称',`privilege_code`varchar(100)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'权限编码',`pid`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'父Id',`url`varchar(200)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'菜单路由',`order_rank`int(3)NULLDEFAULTNULLCOMMENT'序号',`privilege_type`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'权限类型1:项目，2菜单，3按钮',`privilege_description`varchar(200)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'权限描述',`state`varchar(10)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULT'2'COMMENT'状态(1:禁用,2:启用)',`created_by`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'创建人',`created_dt`datetime(0)NULLDEFAULTNULLCOMMENT'创建时间',`version`int(9)NULLDEFAULT1COMMENT'版本号',`updated_by`varchar(32)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'更新人',`updated_dt`datetime(0)NULLDEFAULTNULLCOMMENT'更新时间',`icon_name`int(15)NULLDEFAULTNULLCOMMENT'图标名称',`delete_flag`int(1)NULLDEFAULT1COMMENT'删除标识(1:未删除，2：已删除)',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ciCOMMENT='权限表'ROW_FORMAT=Dynamic;--------------------------------Recordsofbaoan_privilege------------------------------INSERTINTO`baoan_privilege`VALUES('1','首页','A','0',NULL,NULL,'1','首页','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('10','通知管理','F_02','6',NULL,NULL,'2','通知管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('11','操作日志','F_03','6',NULL,NULL,'2','操作日志','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('12','角色管理','F_04','6',NULL,NULL,'2','角色管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('13','存储管理','F_05','6',NULL,NULL,'2','存储管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('14','权限管理','F_06','6',NULL,NULL,'2','权限管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('15','新增','F_01_add','9',NULL,NULL,'3','管理员新增','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('16','修改','F_01_update','9',NULL,NULL,'3','管理员修改','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('17','查询','F_01_search','9',NULL,NULL,'3','管理员查询','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('18','删除','F_01_delete','9',NULL,NULL,'3','管理员删除','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('19','导出','F_01_export','9',NULL,NULL,'3','管理员导出','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('2','用户管理','B','0',NULL,NULL,'1','用户管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('3','商场管理','C','0',NULL,NULL,'1','商场管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('4','商品管理','D','0',NULL,NULL,'1','商品管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('5','推广管理','E','0',NULL,NULL,'1','推广管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('6','系统管理','F','0',NULL,NULL,'1','系统管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('7','配置管理','G','0',NULL,NULL,'1','配置管理','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('8','统计报表','H','0',NULL,NULL,'1','统计报表','2',NULL,NULL,1,NULL,NULL,NULL,1);INSERTINTO`baoan_privilege`VALUES('9','管理员','F_01','6',NULL,NULL,'2','管理员','2',NULL,NULL,1,NULL,NULL,NULL,1);

第二步:权限表实体定义及其拓展对象

基本对象

packagecom.zzg.entity;importjava.io.Serializable;importjava.util.Date;importcom.baomidou.mybatisplus.annotation.TableName;importlombok.Data;@SuppressWarnings("serial")@TableName(value="baoan_privilege")@DatapublicclassBaoanPrivilegeimplementsSerializable{privateStringid;privateStringprivilegeName;privateStringprivilegeCode;privateStringpid;privateStringurl;privateIntegerorderRank;privateStringprivilegeType;privateStringprivilegeDescription;privateStringstate;privateStringcreatedBy;privateDatecreatedDt;privateIntegerversion;privateStringupdatedBy;privateDateupdatedDt;privateIntegericonName;privateIntegerdeleteFlag;}

拓展对象

packagecom.zzg.vo;importjava.util.List;importcom.zzg.entity.BaoanPrivilege;importlombok.Data;@SuppressWarnings("serial")@DatapublicclassBaoanPrivilegeVoextendsBaoanPrivilege{privateList<BaoanPrivilege>children;}

第三步:权限表Mapper 定义

mapper 接口定义

packagecom.zzg.mapper;importjava.util.List;importjava.util.Map;importorg.apache.ibatis.annotations.Param;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.baomidou.mybatisplus.core.metadata.IPage;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.zzg.entity.BaoanPrivilege;importcom.zzg.vo.BaoanPrivilegeVo;publicinterfaceBaoanPrivilegeMapperextendsBaseMapper<BaoanPrivilege>{List<BaoanPrivilegeVo>selectList(Map<String,Object>parameter);IPage<BaoanPrivilegeVo>selectPage(Pagepage,@Param("vo")Map<String,Object>parameter);}

mapper.xml 文件定义

<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.zzg.mapper.BaoanPrivilegeMapper"><resultMapid="BaseResultMap"type="com.zzg.entity.BaoanPrivilege"><idcolumn="id"jdbcType="VARCHAR"property="id"/><resultcolumn="privilege_name"jdbcType="VARCHAR"property="privilegeName"/><resultcolumn="privilege_code"jdbcType="VARCHAR"property="privilegeCode"/><resultcolumn="pid"jdbcType="VARCHAR"property="pid"/><resultcolumn="url"jdbcType="VARCHAR"property="url"/><resultcolumn="order_rank"jdbcType="INTEGER"property="orderRank"/><resultcolumn="privilege_type"jdbcType="VARCHAR"property="privilegeType"/><resultcolumn="privilege_description"jdbcType="VARCHAR"property="privilegeDescription"/><resultcolumn="state"jdbcType="VARCHAR"property="state"/><resultcolumn="created_by"jdbcType="VARCHAR"property="createdBy"/><resultcolumn="created_dt"jdbcType="TIMESTAMP"property="createdDt"/><resultcolumn="version"jdbcType="INTEGER"property="version"/><resultcolumn="updated_by"jdbcType="VARCHAR"property="updatedBy"/><resultcolumn="updated_dt"jdbcType="TIMESTAMP"property="updatedDt"/><resultcolumn="icon_name"jdbcType="INTEGER"property="iconName"/><resultcolumn="delete_flag"jdbcType="INTEGER"property="deleteFlag"/></resultMap><resultMapid="BaseResultMapVo"type="com.zzg.vo.BaoanPrivilegeVo"extends="BaseResultMap"><collectioncolumn="id"property="children"javaType="java.util.ArrayList"ofType="com.zzg.entity.BaoanPrivilege"select="getNextNodeTree"/></resultMap><sqlid="Base_Column_List">id,privilege_name,privilege_code,pid,url,order_rank,privilege_type,privilege_description,state,created_by,created_dt,version,updated_by,updated_dt,icon_name,delete_flag</sql><sqlid="condition"><iftest="privilegeType!=nullandprivilegeType!=''">andbaoan_privilege.privilege_type=#{privilegeType}</if><iftest="pid!=nullandpid!=''">andbaoan_privilege.pid=#{pid}</if></sql><sqlid="conditionVo"><iftest="vo.privilegeType!=nullandvo.privilegeType!=''">andbaoan_privilege.privilege_type=#{vo.privilegeType}</if><iftest="vo.pid!=nullandvo.pid!=''">andbaoan_privilege.pid=#{vo.pid}</if></sql><selectid="getNextNodeTree"resultMap="BaseResultMapVo">SELECT<includerefid="Base_Column_List"/>FROMbaoan_privilegeWHEREpid=#{id}</select><selectid="selectList"parameterType="map"resultMap="BaseResultMapVo">select<includerefid="Base_Column_List"></include>FROMbaoan_privilegeWHERE1=1<includerefid="condition"></include></select><selectid="selectPage"parameterType="map"resultMap="BaseResultMapVo">select<includerefid="Base_Column_List"></include>FROMbaoan_privilegeWHERE1=1<includerefid="conditionVo"></include></select></mapper>

第三步:权限表Service 定义

packagecom.zzg.service;importjava.util.List;importjava.util.Map;importcom.baomidou.mybatisplus.core.metadata.IPage;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.baomidou.mybatisplus.extension.service.IService;importcom.zzg.entity.BaoanPrivilege;importcom.zzg.vo.BaoanPrivilegeVo;publicinterfaceBaoanPrivilegeServiceextendsIService<BaoanPrivilege>{List<BaoanPrivilegeVo>selectList(Map<String,Object>parameter);IPage<BaoanPrivilegeVo>selectPage(Page<BaoanPrivilegeVo>page,Map<String,Object>parameter);}

packagecom.zzg.service.impl;importjava.util.List;importjava.util.Map;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importcom.baomidou.mybatisplus.core.metadata.IPage;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.baomidou.mybatisplus.extension.service.impl.ServiceImpl;importcom.zzg.entity.BaoanPrivilege;importcom.zzg.mapper.BaoanPrivilegeMapper;importcom.zzg.service.BaoanPrivilegeService;importcom.zzg.vo.BaoanPrivilegeVo;@ServicepublicclassBaoanPrivilegeServiceImplextendsServiceImpl<BaoanPrivilegeMapper,BaoanPrivilege>implementsBaoanPrivilegeService{@AutowiredprivateBaoanPrivilegeMappermapper;@OverridepublicList<BaoanPrivilegeVo>selectList(Map<String,Object>parameter){//TODOAuto-generatedmethodstubreturnmapper.selectList(parameter);}@OverridepublicIPage<BaoanPrivilegeVo>selectPage(Page<BaoanPrivilegeVo>page,Map<String,Object>parameter){//TODOAuto-generatedmethodstubreturnmapper.selectPage(page,parameter);}}

第四步:controller 层接口对外提供服务

//查@ApiOperation(httpMethod="POST",value="基于分页查询符合条件权限记录")@RequestMapping(value="/getPage",method={RequestMethod.POST})@ApiImplicitParams({@ApiImplicitParam(name="username",value="管理员名称",required=false,dataType="String",paramType="query")})publicResultgetPage(@RequestBodyMap<String,Object>parame){//动态构建添加参数//QueryWrapper<BaoanPrivilegeVo>query=newQueryWrapper<BaoanPrivilegeVo>();//this.buildQuery(parame,query);PageParamepageParame=this.initPageBounds(parame);Page<BaoanPrivilegeVo>page=newPage<BaoanPrivilegeVo>(pageParame.getPage(),pageParame.getLimit());IPage<BaoanPrivilegeVo>list=baoanPrivilegeService.selectPage(page,parame);returnResult.ok().setDatas(list);}

前端效果展示: