SpringBoot3.x+MyBatisPlus+druid多数据源配置

1 引言

本章主要介绍SpringBoot3.x多数据源配置，以及在此基础上配置分页拦截，自动填充功等功能，源码链接在文章最后。下面列出几个重要文件进行介绍。

2 项目结构

整体项目结构如下，主要介绍配置文件和配置类。

3 主要代码

3.1 pom.xml

注意SpringBoot3.x对应依赖为mybatis-plus-spring-boot3-starte和druid-spring-boot-3-starter。

<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.zeroone</groupId>
    <artifactId>zeroone</artifactId>
    <version>1.0</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <!--  为Spring Boot项目提供一系列默认的配置和依赖管理-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.2</version>
        <relativePath/>
    </parent>

    <dependencies>
        <!--  Spring Boot核心依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!-- Spring Boot单元测试和集成测试的依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot构建Web应用程序的依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- mysql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>
        <!-- mybatis-plus核心依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.7</version>
        </dependency>
        <!-- 阿里数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>1.2.23</version>
        </dependency>


    </dependencies>

</project>

3.2 application.yml

server:
  port: 8080 # 服务器的HTTP端口，默认为8080
  tomcat:
    uri-encoding: UTF-8 # tomcat的URI编码
    accept-count: 1000 # 连接数满后的排队数，默认为100
    threads:
      max: 800 # tomcat最大线程数，默认为200
      min-spare: 100 # Tomcat启动初始化的线程数，默认值10

# Spring配置
spring:
  servlet:
    multipart:
      max-file-size: 10MB  # 单个文件大小
      max-request-size: 20MB # 设置总上传的文件大小
  # 数据源配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      master: # 主库数据源
        url: jdbc:mysql://127.0.0.1:33066/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: 123456
        initialSize: 5 # 初始连接数
        minIdle: 10 # 最小连接池数量
        maxActive: 20 # 最大连接池数量
        maxWait: 60000 # 配置获取连接等待超时的时间
        connectTimeout: 30000 # 配置连接超时时间
        socketTimeout: 60000 # 配置网络超时时间
        timeBetweenEvictionRunsMillis: 60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        minEvictableIdleTimeMillis: 300000 # 配置一个连接在池中最小生存的时间，单位是毫秒
        maxEvictableIdleTimeMillis: 900000 # 配置一个连接在池中最大生存的时间，单位是毫秒
      slave:  # 从库数据源
        enabled: true # 从数据源开关/默认关闭
        url: jdbc:mysql://127.0.0.1:33067/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: 123456
      validationQuery: SELECT 1 FROM DUAL # 配置检测连接是否有效
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      webStatFilter:
        enabled: true
        url-pattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
      statViewServlet:
        enabled: true
        allow:
        url-pattern: /druid/* # 设置白名单，不填则允许所有访问
        login-username: admin  # 控制台管理用户名和密码
        login-password: 123456
      filter:
        stat:
          enabled: true
          log-slow-sql: true # 慢SQL记录
          slow-sql-millis: 1000
          merge-sql: true
        wall:
          config:
            multi-statement-allow: true

# MyBatis配置
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.3 DBMaster.java

这个是主库配置，注意自动填充功能、拦截器功能、application.yml配置均需要在构建SqlSessionFactory 的时候手动添加进去，否则是不生效的。

package com.zeroone.config.database;

import com.alibaba.druid.spring.boot3.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Date;


@Configuration
@MapperScan(basePackages = "com.zeroone.mapper.master", sqlSessionTemplateRef = "sqlSessionTemplateMaster")
public class DBMaster {

    Logger log = LoggerFactory.getLogger(getClass());

    /**
     * MyBatis-Plus自动填充功能
     */
    static class MetaObjectHandlerImplMaster implements MetaObjectHandler {
        @Override
        public void insertFill(MetaObject metaObject) {
            this.setFieldValByName("createTime", new Date(), metaObject);
            this.setFieldValByName("updateTime", new Date(), metaObject);
            this.setFieldValByName("version", Integer.valueOf("0"), metaObject);
        }

        @Override
        public void updateFill(MetaObject metaObject) {
            this.setFieldValByName("updateTime", new Date(), metaObject);
        }
    }

    /**
     * MyBatis-Plus拦截器
     *
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptorMaster() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页拦截器
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //添加乐观锁拦截器
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }


    /**
     * 读取MyBatis-Plus配置
     *
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.global-config")
    public GlobalConfig globalConfigMaster() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new MetaObjectHandlerImplMaster());
        return globalConfig;
    }

    /**
     * 读取MyBatis-Plus配置文件
     *
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.configuration")
    public MybatisConfiguration mybatisConfigurationMaster() {
        return new MybatisConfiguration();
    }


    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource dataSourceMaster() {
        return DruidDataSourceBuilder.create().build();
    }


    @Primary
    @Bean
    public SqlSessionFactory sqlSessionFactoryMaster() throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setConfiguration(mybatisConfigurationMaster());
        bean.setGlobalConfig(globalConfigMaster());//加载MyBatis-Plus配置
        bean.setDataSource(dataSourceMaster());//加载MyBatis-Plus配置
        bean.setPlugins(mybatisPlusInterceptorMaster());//加载MyBatis-Plus拦截器
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean
    public SqlSessionTemplate sqlSessionTemplateMaster() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryMaster());
    }

    @Primary
    @Bean
    public DataSourceTransactionManager transactionManagerMaster() {
        return new DataSourceTransactionManager(dataSourceMaster());
    }


}

3.4 DBSlave.java.java

这个是从库配置，配置方法与主库相同，注意@bean方法的名称不能与主库配置相同。

package com.zeroone.config.database;

import com.alibaba.druid.spring.boot3.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Date;


@Configuration
@MapperScan(basePackages = "com.zeroone.mapper.slave", sqlSessionTemplateRef = "sqlSessionTemplateSlave")
public class DBSlave {

    Logger log = LoggerFactory.getLogger(getClass());

    /**
     * MyBatis-Plus自动填充功能
     */
    static class MetaObjectHandlerImplSlave implements MetaObjectHandler {
        @Override
        public void insertFill(MetaObject metaObject) {
            this.setFieldValByName("createTime", new Date(), metaObject);
            this.setFieldValByName("updateTime", new Date(), metaObject);
            this.setFieldValByName("version", Integer.valueOf("0"), metaObject);
        }

        @Override
        public void updateFill(MetaObject metaObject) {
            this.setFieldValByName("updateTime", new Date(), metaObject);
        }
    }

    /**
     * MyBatis-Plus拦截器
     *
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptorSlave() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页拦截器
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //添加乐观锁拦截器
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }


    /**
     * 读取MyBatis-Plus配置
     *
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.global-config")
    public GlobalConfig globalConfigSlave() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new MetaObjectHandlerImplSlave());
        return globalConfig;
    }

    /**
     * 读取MyBatis-Plus配置文件
     *
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.configuration")
    public MybatisConfiguration mybatisConfigurationSlave() {
        return new MybatisConfiguration();
    }


    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
    public DataSource dataSourceSlave() {
        return DruidDataSourceBuilder.create().build();
    }


    @Bean
    public SqlSessionFactory sqlSessionFactorySlave() throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setConfiguration(mybatisConfigurationSlave());
        bean.setGlobalConfig(globalConfigSlave());//加载MyBatis-Plus配置
        bean.setDataSource(dataSourceSlave());//加载MyBatis-Plus配置
        bean.setPlugins(mybatisPlusInterceptorSlave());//加载MyBatis-Plus拦截器
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/*.xml"));
        return bean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplateSlave() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactorySlave());
    }

    @Bean
    public DataSourceTransactionManager transactionManagerSlave() {
        return new DataSourceTransactionManager(dataSourceSlave());
    }
}

4 测试

启动项目后访问：http://localhost:8080/druid/index.html，账号/密码：admin/123456。可以看到druid监控页面。

• 其中有两个数据源，跟我们的配置一致。
  - 然后随意调用UserController中的接口，并查看sql监控。
  

5 源码

Gitee代码链接