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

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

场景:

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

功能实现:

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

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);
 }

前端效果展示:

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