SpringCloud微服务实战——搭建企业级开发框架（五）：数据库持久化集成MySql+Druid+MyBatis-Plus

举报
全栈程序猿 发表于 2023/07/10 15:12:08 2023/07/10
【摘要】   在引入相关数据库持久化相关依赖库之前，我们可以考虑到，当我们因业务开发需要，引入各种各样的依赖库时，Jar包冲突是我们必须面对的一个问题，Spring为了解决这些Jar包的冲突，推出了各种bom，最著名的就是Spring IO Platform bom，其中最核心的三个是：spring-framework-bom、spring-boot-dependencies、platform-bom...

  在引入相关数据库持久化相关依赖库之前，我们可以考虑到，当我们因业务开发需要，引入各种各样的依赖库时，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用于管理系统需要用到的持久层框架，建好的结构如下：

image.png

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私服。

image.png

image.png

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，可以看到数据里面的数据：

image.png

image.png

image.png

本文源码在https://gitee.com/wmz1930/GitEgg的chapter-05分支。

GitEgg-Cloud是一款基于SpringCloud整合搭建的企业级微服务应用开发框架，开源项目地址:

Gitee: https://gitee.com/wmz1930/GitEgg

GitHub: https://github.com/wmz1930/GitEgg

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MyBatis 微服务 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入