经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MyBatis » 查看文章
SpringBoot+MyBatisPlus+MySQL8实现树形结构查询
来源：jb51　　时间：2021/6/7 9:22:46　　对本文有异议

本文实例为大家分享了SpringBoot+MyBatisPlus+MySQL8实现树形结构查询，供大家参考，具体内容如下

场景:

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

功能实现:

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

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

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

基本对象

  1. package com.zzg.entity;
  2.  
  3. import java.io.Serializable;
  4. import java.util.Date;
  5.  
  6. import com.baomidou.mybatisplus.annotation.TableName;
  7.  
  8. import lombok.Data;
  9.  
  10. @SuppressWarnings("serial")
  11. @TableName(value = "baoan_privilege")
  12. @Data
  13. public class BaoanPrivilege implements Serializable {
  14.  private String id;
  15.  
  16.     private String privilegeName;
  17.  
  18.     private String privilegeCode;
  19.  
  20.     private String pid;
  21.  
  22.     private String url;
  23.  
  24.     private Integer orderRank;
  25.  
  26.     private String privilegeType;
  27.  
  28.     private String privilegeDescription;
  29.  
  30.     private String state;
  31.  
  32.     private String createdBy;
  33.  
  34.     private Date createdDt;
  35.  
  36.     private Integer version;
  37.  
  38.     private String updatedBy;
  39.  
  40.     private Date updatedDt;
  41.  
  42.     private Integer iconName;
  43.  
  44.     private Integer deleteFlag;
  45. }

拓展对象

  1. package com.zzg.vo;
  2.  
  3. import java.util.List;
  4.  
  5. import com.zzg.entity.BaoanPrivilege;
  6.  
  7. import lombok.Data;
  8.  
  9. @SuppressWarnings("serial")
  10. @Data
  11. public class BaoanPrivilegeVo  extends BaoanPrivilege {
  12.  
  13.  private List<BaoanPrivilege> children;
  14. }

第三步:权限表Mapper 定义

mapper 接口定义

  1. package com.zzg.mapper;
  2.  
  3. import java.util.List;
  4. import java.util.Map;
  5.  
  6. import org.apache.ibatis.annotations.Param;
  7.  
  8. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
  9. import com.baomidou.mybatisplus.core.metadata.IPage;
  10. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
  11. import com.zzg.entity.BaoanPrivilege;
  12. import com.zzg.vo.BaoanPrivilegeVo;
  13.  
  14. public interface BaoanPrivilegeMapper extends BaseMapper<BaoanPrivilege> {
  15.  List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter);
  16.  
  17.  IPage<BaoanPrivilegeVo> selectPage(Page page, @Param("vo")Map<String, Object> parameter);
  18. }

mapper.xml 文件定义

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <mapper namespace="com.zzg.mapper.BaoanPrivilegeMapper">
  4.   <resultMap id="BaseResultMap" type="com.zzg.entity.BaoanPrivilege">
  5.     <id column="id" jdbcType="VARCHAR" property="id" />
  6.     <result column="privilege_name" jdbcType="VARCHAR" property="privilegeName" />
  7.     <result column="privilege_code" jdbcType="VARCHAR" property="privilegeCode" />
  8.     <result column="pid" jdbcType="VARCHAR" property="pid" />
  9.     <result column="url" jdbcType="VARCHAR" property="url" />
  10.     <result column="order_rank" jdbcType="INTEGER" property="orderRank" />
  11.     <result column="privilege_type" jdbcType="VARCHAR" property="privilegeType" />
  12.     <result column="privilege_description" jdbcType="VARCHAR" property="privilegeDescription" />
  13.     <result column="state" jdbcType="VARCHAR" property="state" />
  14.     <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
  15.     <result column="created_dt" jdbcType="TIMESTAMP" property="createdDt" />
  16.     <result column="version" jdbcType="INTEGER" property="version" />
  17.     <result column="updated_by" jdbcType="VARCHAR" property="updatedBy" />
  18.     <result column="updated_dt" jdbcType="TIMESTAMP" property="updatedDt" />
  19.     <result column="icon_name" jdbcType="INTEGER" property="iconName" />
  20.     <result column="delete_flag" jdbcType="INTEGER" property="deleteFlag" />
  21.   </resultMap>
  22.   
  23.   <resultMap id="BaseResultMapVo" type="com.zzg.vo.BaoanPrivilegeVo" extends="BaseResultMap">
  24.    <collection column="id" property="children" javaType="java.util.ArrayList"
  25. ofType="com.zzg.entity.BaoanPrivilege" select="getNextNodeTree"/>
  26.   </resultMap>
  27.   
  28.   <sql id="Base_Column_List">
  29.     id, privilege_name, privilege_code, pid, url, order_rank, privilege_type, privilege_description, 
  30.     state, created_by, created_dt, version, updated_by, updated_dt, icon_name, delete_flag
  31.   </sql>
  32.   
  33.   <sql id="condition">
  34.    <if test="privilegeType != null and privilegeType != '' ">
  35.     and baoan_privilege.privilege_type = #{privilegeType}
  36.    </if>
  37.    <if test="pid != null and pid != '' ">
  38.     and baoan_privilege.pid = #{pid}
  39.    </if>
  40.   </sql>
  41.   
  42.   <sql id="conditionVo">
  43.    <if test="vo.privilegeType != null and vo.privilegeType != '' ">
  44.     and baoan_privilege.privilege_type = #{vo.privilegeType}
  45.    </if>
  46.    <if test="vo.pid != null and vo.pid != '' ">
  47.     and baoan_privilege.pid = #{vo.pid}
  48.    </if>
  49.   </sql>
  50.   
  51.   <select id="getNextNodeTree" resultMap="BaseResultMapVo">
  52.  SELECT
  53.   <include refid="Base_Column_List"/>
  54.  FROM baoan_privilege
  55.  WHERE pid = #{id}
  56.  </select>
  57.  
  58.  <select id="selectList" parameterType="map" resultMap="BaseResultMapVo">
  59.     select
  60.   <include refid="Base_Column_List"></include>
  61.     FROM baoan_privilege
  62.     WHERE 1 = 1 
  63.     <include refid="condition"></include>
  64.   </select>
  65.   <select id="selectPage" parameterType="map" resultMap="BaseResultMapVo">
  66.     select
  67.   <include refid="Base_Column_List"></include>
  68.     FROM baoan_privilege
  69.     WHERE 1 = 1 
  70.     <include refid="conditionVo"></include>
  71.   </select>
  72. </mapper>

第三步:权限表Service 定义

  1. package com.zzg.service;
  2.  
  3. import java.util.List;
  4. import java.util.Map;
  5.  
  6. import com.baomidou.mybatisplus.core.metadata.IPage;
  7. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
  8. import com.baomidou.mybatisplus.extension.service.IService;
  9. import com.zzg.entity.BaoanPrivilege;
  10. import com.zzg.vo.BaoanPrivilegeVo;
  11.  
  12. public interface BaoanPrivilegeService extends IService<BaoanPrivilege> {
  13.  List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter);
  14.  
  15.  IPage<BaoanPrivilegeVo> selectPage(Page<BaoanPrivilegeVo> page, Map<String, Object> parameter);
  16. }
  1. package com.zzg.service.impl;
  2.  
  3. import java.util.List;
  4. import java.util.Map;
  5.  
  6. import org.springframework.beans.factory.annotation.Autowired;
  7. import org.springframework.stereotype.Service;
  8.  
  9. import com.baomidou.mybatisplus.core.metadata.IPage;
  10. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
  11. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  12. import com.zzg.entity.BaoanPrivilege;
  13. import com.zzg.mapper.BaoanPrivilegeMapper;
  14. import com.zzg.service.BaoanPrivilegeService;
  15. import com.zzg.vo.BaoanPrivilegeVo;
  16.  
  17. @Service
  18. public class BaoanPrivilegeServiceImpl extends ServiceImpl<BaoanPrivilegeMapper, BaoanPrivilege> implements BaoanPrivilegeService {
  19.  @Autowired
  20.  private BaoanPrivilegeMapper mapper;
  21.  
  22.  @Override
  23.  public List<BaoanPrivilegeVo> selectList(Map<String, Object> parameter) {
  24.   // TODO Auto-generated method stub
  25.   return mapper.selectList(parameter);
  26.  }
  27.  
  28.  @Override
  29.  public IPage<BaoanPrivilegeVo> selectPage(Page<BaoanPrivilegeVo> page, Map<String, Object> parameter) {
  30.   // TODO Auto-generated method stub
  31.   return mapper.selectPage(page, parameter);
  32.  }
  33.  
  34. }

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

  1.  // 查
  2.  @ApiOperation(httpMethod = "POST", value = "基于分页查询符合条件权限记录")
  3.  @RequestMapping(value = "/getPage", method = { RequestMethod.POST })
  4.  @ApiImplicitParams({
  5.    @ApiImplicitParam(name = "username", value = "管理员名称", required = false, dataType = "String", paramType = "query") })
  6.  public Result getPage(@RequestBody Map<String, Object> parame) {
  7.   // 动态构建添加参数
  8. //  QueryWrapper<BaoanPrivilegeVo> query = new QueryWrapper<BaoanPrivilegeVo>();
  9. //  this.buildQuery(parame, query);
  10.  
  11.   PageParame pageParame = this.initPageBounds(parame);
  12.   Page<BaoanPrivilegeVo> page = new Page<BaoanPrivilegeVo>(pageParame.getPage(), pageParame.getLimit());
  13.   IPage<BaoanPrivilegeVo> list = baoanPrivilegeService.selectPage(page, parame);
  14.   return Result.ok().setDatas(list);
  15.  }

前端效果展示:

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持w3xue。

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MyBatis热门文章

  • Mybatis使用连表查询的操作代码
  • Mybatis-plus如何查询表中指定字段(不查询全部字段)
  • mybatis/mybatis-plus模糊查询语句特殊字符转义拦截器的实现
  • MyBatis注解开发-@Insert和@InsertProvider的使用
  • springboot+mybatis报错找不到实体类的问题
  • mybatis 忽略实体对象的某个属性(2种方式)
  • mybatis update set 多个字段实例
  • 如何通过一个注解实现MyBatis字段加解密
  • oracle+mybatis-plus+springboot实现分页查询的实例
  • MybatisPlus中的insert操作详解

    • MyBatis推荐文章

  • Mybatis-Plus最优化持久层开发
  • MyBatis 的在使用上的注意事项及其辨析 - Rainbow-Sea
  • 在 Wed 中应用 MyBatis（同时使用MVC架构模式，以及ThreadLocal 事务控制） - Rainbow-Sea
  • MyBatis源码之前言—JDBC编码存在的问题和Mybatis的介绍
  • mybatis plugin源码解析
  • Spring Boot + MyBatis-Plus 实现 MySQL 主从复制动态数据源切换
  • 一种轻量分表方案-MyBatis拦截器分表实践
  • 使用Mybatis自定义插件实现不侵入业务的公共参数自动追加
  • MyBatis-Plus雪花算法实现源码解析
  • Mybatis和其他主流框架的整合使用

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号