Java:SpringBoot+Jwt+Shiro+Mybati

2020-05-05 本文已影响0人颈椎以上瘫痪

基于公司业务和工作实践，在此记录项目中的coding环节，仅供大家参考，对笔者自己也是一个总结。在使用过程中大家有什么问题也可以提出来一起学习！
在贴上来的代码块中会包含一些注释，这是笔者在使用过程中留下的痕迹，与代码功能无关，大家可以参考或者忽略。

项目背景

采用BS架构的前后端分离一体化平台系统，后端技术和框架选型及目标：

SpringBoot：项目的主体框架

Jwt：前后端分离身份认证

Shiro：权限认证

MybatisPlus：数据库操作和代码生成

Swagger：接口调试

项目环境

OS：Windows 10

Java：java version "1.8.0_101"

IDE：IntelliJ IDEA 2018.2.2 (Ultimate Edition)

MySQL：MySQL - 5.7.27

Maven：apache-maven-3.6.0

项目集成

pom文件
根据下文中pom文件的内容可以看出各框架的版本，不再逐一赘述。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.huafeng</groupId>
    <artifactId>cams_hf</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>cams_hf</name>
    <description>一体化项目</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!--swagger begin-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <!--不添加这个配置时swagger默认引用的是1.5.20版本,其中会对一些没有添加注解的位置进行默认参数判断,在进入swagger接口文档首页时会出现一个数据转换错误,升级版本后对默认属性的判断方式不同,不会出现这个异常-->
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.5.22</version>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-models</artifactId>
            <version>1.5.22</version>
        </dependency>
        <!--swagger end-->

        <!--mybatis plus begin-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.0</version>
        </dependency>
        <!--mybatis plus 在3.0.3之后移除了代码生成器与模板引擎的默认依赖,需要手动添加相关依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.1</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.29</version>
        </dependency>
        <!--mybatis plus 会自动维护,只需要引用上述一个依赖即可,引入mybatis plus 后就不要再引入mybatis以及mybatis-spring了,避免版本差异导致的问题-->
        <!--<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatisplus-spring-boot-starter</artifactId>
            <version>1.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>2.3</version>
        </dependency>-->
        <!--mybatis plus end-->

        <!--mysql begin-->
        <!--注意MySQL驱动的版本,如果高版本比如8.0.2与mybatis plus一起使用时在项目启动会有错误-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>
        <!--<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>-->
        <!--mysql end-->

        <!--gson begin-->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
        </dependency>
        <!--gson end-->


        <!--redis begin-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <!--redis end-->

        <!--shiro begin-->
        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-spring</artifactId>
            <version>1.4.0</version>
        </dependency>
        <!--shiro end-->

        <!--jwt token begin-->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>3.8.0</version>
        </dependency>
        <!--jwt token end-->

    </dependencies>

    <build>
        <!--spring boot 编译是默认只会编译Java目录下的Java文件和resources中的文件,.xml文件不会被编译,mybatis扫描不到,加上这个配置可以将Java目录下的XML文件编译进去-->
        <resources>
            <resource>
                <filtering>true</filtering>
                <directory>${basedir}/src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>${basedir}/src/main/java</directory>
                <excludes>
                    <exclude>**/*.xml</exclude>
                </excludes>
            </resource>
        </resources>

        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

properties文件
这里使用的是yml格式的配置文件，理论上与properties文件一样使用，可能存在某些差异，但是不影响笔者使用。直观区别在于语法，大家可以在使用时了解一下yml的语法，还是挺好用的。

server:
  port: 8085
  servlet:
    context-path: /cams_hf

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/cams_server_hf?useSSL=true&characterEncoding=utf8&serverTimezone=UTC
    username: jinxing
    password: 19910414
    schema: classpath:/sql/db.sql
    #spring boot2 过后需要添加这个initialization-mode属性才会执行SQL文件
    #SQL文件会在程序每次运行时都执行,所以建议在数据创建完成后删除掉,否则会导致数据错误，也在SQL文件中对SQL语法进行处理,避免数据错误
    initialization-mode: always

mybatis-plus:
  mapper-locations: classpath*:com/huafeng/cams/module/*.xml # java目录
  #mapper-locations: classpath:mapper/*.xml  #sources目录 编译后没有resources目录,所以不用在目录前面加resource
  type-aliases-package: com.huafeng.cams
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

logging:
  file:
    path: d:/javaWebLog/cams_server_hf
    max-size: 10MB

集成完毕后使用Maven还对环境进行构建。完成后运行项目可能会出现一些问题无法启动，因为添加的框架有的需要以下配置才能生效。

项目配置

Swagger

package com.huafeng.cams.common.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.ParameterBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.schema.ModelRef;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Parameter;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;

import java.util.ArrayList;
import java.util.List;

/**
 * Author      JinXing     _让世界看到我
 * On          2019/12/30
 * Note        swagger 配置文件
 */
@Configuration
public class SwaggerConfig   {

    //添加了bean注解在接口文档界面接口内容不显示
    @Bean
    public Docket createRestApi(){

        //swagger 接口文档中添加填写header项
        ParameterBuilder pb = new ParameterBuilder();
        List<Parameter> pars = new ArrayList<>();
        pb
                .name("token")
                .modelRef(new ModelRef("string"))
                .parameterType("header")
                .required(false)
                .build();
        pars.add(pb.build());

        Docket docket = new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(getApiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.huafeng"))
                .paths(PathSelectors.any())
                .build()
                .globalOperationParameters(pars);
        return docket;
    }

    private ApiInfo getApiInfo() {
        ApiInfo apiInfo = new ApiInfoBuilder()
                .title("一体化平台接口文档")
                .description("restful接口测试")
                .termsOfServiceUrl("httpL//swagger.io/")
                .version("v1.0.0")
                .build();
        return apiInfo;
    }
}

Redis
笔者在项目中添加了Redis，实际上没有使用到Redis。现在用Redis做缓存是一个很常见的事情，因此集成是有必要的，但是根据公司业务实际不需要使用，因此只是集成了Redis环境，并没有使用。

package com.huafeng.cams.common.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;

/**
 * Author      JinXing     _让世界看到我
 * On          2020/1/7
 * Note        Redis 配置文件
 */
@Configuration
public class RedisConfig {

    @Bean
    public RedisTemplate<String,Object> redisTemplate(RedisConnectionFactory factory){
        RedisTemplate <String,Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        return template;
    }
}

MybatisPlus

package com.huafeng.cams.common.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * Author      JinXing     _让世界看到我
 * On          2019/12/30
 * Note        mybatis plus 配置文件
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        PaginationInterceptor page = new PaginationInterceptor();
        page.setDialectType("mysql");
        return page;
    }
}

启动类

package com.huafeng.cams;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@SpringBootApplication
@EnableSwagger2
@MapperScan("com.huafeng.cams")
public class CamsHFApplication {

    public static void main(String[] args) {
        SpringApplication.run(CamsHFApplication.class, args);
    }
}

按照上述配置文件启动应该是会失败的，因为配置文件中声明了项目启动时需要连接的数据库和执行SQL文件，但是目前还没有。需要在数据库中创建对应的数据库，和在指定的目录中创建SQL文件。因为SQL文件是每次项目运行都会执行，为了数据安全建议采用其它的方式初始化，或者初始化后删除这个配置，或者在SQL脚本中通过SQL语法进行控制，避免数据错误，下文贴一下通过SQL语法避免数据错误的SQL文件：

/*如果数据库不存在就创建数据库*/
CREATE DATABASE IF NOT EXISTS `cams_server_hf` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;

/*使用数据库*/
USE `cams_server_hf`;

/******************************************角色表*****************************************/
/*如果角色表不存在就创建*/
CREATE TABLE IF NOT EXISTS `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '角色名称',
  `type` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '角色类型',
  `description` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '角色描述',
  `create_user` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `department` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '单位/部门',
  PRIMARY KEY (`id`),
  UNIQUE KEY `角色名称唯一命中` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='角色表';

/*添加角色数据,添加逻辑是不存在的角色才添加*/
/*replace 代替insert 如果表中有这条数据就先删除然后插入,但是插入的内容中必须包含一个数据为主键,而且数据的id会被改变,因此不太实用*/
INSERT INTO sys_role (`name`,`type`,`description`) SELECT '系统管理员','系统预置','管理员' FROM DUAL WHERE NOT EXISTS (SELECT NAME FROM sys_role WHERE NAME='系统管理员');
INSERT INTO sys_role (`name`,`type`,`description`) SELECT '普通用户','系统预置','小小老百姓' FROM DUAL WHERE NOT EXISTS (SELECT NAME FROM sys_role WHERE NAME='普通用户');


/******************************************用户表*****************************************/
/*如果用户表不存在就创建*/
CREATE TABLE IF NOT EXISTS `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `username` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '登录名称',
  `password` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
  `type` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '用户类型',
  `phone_num` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '手机号',
  `organization` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '归属组织',
  `job` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '岗位',
  `create_user` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `last_login_time` bigint(20) DEFAULT NULL COMMENT '最后登录时间',
  `c_status` int(11) DEFAULT '1' COMMENT '用户状态(1:正常,2:注销,3:禁用,4:删除)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表';

/*添加用户数据,添加逻辑是不存在的用户才添加*/
INSERT INTO sys_user (`username`,`password`,`name`,`type`) SELECT 'admin','123','系统管理员','系统预置' FROM DUAL WHERE NOT EXISTS (SELECT username FROM sys_user WHERE username='admin');
INSERT INTO sys_user (`username`,`password`,`name`,`type`) SELECT 'zwl','123','周文璐','系统预置' FROM DUAL WHERE NOT EXISTS (SELECT username FROM sys_user WHERE username='zwl');

/******************************************用户角色表*****************************************/
/*如果用户角色表不存在就创建*/
CREATE TABLE IF NOT EXISTS `sys_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户角色表';

/*添加预设用户角色数据*/
INSERT INTO sys_user_role (`user_id`,`role_id`) SELECT (SELECT id FROM sys_user WHERE username='admin'),(SELECT id FROM sys_role WHERE NAME='系统管理员') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_user_role WHERE user_id=(SELECT id FROM sys_user WHERE username='admin') AND role_id=(SELECT id FROM sys_role WHERE NAME='系统管理员'));
INSERT INTO sys_user_role (`user_id`,`role_id`) SELECT (SELECT id FROM sys_user WHERE username='zwl'),(SELECT id FROM sys_role WHERE NAME='普通用户') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_user_role WHERE user_id=(SELECT id FROM sys_user WHERE username='zwl') AND role_id=(SELECT id FROM sys_role WHERE NAME='普通用户'));



/******************************************菜单表*****************************************/
/*如果系统菜单表不存在就创建*/
CREATE TABLE IF NOT EXISTS  `sys_menu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `pid` bigint(20) DEFAULT NULL COMMENT '父级菜单id',
  `name` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '菜单名称',
  `level` int(11) DEFAULT NULL COMMENT '菜单等级',
  `path` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '路径',
  `c_type` int(11) DEFAULT NULL COMMENT '菜单类型(1:菜单/2:行为)',
  `permission` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '权限',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*添加系统预设菜单*/
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '首页',1,0,1 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='首页');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '系统管理',1,0,1 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='系统管理');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '角色管理',2,(SELECT id FROM sys_menu WHERE name='系统管理'),2 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='角色管理');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '用户管理',2,(SELECT id FROM sys_menu WHERE name='系统管理'),2 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='用户管理');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '菜单管理',2,(SELECT id FROM sys_menu WHERE name='系统管理'),1 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='菜单管理');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '权限管理',2,(SELECT id FROM sys_menu WHERE name='系统管理'),1 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='权限管理');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '菜单权限',3,(SELECT id FROM sys_menu WHERE name='权限管理'),2 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='菜单权限');
INSERT INTO sys_menu (`name`,`level`,`pid`,`c_type`) SELECT '功能权限',3,(SELECT id FROM sys_menu WHERE name='权限管理'),2 FROM DUAL WHERE NOT EXISTS (SELECT name FROM sys_menu WHERE name='功能权限');

/******************************************角色菜单权限表*****************************************/
/*创建角色菜单权限表*/
CREATE TABLE IF NOT EXISTS `sys_role_menu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色id',
  `menu_id` bigint(20) DEFAULT NULL COMMENT '菜单id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='角色的菜单权限';

INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='首页') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='首页'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='系统管理') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='系统管理'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='角色管理') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='角色管理'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='用户管理') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='用户管理'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='菜单管理') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='权限管理') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='权限管理'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='菜单权限') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限'));
INSERT INTO sys_role_menu (`role_id`,`menu_id`) SELECT (SELECT id FROM sys_role WHERE NAME='系统管理员'),(SELECT id FROM sys_menu WHERE name='功能权限') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_menu WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND menu_id=(SELECT id FROM sys_menu WHERE name='功能权限'));

/******************************************菜单权限表*****************************************/
/*如果权限表不存在就创建*/
CREATE TABLE IF NOT EXISTS `sys_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `menu_id` bigint(20) DEFAULT NULL COMMENT '对应菜单id',
  `permission` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '权限',
  `description` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='功能权限表';

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:list','用户信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:update','用户信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:insert','用户信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:delete','用户信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='角色管理'),'role:list','角色信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='角色管理'),'role:update','角色信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='角色管理'),'role:insert','角色信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='角色管理'),'role:delete','角色信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:role:list','用户角色信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:role:update','用户角色信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:role:insert','用户角色信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'user:role:delete','用户角色信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'role:permission:list','用户权限信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'role:permission:update','用户权限信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'role:permission:insert','用户权限信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='用户管理'),'role:permission:delete','用户权限信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单权限'),'role:menu:list','菜单权限信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单权限'),'role:menu:update','菜单权限信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单权限'),'role:menu:insert','菜单权限信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单权限'),'role:menu:delete','菜单权限信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='功能权限'),'permission:list','功能权限信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='功能权限'),'permission:update','功能权限信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='功能权限'),'permission:insert','功能权限信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='功能权限'),'permission:delete','功能权限信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:delete');

INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单管理'),'menu:list','菜单管理信息查看' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:list');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单管理'),'menu:update','菜单管理信息编辑' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:update');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单管理'),'menu:insert','菜单管理信息添加' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:insert');
INSERT INTO sys_permission (`menu_id`,`permission`,`description`) SELECT (SELECT id FROM sys_menu WHERE name='菜单管理'),'menu:delete','菜单管理信息删除' FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:delete');


/******************************************角色权限表*****************************************/
/*如果角色权限表不存在就创建*/
CREATE TABLE IF NOT EXISTS `sys_role_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色id',
  `permission_id` bigint(20) DEFAULT NULL COMMENT '权限id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='角色权限表';

INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:delete'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='角色管理') AND permission='role:delete'));

INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='user:role:delete'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='用户管理') AND permission='role:permission:delete'));

INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单权限') AND permission='role:menu:delete'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='功能权限') AND permission='permission:delete'));

INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:list') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:list'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:update') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:update'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:insert') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:insert'));
INSERT INTO sys_role_permission (`role_id`,`permission_id`) SELECT (SELECT id FROM sys_role WHERE name='系统管理员'),(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:delete') FROM DUAL WHERE NOT EXISTS (SELECT id FROM sys_role_permission WHERE role_id=(SELECT id FROM sys_role WHERE name='系统管理员') AND permission_id=(SELECT id FROM sys_permission WHERE menu_id=(SELECT id FROM sys_menu WHERE name='菜单管理') AND permission='menu:delete'));






/*创建船型号表*/
CREATE TABLE IF NOT EXISTS `ship_model` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `model_name` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '型号名称',
  `s_length` float DEFAULT NULL COMMENT '船长度(m)',
  `s_width` float DEFAULT NULL COMMENT '船宽度(m)',
  `s_depth` float DEFAULT NULL COMMENT '船深度(m)',
  `superstratum_height` float DEFAULT NULL COMMENT '上层建筑高度(m)',
  `ny_diameter` float DEFAULT NULL COMMENT 'NY船体直径(m)',
  `normal_tonnage` float DEFAULT NULL COMMENT '正常排水量(t)',
  `overload_tonnage` float DEFAULT NULL COMMENT '超载排水量(t)',
  `sx_tonnage` float DEFAULT NULL COMMENT 'SX排水量(t)',
  `people_amount` int(11) DEFAULT NULL COMMENT '人员编制(人)',
  `power_source` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '电源',
  `cool_water` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '冷却水',
  `air_source` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '气源',
  `hydraulic_source` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '液压源',
  `description` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '描述信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='船型号';

/*创建船型号系统表*/
CREATE TABLE  IF NOT EXISTS `ship_sys` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `ship_model_id` bigint(20) DEFAULT NULL COMMENT '船型号id',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '系统名称',
  `responsible_unit` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '责任单位',
  `mtbf` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '可靠性MTBF(h)',
  `mttr` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '维修性MTTR(h)',
  `performance` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '主要性能',
  `function` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '主要功能',
  `remark` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  `level` int(11) DEFAULT NULL COMMENT '系统等级',
  `pid` int(11) DEFAULT NULL COMMENT '父系统id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='船型号所属系统';

/*创建船型号中设备表*/
CREATE TABLE IF NOT EXISTS `ship_device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '设备名称',
  `model` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '设备型号',
  `mttr` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '维修性MTTR(h)',
  `mtbf` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '可靠性MTBF(h)',
  `length` float DEFAULT NULL COMMENT '长度(mm)',
  `width` float DEFAULT NULL COMMENT '宽度(mm)',
  `height` float DEFAULT NULL COMMENT '高度(mm)',
  `weight` float DEFAULT NULL COMMENT '重量(kg)',
  `manufacturer` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '生产厂家',
  `lifetime` float DEFAULT NULL COMMENT '寿命(年)',
  `amount` int(11) DEFAULT NULL COMMENT '数量',
  `model_id` bigint(20) DEFAULT NULL COMMENT '父级型号id',
  `sys_id` bigint(20) DEFAULT NULL COMMENT '父级系统id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='船型号所属设备';

/*创建船设备的组件表*/
CREATE TABLE IF NOT EXISTS `ship_device_component` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '组件名称',
  `model` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '组件型号',
  `mttr` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '维修性MTTR(h)',
  `mtbf` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '可靠性MTBF(h)',
  `length` float DEFAULT NULL COMMENT '长度(mm)',
  `width` float DEFAULT NULL COMMENT '宽度(mm)',
  `height` float DEFAULT NULL COMMENT '高度(mm)',
  `weight` float DEFAULT NULL COMMENT '重量(kg)',
  `manufacturer` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '生产厂家',
  `lifetime` float DEFAULT NULL COMMENT '寿命(年)',
  `amount` int(11) DEFAULT NULL COMMENT '数量',
  `device_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='设备的组件';

添加上述的配置项目可以成功启动，最直观的体现是访问Swagger:http://localhost:8085/cams_hf/swagger-ui.html
端口和项目路径是在yml文件中配置的。
image.png

项目结构

image.png

其中选中的module包下是使用MybatisPlus代码生成功能，根据数据库表结构生成的代码：

image.png

后续会讲到MybatisPlus的代码生成。

Java:SpringBoot+Jwt+Shiro+Mybati

项目背景

项目环境

项目集成

项目配置

项目结构

未完待续~~

猜你喜欢

热点阅读