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

熊孩纸 阅读:301 2021-03-31 12:26:11 评论: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); 
	} 

前端效果展示:

声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
KIKK导航

KIKK导航

排行榜
关注我们

一个IT知识分享的公众号