1, Database design
1. Database
xii_education
2. Data sheet
I put the database file on Baidu network disk
Link: https://pan.baidu.com/s/1e2Av81Ib14lpkhy5Feic8g
Extraction code: wxih
2, Database design specification
The following regulations are only for this module. For more comprehensive documents, please refer to Alibaba Java Development Manual: v. MySQL database
1. The library name should be consistent with the application name as much as possible
2. Table names and field names must use lowercase letters or numbers, and the beginning of numbers is prohibited,
3. Plural nouns are not used in table names
4. It is better to add "business name table function" to the name of the table. For example, edu_teacher
5. Three required fields in the table: id, gmt_create, gmt_modified
explain:
The id must be the primary key, the type is bigint unsigned, and the step size is 1.
(if the cluster deployment by database and table is used, the id type is verchar, which is not self increment, and the distributed id generator is used in the business)
gmt_ create, gmt_ The type of modified is datetime. The former current tense indicates active creation, and the latter past participle indicates passive update.
6. Only when the number of rows in a single table exceeds 5 million or the capacity of a single table exceeds 2GB, it is recommended to divide the database and table. Note: if the estimated data volume in three years cannot reach this level at all, please do not divide databases and tables when creating tables.
7. The field expressing the concept of yes or no must use is_xxx. The data type is unsigned tinyint (1 means yes, 0 means no).
Note: if any field is non negative, it must be unsigned.
Note: any boolean variable in POJO class should not be prefixed with is. The database indicates the value of yes or no, using tinyint type and adhering to is_ The naming method of XXX is to clarify its value meaning and value range.
Positive example: field name is expressing logical deletion_ Deleted, 1 means deleted, 0 means not deleted.
8. The decimal type is decimal, and float and double are prohibited. Note: when storing float and double, there is a problem of accuracy loss. It is likely to get incorrect results when comparing values. If the stored data range exceeds the decimal range, it is recommended to split the data into integers and decimals and store them separately.
9. If the stored strings are almost equal in length, use the char fixed length string type.
10. varchar is a variable length string. It does not pre allocate storage space, and the length should not exceed 5000. If the storage length is greater than this value, define the field type as text, and separate a table to correspond with the primary key to avoid affecting the index efficiency of other fields.
11. The unique index name is uk_ Field name; The normal index name is idx_ Field name.
Description: uk_ unique key; idx_ The abbreviation of index
12. Foreign keys and cascading are not allowed. All foreign key concepts must be solved at the application layer. Foreign keys and cascaded updates are suitable for single machine low concurrency, not for distributed and high concurrency clusters; Cascade update is a strong blocking, and there is a risk of database update storm; Foreign keys affect the insertion speed of the database.
3, Introduction to engineering structure
1. General structure of the project
2. Module description
guli-parent: The online teaching root directory (parent project) manages four sub modules:
canal-client: canal Database table synchronization module (statistical synchronization data)
common: Common module parent node
common-util: Tool class module. All modules can depend on it
service-base: service Service base Package, including service Public configuration class of service, all service The module depends on it
spring-security: Authentication and authorization module, which requires authentication and authorization service The service depends on it
infrastructure: Parent node of basic service module
api-gateway: api Gateway service
service: api Interface service parent node
service-acl: User rights management api Interface services (user management, role management, authority management, etc.)
service-cms: cms api Interface service
service-edu: Teaching related api Interface service
service-msm: short message api Interface service
service-order: Order related api Interface service
service-oss: Alibaba cloud oss api Interface service
service-statistics: Statistical report api Interface service
service-ucenter: member api Interface service
service-vod: video on demand api Interface service
4, Create parent project
1. Create sprigboot project
In the idea development tool, use Spring Initializr to quickly initialize a Spring Boot module. Version: 2.2.1 RELEASE
2. Delete src directory
3. Configure POM xml
Revised version: 2.2.1 RELEASE
Add pom type after node
<artifactId>xii-parent</artifactId> <packaging>pom</packaging>
4. In POM Add dependent versions to XML
Delete POM Content in XML
<!-- Delete the following -->
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
Add a dependent version
<properties>
<java.version>1.8</java.version>
<guli.version>0.0.1-SNAPSHOT</guli.version>
<mybatis-plus.version>3.0.5</mybatis-plus.version>
<velocity.version>2.0</velocity.version>
<swagger.version>2.7.0</swagger.version>
<aliyun.oss.version>2.8.3</aliyun.oss.version>
<jodatime.version>2.10.1</jodatime.version>
<poi.version>3.17</poi.version>
<commons-fileupload.version>1.3.1</commons-fileupload.version>
<commons-io.version>2.6</commons-io.version>
<httpclient.version>4.5.1</httpclient.version>
<jwt.version>0.7.0</jwt.version>
<aliyun-java-sdk-core.version>4.3.3</aliyun-java-sdk-core.version>
<aliyun-sdk-oss.version>3.1.0</aliyun-sdk-oss.version>
<aliyun-java-sdk-vod.version>2.15.2</aliyun-java-sdk-vod.version>
<aliyun-java-vod-upload.version>1.4.11</aliyun-java-vod-upload.version>
<aliyun-sdk-vod-upload.version>1.4.11</aliyun-sdk-vod-upload.version>
<fastjson.version>1.2.28</fastjson.version>
<gson.version>2.8.2</gson.version>
<json.version>20170516</json.version>
<commons-dbutils.version>1.7</commons-dbutils.version>
<canal.client.version>1.1.0</canal.client.version>
<docker.image.prefix>zx</docker.image.prefix>
<cloud-alibaba.version>0.2.2.RELEASE</cloud-alibaba.version>
</properties>
Locked version of < dependencymanagement >
<dependencyManagement>
<dependencies>
<!--Spring Cloud-->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>Hoxton.RELEASE</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-alibaba-dependencies</artifactId>
<version>${cloud-alibaba.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<!--mybatis-plus Persistent layer-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<!-- velocity template engine, Mybatis Plus Code generator needs -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>${velocity.version}</version>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>${swagger.version}</version>
</dependency>
<!--swagger ui-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>${swagger.version}</version>
</dependency>
<!--aliyunOSS-->
<dependency>
<groupId>com.aliyun.oss</groupId>
<artifactId>aliyun-sdk-oss</artifactId>
<version>${aliyun.oss.version}</version>
</dependency>
<!--Date time tool-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>${jodatime.version}</version>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!--File upload-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>${commons-fileupload.version}</version>
</dependency>
<!--commons-io-->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>${commons-io.version}</version>
</dependency>
<!--httpclient-->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>${httpclient.version}</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>${gson.version}</version>
</dependency>
<!-- JWT -->
<dependency>
<groupId>io.jsonwebtoken</groupId>
<artifactId>jjwt</artifactId>
<version>${jwt.version}</version>
</dependency>
<!--aliyun-->
<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-java-sdk-core</artifactId>
<version>${aliyun-java-sdk-core.version}</version>
</dependency>
<dependency>
<groupId>com.aliyun.oss</groupId>
<artifactId>aliyun-sdk-oss</artifactId>
<version>${aliyun-sdk-oss.version}</version>
</dependency>
<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-java-sdk-vod</artifactId>
<version>${aliyun-java-sdk-vod.version}</version>
</dependency>
<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-java-vod-upload</artifactId>
<version>${aliyun-java-vod-upload.version}</version>
</dependency>
<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-sdk-vod-upload</artifactId>
<version>${aliyun-sdk-vod-upload.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>${json.version}</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>${commons-dbutils.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba.otter</groupId>
<artifactId>canal.client</artifactId>
<version>${canal.client.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
5, Build service module
1. Create a module service under the parent project XII parent
Select maven type and click next
The added module type is pom
Add pom type after node
<artifactId>service</artifactId> <packaging>pom</packaging>
Add dependencies required by the project
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-ribbon</artifactId>
</dependency>
<!--hystrix Dependence, mainly with @HystrixCommand -->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-hystrix</artifactId>
</dependency>
<!--Service registration-->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
</dependency>
<!--Service call-->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-openfeign</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- velocity template engine, Mybatis Plus Code generator needs -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
</dependency>
<!--lombok Used to simplify entity classes: need to install lombok plug-in unit-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
</dependency>
<!--httpclient-->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
</dependency>
<!--commons-io-->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
</dependency>
<!--gson-->
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
These dependencies can't be annotated for the time being to prevent problems
6, Build service edu module
1. Create a sub module service edu under the parent project service module
7, Instructor management module configuration
1. Create a configuration file in the service edu module under service
Create the file application. In the resources directory properties
# Service port server.port=8001 # service name spring.application.name=service-edu # Environment settings: dev, test, prod spring.profiles.active=dev # mysql database connection spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/guli?serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=root #mybatis log mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
Create MP code generator
Create the package com. In the test/java directory atguigu. Eduservice, create code generator: codegenerator java
package com.xh.Generator;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import org.junit.Test;
/**
* @author
* @since 2018/12/13
*/
public class CodeGenerator {
@Test
public void run() {
// 1. Create code generator
AutoGenerator mpg = new AutoGenerator();
// 2. Global configuration
GlobalConfig gc = new GlobalConfig();
String projectPath = System.getProperty("user.dir");
// gc.setOutputDir(projectPath + "/src/main/java");
gc.setOutputDir("E:\\IDEA\\project\\My project\\xii_education\\service\\service_edu" + "/src/main/java");
gc.setAuthor("xhjava");
gc.setOpen(false); //Open Explorer after build
gc.setFileOverride(false); //Whether the file is overwritten during regeneration
gc.setServiceName("%sService"); //Remove the initial I of the Service interface
gc.setIdType(IdType.ID_WORKER_STR); //Primary key policy
gc.setDateType(DateType.ONLY_DATE);//Defines the date type in the generated entity class
gc.setSwagger2(true);//Turn on Swagger2 mode
mpg.setGlobalConfig(gc);
// 3. Data source configuration
DataSourceConfig dsc = new DataSourceConfig();
dsc.setUrl("jdbc:mysql://localhost:3306/xii_education?serverTimezone=GMT%2B8");
dsc.setDriverName("com.mysql.cj.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("1214521");
dsc.setDbType(DbType.MYSQL);
mpg.setDataSource(dsc);
// 4. Package configuration
PackageConfig pc = new PackageConfig();
pc.setModuleName("eduservice"); //Module name
pc.setParent("com.xii");
pc.setController("controller");
pc.setEntity("entity");
pc.setService("service");
pc.setMapper("mapper");
mpg.setPackageInfo(pc);
// 5. Policy configuration
StrategyConfig strategy = new StrategyConfig();
strategy.setInclude("edu_teacher");
strategy.setNaming(NamingStrategy.underline_to_camel);//Naming strategy for mapping database tables to entities
strategy.setTablePrefix(pc.getModuleName() + "_"); //Remove the table prefix when generating entities
strategy.setColumnNaming(NamingStrategy.underline_to_camel);//Naming policy for mapping database table fields to entities
strategy.setEntityLombokModel(true); // lombok model @ accessories (chain = true) setter chain operation
strategy.setRestControllerStyle(true); //restful api style controller
strategy.setControllerMappingHyphenStyle(true); //Hump to hyphen in url
mpg.setStrategy(strategy);
// 6. Execute
mpg.execute();
}
}
8, Write background management api interface
1. Write controller code
@Autowired
private TeacherService teacherService;
@GetMapping("findAll")
public List<Teacher> list(){
return teacherService.list(null);
}
2. Create SpringBoot configuration class
Create config package under edu package and create mybatisplusconfig java
package com.guli.edu.config;
@Configuration
@EnableTransactionManagement
@MapperScan("com.atguigu.eduservice.mapper")
public class MyBatisPlusConfig {
}
3. Configure SQL execution performance analysis plug-in
/**
* SQL Execute performance analysis plug-in
* It is not recommended to use the development environment online. maxTime refers to the maximum execution time of sql
*/
@Bean
@Profile({"dev","test"})// Set dev test environment on
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setMaxTime(1000);//ms, beyond the ms set here, sql will not be executed
performanceInterceptor.setFormat(true);
return performanceInterceptor;
}
4. Create SpringBoot startup class
Start the eduapplication class Java, pay attention to the creation location of the startup class
@SpringBootApplication
public class EduApplication {
public static void main(String[] args) {
SpringApplication.run(EduApplication.class, args);
}
}
5. Run startup class
visit http://localhost:8001/eduservice/teacher
Get json data
6. Unified returned json time format
By default, json time format has time zone and is world standard time, which is eight hours away from our time
In application Set in properties
#Returns the global time format of json spring.jackson.date-format=yyyy-MM-dd HH:mm:ss spring.jackson.time-zone=GMT+8
9, Instructor logical deletion function
1. EduTeacherController add delete method
@DeleteMapping("{id}")
public boolean removeById(@PathVariable String id){
return teacherService.removeById(id);
}
2. Configure logical deletion plug-in
Configuration in MyBatisPlusConfig
/**
* Logical deletion plug-in
*/
@Bean
public ISqlInjector sqlInjector() {
return new LogicSqlInjector();
}
And add a TableLogic annotation (logical deletion) to the used field
3. Test deletion using postman
Test result: is in database_ The deleted field is modified to 1
Swagger2 can also be used for port viewing, which is relatively more widely used
10, Swagger2 introduction
api document is the best way to communicate in the development mode of front-end and back-end separation.
Swagger is a normative and complete framework for generating, describing, invoking and visualizing RESTful Web services.
- Timeliness (timely and accurately notify relevant front and rear end developers after interface changes)
- Standardization (and ensure the standardization of the interface, such as interface address, request mode, parameters, response format and error information)
- Consistency (the interface information is consistent, and there will be no disagreement due to the inconsistent version of the document obtained by the developer)
- Testability (test directly on the interface document to facilitate business understanding)
Configure Swagger2
1. Create common module
Create module common under Guli parent
2. Introduce related dependencies in common
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<scope>provided </scope>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<scope>provided </scope>
</dependency>
<!--lombok Used to simplify entity classes: need to install lombok plug-in unit-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided </scope>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<scope>provided </scope>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<scope>provided </scope>
</dependency>
<!-- redis -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!-- spring2.X integrate redis what is needed common-pool2
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.6.0</version>
</dependency>-->
</dependencies>
3. Create a sub module service base under common. In the module service base, create the configuration class of swagger
Create package com atguigu. servicebase. Config, create the class SwaggerConfig
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket webApiConfig(){
return new Docket(DocumentationType.SWAGGER_2)
.groupName("webApi")
.apiInfo(webApiInfo())
.select()
.paths(Predicates.not(PathSelectors.regex("/admin/.*")))
.paths(Predicates.not(PathSelectors.regex("/error.*")))
.build();
}
private ApiInfo webApiInfo(){
return new ApiInfoBuilder()
.title("website-Course Center API file")
.description("This document describes the definition of course center microservice interface")
.version("1.0")
.contact(new Contact("Helen", "http://atguigu.com", "55317332@qq.com"))
.build();
}
}
4. Introduce service base into service module
<dependency>
<groupId>com.atguigu</groupId>
<artifactId>service-base</artifactId>
<version>0.0.1-SNAPSHOT</version>
</dependency>
5. Add comments on the service edu startup class for testing
6. API model
You can add some custom settings, such as:
Define sample data
@ApiModelProperty(value = "Creation time", example = "2019-01-01 8:00:00") @TableField(fill = FieldFill.INSERT) private Date gmtCreate; @ApiModelProperty(value = "Update time", example = "2019-01-01 8:00:00") @TableField(fill = FieldFill.INSERT_UPDATE) private Date gmtModified;
Define interface description and parameter description (supplementary)
Defined on class: @ Api
Defined on method: @ ApiOperation
Defined on parameters: @ ApiParam
@Api(description="Instructor management")
@RestController
@RequestMapping("/admin/edu/teacher")
public class TeacherAdminController {
@Autowired
private TeacherService teacherService;
@ApiOperation(value = "List of all instructors")
@GetMapping
public List<Teacher> list(){
return teacherService.list(null);
}
@ApiOperation(value = "according to ID Delete instructor")
@DeleteMapping("{id}")
public boolean removeById(
@ApiParam(name = "id", value = "lecturer ID", required = true)
@PathVariable String id){
return teacherService.removeById(id);
}
}
Execute in swagger UI Use in HTML
Pass parameters. This is the delete request.