SpringCloud微服务实战——搭建企业级开发框架(五):数据库持久化集成MySql+Druid+MyBatis-Plus
SpringCloud微服务实战——搭建企业级开发框架(五):数据库持久化集成MySql+Druid+MyBatis-Plus
在引入相关数据库持久化相关依赖库之前,我们可以考虑到,当我们因业务开发需要,引入各种各样的依赖库时,Jar包冲突是我们必须面对的一个问题,Spring为了解决这些Jar包的冲突,推出了各种bom,最著名的就是Spring IO Platform bom,其中最核心的三个是:spring-framework-bom、spring-boot-dependencies、platform-bom。我们这里参考Spring管理Jar包的方式,新建一个GitEgg-Platform平台工程,提供各种第三方组件的配置及自定义方法,使用子工程gitegg-platform-bom统一管理GitEgg自定义方法扩展jar包及第三方Jar包版本。
1、首先在GitEgg-Platform工程下新建gitegg-platform-db用于管理系统需要用到的数据库驱动、数据库连接池的jar包及配置,新建gitegg-platform-mybatis用于管理系统需要用到的持久层框架,建好的结构如下:
2、修改GitEgg-Platform根目录下的pom.xml,设置工程编码方式,及引入的Spring Boot,Spring Cloud,gitegg-platform-bom版本:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.gitegg.platform</groupId>
<artifactId>GitEgg-Platform</artifactId>
<name>${project.artifactId}</name>
<version>1.0-SNAPSHOT</version>
<packaging>pom</packaging>
<modules>
<module>gitegg-platform-bom</module>
<module>gitegg-platform-db</module>
<module>gitegg-platform-mybatis</module>
</modules>
<properties>
<!-- jdk版本1.8 -->
<java.version>1.8</java.version>
<!-- maven-compiler-plugin插件版本,Java代码编译 -->
<maven.plugin.version>3.8.1</maven.plugin.version>
<!-- maven编译时指定编码UTF-8 -->
<maven.compiler.encoding>UTF-8</maven.compiler.encoding>
<!-- 项目统一字符集编码UTF-8 -->
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<!-- 项目统一字符集编码UTF-8 -->
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- SpringBoot版本号 -->
<spring.boot.version>2.3.3.RELEASE</spring.boot.version>
<!-- SpringCloud版本号 -->
<spring.cloud.version>Hoxton.SR8</spring.cloud.version>
<!-- SpringPlatform版本号 -->
<spring.platform.version>Cairo-SR8</spring.platform.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-bom</artifactId>
<version>${project.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring.boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring.cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<finalName>${project.name}</finalName>
<resources>
<resource>
<directory>src/main/resources</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>${maven.plugin.version}</version>
<configuration>
<source>${java.version}</source>
<target>${java.version}</target>
<encoding>UTF-8</encoding>
<compilerArgs>
<arg>-parameters</arg>
</compilerArgs>
</configuration>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>aliyun-repos</id>
<url>https://maven.aliyun.com/nexus/content/groups/public/</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
<repository>
<id>gitegg-release</id>
<name>Release Repository</name>
<url>https://packages.aliyun.com/maven/repository/2020515-release-dpxo1j/</url>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>aliyun-plugin</id>
<url>https://maven.aliyun.com/nexus/content/groups/public/</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
<profiles>
<profile>
<id>dev</id>
<properties>
<profileActive>dev</profileActive>
</properties>
<activation>
<!--默认为dev环境打包方式-->
<activeByDefault>true</activeByDefault>
</activation>
</profile>
<profile>
<id>test</id>
<properties>
<profileActive>test</profileActive>
</properties>
</profile>
<profile>
<id>pro</id>
<properties>
<profileActive>pro</profileActive>
</properties>
</profile>
</profiles>
</project>
3、修改gitegg-platform-bom工程下的pom.xml,引入目前需要的数据库驱动,数据连接池及Mybatis-Plus:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.3.RELEASE</version>
<relativePath />
</parent>
<modelVersion>4.0.0</modelVersion>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-bom</artifactId>
<name>${project.artifactId}</name>
<version>${gitegg.project.version}</version>
<packaging>pom</packaging>
<properties>
<!-- jdk版本1.8 -->
<java.version>1.8</java.version>
<!-- maven-compiler-plugin插件版本,Java代码编译 -->
<maven.plugin.version>3.8.1</maven.plugin.version>
<!-- maven编译时指定编码UTF-8 -->
<maven.compiler.encoding>UTF-8</maven.compiler.encoding>
<!-- 项目统一字符集编码UTF-8 -->
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<!-- 项目统一字符集编码UTF-8 -->
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- GitEgg项目统一设置版本号 -->
<gitegg.project.version>1.0-SNAPSHOT</gitegg.project.version>
<!-- mysql数据库驱动 -->
<mysql.connector.version>8.0.17</mysql.connector.version>
<!-- postgresql数据库驱动 -->
<postgresql.connector.version>9.1-901.jdbc4</postgresql.connector.version>
<!-- 数据库连接池Druid -->
<druid.version>1.1.23</druid.version>
<!-- Mybatis Plus增强工具 -->
<mybatis.plus.version>3.4.0</mybatis.plus.version>
</properties>
<dependencyManagement>
<dependencies>
<!-- gitegg数据库驱动及连接池 -->
<dependency>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-db</artifactId>
<version>${gitegg.project.version}</version>
</dependency>
<!-- gitegg mybatis-plus -->
<dependency>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-mybatis</artifactId>
<version>${gitegg.project.version}</version>
</dependency>
<!-- mysql数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.connector.version}</version>
</dependency>
<!-- postgresql数据库驱动 -->
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>${postgresql.connector.version}</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- Mybatis Plus增强工具 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
</project>
4、修改gitegg-platform-db工程下的pom.xml,只引入数据库驱动及数据库连接池相关jar包:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>GitEgg-Platform</artifactId>
<groupId>com.gitegg.platform</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>gitegg-platform-db</artifactId>
<name>${project.artifactId}</name>
<version>${project.parent.version}</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
</dependencies>
</project>
5、修改gitegg-platform-mybatis工程下的pom.xml,只引入mybatis-plus相关jar包:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>GitEgg-Platform</artifactId>
<groupId>com.gitegg.platform</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>gitegg-platform-mybatis</artifactId>
<name>${project.artifactId}</name>
<version>${project.parent.version}</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
</dependencies>
</project>
6、pom.xml文件配置好之后,在IDEA右侧窗口,Maven中点击install,将包安装到本地,这样GitEgg-Cloud工程就可以引用GitEgg-Platform工程的jar包了,同理,测试及正式环境需要点击deploy,将jar包发布到测试的Maven私服,或者正式环境的Maven私服。
7、回到GitEgg-Cloud项目,在gitegg-service下的pom.xml里面引入gitegg-platform-db和gitegg-platform-mybatis
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>GitEgg-Cloud</artifactId>
<groupId>com.gitegg.cloud</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>gitegg-service</artifactId>
<packaging>pom</packaging>
<modules>
<module>gitegg-service-base</module>
<module>gitegg-service-bigdata</module>
<module>gitegg-service-system</module>
</modules>
<dependencies>
<!-- gitegg数据库驱动及连接池 -->
<dependency>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-db</artifactId>
</dependency>
<!-- gitegg mybatis-plus -->
<dependency>
<groupId>com.gitegg.platform</groupId>
<artifactId>gitegg-platform-mybatis</artifactId>
</dependency>
<!-- spring boot web核心包 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- spring boot 健康监控 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
</dependencies>
</project>
8、在gitegg-service-system工程下修改application.yml,增加数据库连接和mybatis的配置:
server:
port: 8001
spring:
application:
name: gitegg-service-system
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1/gitegg_cloud?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&autoReconnect=true
username: root
password: root
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: config,stat,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
mybatis-plus:
mapper-locations: classpath*:/com/gitegg/*/*/mapper/*Mapper.xml
typeAliasesPackage: com.gitegg.*.*.entity
global-config:
#主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
id-type: 2
#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
field-strategy: 2
#驼峰下划线转换
db-column-underline: true
#刷新mapper 调试神器
refresh-mapper: true
#数据库大写下划线转换
#capital-mode: true
#逻辑删除配置
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
map-underscore-to-camel-case: true
cache-enabled: false
9、修改GitEggSystemApplication,添加要扫描的mapper路径声明:
package com.gitegg.service.system;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* gitegg-system 启动类
*/
@MapperScan("com.gitegg.*.*.mapper")
@SpringBootApplication
public class GitEggSystemApplication {
public static void main(String[] args) {
SpringApplication.run(GitEggSystemApplication.class,args);
}
}
10、修改GitEgg-Platform工程中的gitegg-platform-db和gitegg-platform-mybatis,新增mybatis-plus分页配置,和Druid数据库连接配置,此配置类预留,后面需要自定义修改。
- DruidConfig.java内容如下:
package com.gitegg.platform.db.config;
import org.springframework.context.annotation.Configuration;
@Configuration
public class DruidConfig {
}
- MybatisPlusConfig.java内容如下:
package com.gitegg.platform.mybatis.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@MapperScan("com.gitegg.**.mapper.**")
public class MybatisPlusConfig {
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false
* 避免缓存出现问题(该属性会在旧插件移除后一同移除)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
11、增加测试类,通过访问controller->service->dao层,获取数据库数据。新建controller、service、impl、mapper、entity、dto包,这些包和类,后面可以用系统自动生成,不需要每次都自己手动建立。
- SystemController.java文件内容:
package com.gitegg.service.system.controller;
import com.gitegg.service.system.service.ISystemService;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping(value = "system")
@AllArgsConstructor
public class SystemController {
private final ISystemService systemService;
@GetMapping(value = "list")
public Object list() {
return systemService.list();
}
@GetMapping(value = "page")
public Object page() {
return systemService.page();
}
}
- ISystemService.java文件内容:
package com.gitegg.service.system.service;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gitegg.service.system.entity.SystemTable;
import java.util.List;
public interface ISystemService {
List<SystemTable> list();
Page<SystemTable> page();
}
- SystemServiceImpl.java 内容:
package com.gitegg.service.system.service.impl;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gitegg.service.system.entity.SystemTable;
import com.gitegg.service.system.mapper.SystemTableMapper;
import com.gitegg.service.system.service.ISystemService;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
*
*/
@Service
@AllArgsConstructor
public class SystemServiceImpl implements ISystemService {
private final SystemTableMapper systemTableMapper;
@Override
public List<SystemTable> list() {
return systemTableMapper.list();
}
@Override
public Page<SystemTable> page() {
Page<SystemTable> page = new Page<>(1, 10);
List<SystemTable> records = systemTableMapper.page(page);
page.setRecords(records);
return page;
}
}
- SystemTableMapper.java内容:
package com.gitegg.service.system.mapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gitegg.service.system.entity.SystemTable;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface SystemTableMapper {
List<SystemTable> list();
List<SystemTable> page(Page<SystemTable> page);
}
- SystemTable.java内容:
package com.gitegg.service.system.entity;
import lombok.Data;
@Data
public class SystemTable {
private Long id;
private String name;
}
- SystemTableMapper.xml内容:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.gitegg.service.system.mapper.SystemTableMapper" >
<select id="list" resultType="com.gitegg.service.system.entity.SystemTable">
select * from system_table
</select>
<select id="page" resultType="com.gitegg.service.system.entity.SystemTable">
select * from system_table
</select>
</mapper>
12、运行GitEggSystemApplication,在控制台看是否启动成功,如果启动成功,在浏览器中分别访问http://127.0.0.1:8001/system/list和http://127.0.0.1:8001/system/page,可以看到数据里面的数据: