SpringBoot + MyBatisPlus + MySQL8 实现树形结构查询

ゝ一纸荒年。 2023-10-10 12:33 75阅读 0赞

## 场景: ##

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

## 功能实现: ##

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

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

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

基本对象

package com.zzg.entity;
    
    import java.io.Serializable;
    import java.util.Date;
    
    import com.baomidou.mybatisplus.annotation.TableName;
    
    import lombok.Data;
    
    @SuppressWarnings("serial")
    @TableName(value = "baoan_privilege")
    @Data
    public class BaoanPrivilege implements Serializable {
    	private String id;
    
        private String privilegeName;
    
        private String privilegeCode;
    
        private String pid;
    
        private String url;
    
        private Integer orderRank;
    
        private String privilegeType;
    
        private String privilegeDescription;
    
        private String state;
    
        private String createdBy;
    
        private Date createdDt;
    
        private Integer version;
    
        private String updatedBy;
    
        private Date updatedDt;
    
        private Integer iconName;
    
        private Integer deleteFlag;
    }

拓展对象

package com.zzg.vo;
    
    import java.util.List;
    
    import com.zzg.entity.BaoanPrivilege;
    
    import lombok.Data;
    
    @SuppressWarnings("serial")
    @Data
    public class BaoanPrivilegeVo  extends BaoanPrivilege {
    	
    	private List<BaoanPrivilege> children;
    }

第三步:权限表Mapper 定义

mapper 接口定义

package com.zzg.mapper;
    
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Param;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.zzg.entity.BaoanPrivilege;
    import com.zzg.vo.BaoanPrivilegeVo;
    
    public interface BaoanPrivilegeMapper extends BaseMapper<BaoanPrivilege> {
    	List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter);
    	
    	IPage<BaoanPrivilegeVo> selectPage(Page page, @Param("vo")Map<String, Object> parameter);
    }

mapper.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.zzg.mapper.BaoanPrivilegeMapper">
      <resultMap id="BaseResultMap" type="com.zzg.entity.BaoanPrivilege">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <result column="privilege_name" jdbcType="VARCHAR" property="privilegeName" />
        <result column="privilege_code" jdbcType="VARCHAR" property="privilegeCode" />
        <result column="pid" jdbcType="VARCHAR" property="pid" />
        <result column="url" jdbcType="VARCHAR" property="url" />
        <result column="order_rank" jdbcType="INTEGER" property="orderRank" />
        <result column="privilege_type" jdbcType="VARCHAR" property="privilegeType" />
        <result column="privilege_description" jdbcType="VARCHAR" property="privilegeDescription" />
        <result column="state" jdbcType="VARCHAR" property="state" />
        <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
        <result column="created_dt" jdbcType="TIMESTAMP" property="createdDt" />
        <result column="version" jdbcType="INTEGER" property="version" />
        <result column="updated_by" jdbcType="VARCHAR" property="updatedBy" />
        <result column="updated_dt" jdbcType="TIMESTAMP" property="updatedDt" />
        <result column="icon_name" jdbcType="INTEGER" property="iconName" />
        <result column="delete_flag" jdbcType="INTEGER" property="deleteFlag" />
      </resultMap>
      
      <resultMap id="BaseResultMapVo" type="com.zzg.vo.BaoanPrivilegeVo" extends="BaseResultMap">
      	<collection column="id" property="children" javaType="java.util.ArrayList"
    ofType="com.zzg.entity.BaoanPrivilege" select="getNextNodeTree"/>
      </resultMap>
      
      <sql id="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>
      
      <sql id="condition">
      	<if test="privilegeType != null and privilegeType != '' ">
      		and baoan_privilege.privilege_type = #{privilegeType}
      	</if>
      	<if test="pid != null and pid != '' ">
      		and baoan_privilege.pid = #{pid}
      	</if>
      </sql>
      
      <sql id="conditionVo">
      	<if test="vo.privilegeType != null and vo.privilegeType != '' ">
      		and baoan_privilege.privilege_type = #{vo.privilegeType}
      	</if>
      	<if test="vo.pid != null and vo.pid != '' ">
      		and baoan_privilege.pid = #{vo.pid}
      	</if>
      </sql>
      
      <select id="getNextNodeTree" resultMap="BaseResultMapVo">
    	SELECT
    		<include refid="Base_Column_List"/>
    	FROM baoan_privilege
    	WHERE pid = #{id}
     </select>
     
     <select id="selectList" parameterType="map" resultMap="BaseResultMapVo">
      		select
    		<include refid="Base_Column_List"></include>
      		FROM baoan_privilege
      		WHERE 1 = 1 
      		<include refid="condition"></include>
      </select>
      <select id="selectPage" parameterType="map" resultMap="BaseResultMapVo">
      		select
    		<include refid="Base_Column_List"></include>
      		FROM baoan_privilege
      		WHERE 1 = 1 
      		<include refid="conditionVo"></include>
      </select>
    </mapper>

第三步:权限表Service 定义

package com.zzg.service;
    
    import java.util.List;
    import java.util.Map;
    
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.zzg.entity.BaoanPrivilege;
    import com.zzg.vo.BaoanPrivilegeVo;
    
    public interface BaoanPrivilegeService extends IService<BaoanPrivilege> {
    	List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter);
    	
    	IPage<BaoanPrivilegeVo> selectPage(Page<BaoanPrivilegeVo> page, Map<String, Object> parameter);
    }

package com.zzg.service.impl;
    
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.zzg.entity.BaoanPrivilege;
    import com.zzg.mapper.BaoanPrivilegeMapper;
    import com.zzg.service.BaoanPrivilegeService;
    import com.zzg.vo.BaoanPrivilegeVo;
    
    @Service
    public class BaoanPrivilegeServiceImpl extends ServiceImpl<BaoanPrivilegeMapper, BaoanPrivilege> implements BaoanPrivilegeService {
    	@Autowired
    	private BaoanPrivilegeMapper mapper;
    	
    	@Override
    	public List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter) {
    		// TODO Auto-generated method stub
    		return mapper.selectList(parameter);
    	}
    
    	@Override
    	public IPage<BaoanPrivilegeVo> selectPage(Page<BaoanPrivilegeVo> page, Map<String, Object> parameter) {
    		// TODO Auto-generated method stub
    		return mapper.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") })
    	public Result getPage(@RequestBody Map<String, Object> parame) {
    		// 动态构建添加参数
    //		QueryWrapper<BaoanPrivilegeVo> query = new QueryWrapper<BaoanPrivilegeVo>();
    //		this.buildQuery(parame, query);
    
    		PageParame pageParame = this.initPageBounds(parame);
    		Page<BaoanPrivilegeVo> page = new Page<BaoanPrivilegeVo>(pageParame.getPage(), pageParame.getLimit());
    		IPage<BaoanPrivilegeVo> list = baoanPrivilegeService.selectPage(page, parame);
    		return Result.ok().setDatas(list);
    	}

前端效果展示:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/202102101140429.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70