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端口,默认为8080tomcat:uri-encoding: UTF-8 # tomcat的URI编码accept-count: 1000 # 连接数满后的排队数,默认为100threads:max: 800 # tomcat最大线程数,默认为200min-spare: 100 # Tomcat启动初始化的线程数,默认值10# Spring配置
spring:servlet:multipart:max-file-size: 10MB # 单个文件大小max-request-size: 20MB # 设置总上传的文件大小# 数据源配置datasource:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverdruid:master: # 主库数据源url: jdbc:mysql://127.0.0.1:33066/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: 123456initialSize: 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%2B8username: rootpassword: 123456validationQuery: SELECT 1 FROM DUAL # 配置检测连接是否有效testWhileIdle: truetestOnBorrow: falsetestOnReturn: falsewebStatFilter:enabled: trueurl-pattern: /*exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'statViewServlet:enabled: trueallow:url-pattern: /druid/* # 设置白名单,不填则允许所有访问login-username: admin # 控制台管理用户名和密码login-password: 123456filter:stat:enabled: truelog-slow-sql: true # 慢SQL记录slow-sql-millis: 1000merge-sql: truewall: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 {@Overridepublic void insertFill(MetaObject metaObject) {this.setFieldValByName("createTime", new Date(), metaObject);this.setFieldValByName("updateTime", new Date(), metaObject);this.setFieldValByName("version", Integer.valueOf("0"), metaObject);}@Overridepublic void updateFill(MetaObject metaObject) {this.setFieldValByName("updateTime", new Date(), metaObject);}}/*** MyBatis-Plus拦截器**/@Beanpublic 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@Beanpublic 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@Beanpublic SqlSessionTemplate sqlSessionTemplateMaster() throws Exception {return new SqlSessionTemplate(sqlSessionFactoryMaster());}@Primary@Beanpublic 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 {@Overridepublic void insertFill(MetaObject metaObject) {this.setFieldValByName("createTime", new Date(), metaObject);this.setFieldValByName("updateTime", new Date(), metaObject);this.setFieldValByName("version", Integer.valueOf("0"), metaObject);}@Overridepublic void updateFill(MetaObject metaObject) {this.setFieldValByName("updateTime", new Date(), metaObject);}}/*** MyBatis-Plus拦截器**/@Beanpublic 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();}@Beanpublic 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();}@Beanpublic SqlSessionTemplate sqlSessionTemplateSlave() throws Exception {return new SqlSessionTemplate(sqlSessionFactorySlave());}@Beanpublic DataSourceTransactionManager transactionManagerSlave() {return new DataSourceTransactionManager(dataSourceSlave());}
}
4 测试
启动项目后访问:http://localhost:8080/druid/index.html,账号/密码:admin/123456。可以看到druid监控页面。
- 其中有两个数据源,跟我们的配置一致。
- 然后随意调用UserController中的接口,并查看sql监控。
5 源码
Gitee代码链接