- 基于公司业务和工作实践,在此记录项目中的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文件中配置的。
项目结构
其中选中的module包下是使用MybatisPlus代码生成功能,根据数据库表结构生成的代码:
后续会讲到MybatisPlus的代码生成。