Java:SpringBoot+Jwt+Shiro+MybatisPlus+Swagger集成

  • 基于公司业务和工作实践，在此记录项目中的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='设备的组件';

项目结构

image.png

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


image.png

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

未完待续~~

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 201,681评论 5赞 474
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,710评论 2赞 377
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 148,623评论 0赞 334
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,202评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,232评论 5赞 363
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,368评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,795评论 3赞 393
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,461评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,647评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,476评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,525评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,226评论 3赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,785评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,857评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,090评论 1赞 258
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,647评论 2赞 348
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,215评论 2赞 341