SpringBoot+MyBatisPlus+MySQL8实现树形结构查询-FinClip官网

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

网友投稿 1096 2023-01-13

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 '主键'wMWVCdOuF,

`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 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 {

List selectList(Map parameter);

IPage selectPage(Page page, @Param("vo")Map parameter);

}

mapper.xml 文件定义

ofType="com.zzg.entity.BaoanPrivilege" select="getNextNodeTree"/>

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

and baoan_privilege.privilege_type = #{privilegeType}

and baoan_privilege.pid = #{pid}

and baoan_privilege.privilege_type = #{vo.privilegeType}

and baoan_privilege.pid = #{vo.pid}

SELECT

FROM baoan_privilege

WHERE pid = #{id}

select

FROM baoan_privilege

WHERE 1 = 1

select

FROM baoan_privilege

WHERE 1 = 1

第三步:权限表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 {

List selectList(Map parameter);

IPage selectPage(Page page, Map 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 implements BaoanPrivilegeService {

@Autowired

private BaoanPrivilegeMapper mapper;

@Override

public List selectList(Map parameter) {

// TODO Auto-generated method stub

return mapper.selectList(parameter);

}

@Override

public IPage selectPage(Page page, Map 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 parame) {http://

// 动态构建添加参数

// QueryWrapper query = new QueryWrapper();

// this.buildQuery(parame, query);

PageParame pageParame = this.initPageBounds(parame);

Page page = new Page(pageParame.getPage(), pageParame.getLimit());

IPage list = baoanPrivilegeService.selectPage(page, parame);

return Result.ok().setDatas(list);

}

前端效果展示:

ofType="com.zzg.entity.BaoanPrivilege" select="getNextNodeTree"/>

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

and baoan_privilege.privilege_type = #{privilegeType}

and baoan_privilege.pid = #{pid}

and baoan_privilege.privilege_type = #{vo.privilegeType}

and baoan_privilege.pid = #{vo.pid}

SELECT

FROM baoan_privilege

WHERE pid = #{id}

select

FROM baoan_privilege

WHERE 1 = 1

select

FROM baoan_privilege

WHERE 1 = 1

第三步:权限表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 {

List selectList(Map parameter);

IPage selectPage(Page page, Map 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 implements BaoanPrivilegeService {

@Autowired

private BaoanPrivilegeMapper mapper;

@Override

public List selectList(Map parameter) {

// TODO Auto-generated method stub

return mapper.selectList(parameter);

}

@Override

public IPage selectPage(Page page, Map 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 parame) {http://

// 动态构建添加参数

// QueryWrapper query = new QueryWrapper();

// this.buildQuery(parame, query);

PageParame pageParame = this.initPageBounds(parame);

Page page = new Page(pageParame.getPage(), pageParame.getLimit());

IPage list = baoanPrivilegeService.selectPage(page, parame);

return Result.ok().setDatas(list);

}

前端效果展示:

洞察探索如何利用 FinClip 提供的跨平台小程序技术，实现高效的支付管理与合规运营

1096 2023-01-13

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

洞察提升小程序标签管理，实现高效的金融行业数字化转型

洞察如何通过ios app灵活实现微信小程序的跨平台管理

洞察探索如何利用 FinClip 提供的跨平台小程序技术，实现高效的支付管理与合规运营

最近发表

更多内容

小程序SDK

Finclip技术文档

小程序开发

小程序容器

小程序框架

Finclip小程序平台

Finclip用户投稿

车联网

推荐文章

小程序SDK是什么意思？小程序sdk和插件有什么区别？

小程序支付功能怎么实现？

企业app开发流程是什么？

app运营模式有哪些？

小程序多端引流怎么做？

小程序生态分析的机会和威胁

Flutter入门这一篇效率文章就够了

原生与跨平台解决方案分析,跨平台软件开发技术方案

热更新技术：让软件更新变得更加轻松快速

解决方案

银行解决方案

证券解决方案

互联网解决方案

政企OA解决方案

科技解决方案

loT解决方案

信任解决方案

热评文章

AppCan:基于混合模式的移动应用开发,移动混合模

Hybrid App混合模式开发的了解

小程序容器技术助力券商数字营销突围，小程序容器化的意

用mpvue开发微信小程序基础知识（vue.js开发

小程序多端框架全面测评对比，强烈推荐！

券商app架构 - 解析券商应用程序的构建与设计