权限管理系统，Lamda递归查询所有菜单

有时间指导毕业设计

已于 2022-12-03 18:48:22 修改

阅读量562

点赞数 1

分类专栏：工作中的项目笔记

于 2022-11-30 16:42:08 首次发布

本文链接：https://blog.csdn.net/houzhicongone/article/details/128117921

版权

菜单管理角色权限数据库设计递归查询 Java实体模型

关键词由CSDN通过智能技术生成

工作中的项目笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

可以先去学习一下stream流

stream流学习

表结构建立，sql语句

这个是postgreSql 和mysql有一定的区别，注意一下！


CREATE TABLE "test"."sys_menu" (
	"id" INT8 NOT NULL,
	"parent_id" INT8 NOT NULL DEFAULT '-1' :: INTEGER,
	"name" VARCHAR ( 64 )  NOT NULL,
	"api_url" VARCHAR ( 255 ) ,
	"path" VARCHAR ( 255 ) ,
	"sort" INT2 NOT NULL,
	"create_time" TIMESTAMP ( 6 ),
	"update_time" TIMESTAMP ( 6 ),
	"type" INT2 NOT NULL DEFAULT 1,
	"hidden" INT2 NOT NULL DEFAULT 0,
	"tenant_id" VARCHAR ( 32 ) ,
	"belong_to" INT2 NOT NULL DEFAULT 1,
	CONSTRAINT "pk_sys_menu" PRIMARY KEY ( "id" ) 
);
ALTER TABLE "test"."sys_menu" OWNER TO "iscm";
CREATE INDEX "idx_parent_id" ON "test"."sys_menu" USING btree ( "parent_id" "pg_catalog"."int8_ops" ASC NULLS LAST );
COMMENT ON COLUMN "test"."sys_menu"."id" IS '主键';
COMMENT ON COLUMN "test"."sys_menu"."parent_id" IS '父菜单ID';
COMMENT ON COLUMN "test"."sys_menu"."name" IS '菜单名称';
COMMENT ON COLUMN "test"."sys_menu"."api_url" IS '请求url(type=2时有效)';
COMMENT ON COLUMN "test"."sys_menu"."path" IS '菜单路径';
COMMENT ON COLUMN "test"."sys_menu"."sort" IS '排序';
COMMENT ON COLUMN "test"."sys_menu"."create_time" IS '创建时间';
COMMENT ON COLUMN "test"."sys_menu"."update_time" IS '更新时间';
COMMENT ON COLUMN "test"."sys_menu"."type" IS '类型(1:菜单,2:按钮)';
COMMENT ON COLUMN "test"."sys_menu"."hidden" IS '是否隐藏(1:是,0:否)';
COMMENT ON COLUMN "test"."sys_menu"."tenant_id" IS '租户字段';
COMMENT ON COLUMN "test"."sys_menu"."belong_to" IS '菜单归属(1:后台菜单,2:商城菜单)';
COMMENT ON TABLE "test"."sys_menu" IS '系统菜单表';






CREATE TABLE "test"."sys_role" (
	"id" INT8 NOT NULL,
	"code" VARCHAR ( 32 )  NOT NULL,
	"name" VARCHAR ( 50 )  NOT NULL,
	"create_time" TIMESTAMP ( 6 ),
	"update_time" TIMESTAMP ( 6 ),
	"tenant_id" VARCHAR ( 32 ) ,
	"role_type" INT2,
	CONSTRAINT "pk_sys_role" PRIMARY KEY ( "id" ) 
);
ALTER TABLE "test"."sys_role" OWNER TO "iscm";
COMMENT ON COLUMN "test"."sys_role"."id" IS '主键';
COMMENT ON COLUMN "test"."sys_role"."code" IS '角色编码';
COMMENT ON COLUMN "test"."sys_role"."name" IS '角色名';
COMMENT ON COLUMN "test"."sys_role"."create_time" IS '创建时间';
COMMENT ON COLUMN "test"."sys_role"."update_time" IS '修改时间';
COMMENT ON COLUMN "test"."sys_role"."tenant_id" IS '租户字段';
COMMENT ON COLUMN "test"."sys_role"."role_type" IS '角色类型(0:系统管理员,1:零售商,2:供应商,3:零售商/供应商,4:省分管理员)';
COMMENT ON TABLE "test"."sys_role" IS '系统角色表';





CREATE TABLE "test"."sys_role_menu" ( "role_id" INT8 NOT NULL, "menu_id" INT8 NOT NULL, CONSTRAINT "pk_sys_role_menu" PRIMARY KEY ( "role_id", "menu_id" ) );
ALTER TABLE "test"."sys_role_menu" OWNER TO "iscm";
COMMENT ON TABLE "test"."sys_role_menu" IS '系统角色菜单表';


CREATE TABLE "test"."sys_role_user" ( "user_id" INT8 NOT NULL, "role_id" INT8 NOT NULL, CONSTRAINT "pk_sys_role_user" PRIMARY KEY ( "user_id", "role_id" ) );
ALTER TABLE "test"."sys_role_user" OWNER TO "iscm";
COMMENT ON TABLE "test"."sys_role_user" IS '系统用户角色表';













CREATE TABLE "test"."sys_user" (
	"id" INT8 NOT NULL,
	"username" VARCHAR ( 50 )  NOT NULL,
	"password" VARCHAR ( 60 )  NOT NULL,
	"nickname" VARCHAR ( 255 ) ,
	"head_img_url" VARCHAR ( 255 ) ,
	"mobile" VARCHAR ( 11 ) ,
	"sex" INT2,
	"enabled" INT2 NOT NULL DEFAULT 1,
	"type" VARCHAR ( 16 ) ,
	"create_time" TIMESTAMP ( 6 ),
	"update_time" TIMESTAMP ( 6 ),
	"company" VARCHAR ( 255 ) ,
	"open_id" VARCHAR ( 32 ) ,
	"is_del" INT2 NOT NULL DEFAULT 0,
	CONSTRAINT "pk_sys_user" PRIMARY KEY ( "id" ) 
);
ALTER TABLE "test"."sys_user" OWNER TO "iscm";
COMMENT ON COLUMN "test"."sys_user"."id" IS '主键';
COMMENT ON COLUMN "test"."sys_user"."username" IS '账号';
COMMENT ON COLUMN "test"."sys_user"."password" IS '密码';
COMMENT ON COLUMN "test"."sys_user"."nickname" IS '姓名';
COMMENT ON COLUMN "test"."sys_user"."head_img_url" IS '头像';
COMMENT ON COLUMN "test"."sys_user"."mobile" IS '手机号';
COMMENT ON COLUMN "test"."sys_user"."sex" IS '性别(0:未知,1:男,2:女)';
COMMENT ON COLUMN "test"."sys_user"."enabled" IS '有效状态(1:启用,0:停用)';
COMMENT ON COLUMN "test"."sys_user"."type" IS '类型';
COMMENT ON COLUMN "test"."sys_user"."create_time" IS '创建时间';
COMMENT ON COLUMN "test"."sys_user"."update_time" IS '修改时间';
COMMENT ON COLUMN "test"."sys_user"."company" IS '公司';
COMMENT ON COLUMN "test"."sys_user"."open_id" IS 'openid';
COMMENT ON COLUMN "test"."sys_user"."is_del" IS '删除标识(0-正常,1:删除)';
COMMENT ON TABLE "test"."sys_user" IS '系统用户表';



CREATE TABLE "test"."sys_user_company" (
	"id" INT8 NOT NULL,
	"user_id" INT8 NOT NULL,
	"role_type" INT2 NOT NULL,
	"company_code" VARCHAR ( 20 )  NOT NULL,
	"province_code" VARCHAR ( 20 )  NOT NULL,
	"create_time" TIMESTAMP ( 6 ),
	"update_time" TIMESTAMP ( 6 ),
	"merchant_id" INT8,
	CONSTRAINT "pk_sys_user_organization" PRIMARY KEY ( "id" ) 
);
ALTER TABLE "test"."sys_user_company" OWNER TO "iscm";
CREATE INDEX "idx_sys_user_organization_1" ON "test"."sys_user_company" USING btree ( "user_id" "pg_catalog"."int8_ops" ASC NULLS LAST );
COMMENT ON COLUMN "test"."sys_user_company"."id" IS '主键';
COMMENT ON COLUMN "test"."sys_user_company"."user_id" IS '用户id';
COMMENT ON COLUMN "test"."sys_user_company"."role_type" IS '角色类型(0:系统管理员,1:零售商,2:供应商,3:零售商/供应商,4:省分管理员)';
COMMENT ON COLUMN "test"."sys_user_company"."company_code" IS '公司编码';
COMMENT ON COLUMN "test"."sys_user_company"."province_code" IS '省份编码';
COMMENT ON COLUMN "test"."sys_user_company"."create_time" IS '创建时间';
COMMENT ON COLUMN "test"."sys_user_company"."update_time" IS '修改时间';
COMMENT ON COLUMN "test"."sys_user_company"."merchant_id" IS '商户id';
COMMENT ON TABLE "test"."sys_user_company" IS '用户所属公司组织';

1. 封装一个菜单的返回实体给前端


@Data
@ApiModel("菜单返回参数模型")
public class QueryMenuResp {
	@ApiModelProperty("ID")
	@JsonFormat(shape = JsonFormat.Shape.STRING)
	private Long id;
	/**
	 * 父菜单ID
	 */
	@ApiModelProperty("父菜单ID")
	@JsonFormat(shape = JsonFormat.Shape.STRING)
	private Long parentId;
	/**
	 * 菜单名
	 */
	@ApiModelProperty("菜单名")
	private String name;
	/**
	 * 菜单路径
	 */
	@ApiModelProperty("菜单路径")
	private String path;
	/**
	 * 排序
	 */
	@ApiModelProperty("排序")
	private Integer sort;
	/**
	 * 是否隐藏(1:是,0:否)
	 */
	@ApiModelProperty("是否隐藏(1:是,0:否)")
	private Integer hidden;
	/**
	 * 当前角色选中状态
	 */
	@ApiModelProperty("当前角色选中状态(1:是,0:否,仅findAvaiableMenuTree接口返回)")
	private Integer checked;
	/**
	 * 下级菜单
	 */
	@ApiModelProperty("下级菜单")
	private List<QueryMenuResp> subMenus;
}

2.先查询出来一级菜单，然后查询非一级菜单进行递归调用，直到全部菜单查询出来

    @ParamCheck
    @ApiOperation(value = "查询所有菜单以树形结构返回")
    @PostMapping("/findMenuTree")
    public Result<List<QueryMenuResp>> findMenuTree(@RequestBody @ModelCheck QueryMenuParam param) {
    	log.info("findMenuTree param: {}", param);
    	
    	if (!MenuBelongTo.isLegalMenuBelong(param.getBelongTo())) {
    		return Result.error("非法的菜单归属类型");
    	}
    	
    	List<QueryMenuResp> allMenuList =  ModelChangeUtils.changeMenuRespList(menuService.findMenus(param.getBelongTo()));
    	// 一级菜单
    	List<QueryMenuResp> rootMenus = allMenuList.stream()
				.filter(r -> r.getParentId().equals(CommonConstant.ROOT_MENU_PARENT_ID))
				.collect(Collectors.toList());
    	// 非一级菜单
		List<QueryMenuResp> notRootMenus = allMenuList.stream()
				.filter(r -> !r.getParentId().equals(CommonConstant.ROOT_MENU_PARENT_ID))
				.collect(Collectors.toList());
		
		// 构造树形结构
		for (QueryMenuResp menu : rootMenus) {
            List<QueryMenuResp> subMenus = ModelChangeUtils.iterateMenus(notRootMenus, menu.getId());
            menu.setSubMenus(subMenus);
        }
		
        return Result.succeed(rootMenus);
    }

3.递归方法


public class ModelChangeUtils {
	/**
     * 多级菜单查询方法
     * @param menuList 不包含最高层次菜单的菜单集合
     * @param parentId 父菜单id
     * @return
     */
    public static List<QueryMenuResp> iterateMenus(List<QueryMenuResp> menuList, Long parentId){
        List<QueryMenuResp> result = new ArrayList<QueryMenuResp>();
        for (QueryMenuResp menu : menuList) {
            if (parentId.equals(menu.getParentId())){
                //递归查询当前子菜单的子菜单
                List<QueryMenuResp> iterateMenu = iterateMenus(menuList, menu.getId());
                menu.setSubMenus(iterateMenu);
                result.add(menu);
            }
        }
        return result;
    }

}

4.菜单表结构实体

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_menu")
public class SysMenu extends SuperEntity<SysRole> {
	private static final long serialVersionUID = 749360940290141180L;

	/**
	 * 父菜单ID
	 */
	private Long parentId;
	/**
	 * 菜单名
	 */
	private String name;
	/**
	 * 请求地址(type=2时有效)
	 */
	private String apiUrl;
	/**
	 * 菜单路径
	 */
	private String path;
	/**
	 * 排序
	 */
	private Integer sort;
	/**
	 * 类型(1:菜单,2:按钮)
	 */
	private Integer type;
	/**
	 * 是否隐藏(1:是,0:否)
	 */
	private Integer hidden;
	/**
	 * 菜单归属(1:后台管理菜单,2:商城菜单)
	 */
	private Integer belongTo;
	
	@TableField(exist = false)
	private List<SysMenu> subMenus;
}

5.角色表结构实体

/**
 * 
 * 角色
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_role")
public class SysRole  {
private Long id;
private Date create_time;
private Date update_time;
    private static final long serialVersionUID = 4497149010220586111L;
    private String code;
    private String name;
    /**
     * 角色类型(0:系统管理员,1:零售商,2:供应商,3:零售商/供应商,4:省分管理员)
     */
    private Integer roleType;
    @TableField(exist = false)
    private Long userId;
}

6 加上角色菜单表实体

public class SysRoleMenu{

private Long roleId;
private Long menuId;

}

7.菜单实体表



import java.util.List;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 *
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_menu")
public class SysMenu extends SuperEntity<SysRole> {
	private static final long serialVersionUID = 749360940290141180L;

	/**
	 * 父菜单ID
	 */
	private Long parentId;
	/**
	 * 菜单名
	 */
	private String name;
	/**
	 * 请求地址(type=2时有效)
	 */
	private String apiUrl;
	/**
	 * 菜单路径
	 */
	private String path;
	/**
	 * 排序
	 */
	private Integer sort;
	/**
	 * 类型(1:菜单,2:按钮)
	 */
	private Integer type;
	/**
	 * 是否隐藏(1:是,0:否)
	 */
	private Integer hidden;
	/**
	 * 菜单归属(1:后台管理菜单,2:商城菜单)
	 */
	private Integer belongTo;
	
	@TableField(exist = false)
	private List<SysMenu> subMenus;
}

8.用户表实体



import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import java.util.List;

import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 
 * 用户实体
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("sys_user")
public class SysUser extends SuperEntity<SysUser> {
	private static final long serialVersionUID = -5886012896705137070L;

	private String username;
	private String password;
	private String nickname;
	private String headImgUrl;
	private String mobile;
	private Integer sex;
	@TableField(value = "enabled")
	private Integer userEnabled;
	private String type;
	private String openId;
	@TableLogic
	private Integer isDel;

	@TableField(exist = false)
	private List<SysRole> roles;
	@TableField(exist = false)
	private String roleId;
	@TableField(exist = false)
	private String oldPassword;
	@TableField(exist = false)
	private String newPassword;
	
	//---------------- iscm-sc扩展 ----------------//
	/**
	 * 角色类型
	 */
	@TableField(exist = false)
	private Integer roleType;
	/**
	 * 公司编码
	 */
	@TableField(exist = false)
	private String companyCode;
	/**
	 * 商户ID
	 */
	@TableField(exist = false)
	private Long merchantId;
	/**
	 * 省份编码
	 */
	@TableField(exist = false)
	private String provinceCode;
}

9.SuperEntity所有的基表字段，可以让所有的实体类extends这个

import java.util.Date;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.eshore.khala.common.constant.CommonConstant;
import com.fasterxml.jackson.annotation.JsonFormat;

import lombok.Getter;
import lombok.Setter;

/**
 * 实体父类
 *
 * @author khala
 */
@Setter
@Getter
public class SuperEntity<T extends Model<?>> extends Model<T> {
    /**
	 * 
	 */
	private static final long serialVersionUID = -1642699353161738353L;
	/**
     * 主键ID
     */
    @TableId(type = IdType.ASSIGN_ID)//基于雪花算法生成long主键
    private Long id;
    @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = CommonConstant.DATETIME_FORMAT)
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = CommonConstant.DATETIME_FORMAT)
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}