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); }
前端效果展示:
以上就是本文的全部內容,希望對大傢的學習有所幫助,也希望大傢多多支持WalkonNet。
推薦閱讀:
- Mybatis使用連表查詢的操作代碼
- 基於Java SSM實現Excel數據批量導入
- springboot使用mybatis一對多的關聯查詢問題記錄
- Mybatis實現聯表查詢並且分頁功能
- springboot+mybatis plus實現樹形結構查詢