前言
最近因为业务需要,在项目中使用了多数据源,写这篇博客的目的是给自己留一个记录,同时也分享给有需要的同学。下面的内容是我一贯的风格以实践为主,理论部分可以自己在网上查阅。
逻辑说明
下图为Demo工程的逻辑示意图,搭建多数据源的主要问题在于事务的配置和管理,幸运的是spring本身支持多事务,在工程中可以为每个数据源单独配置一个事务,但使用时需要显示声明属于哪个具体事务,在工程中同一个service内不同的方法可以运行在不同的事务中,各自独立运行互不干扰。
示意图
实践
本Demo基于springboot 2.1.1,集成了mybatis-plus和druid,数据库使用mysql。以下仅列出关键部分有需要全部代码的可以通过此链接https://gitee.com/microapps/multiple-data-sources/tree/v1.0.0下载查看。
(1)pom.xml
<?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>microapps.com.cn</groupId>
<artifactId>multiSource</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.1.RELEASE</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
</project>
(2)application.yml
配置文件的内容虽然看起来很多但真正重要的是one和two两个数据源的定义方式,其他内容为druid连接池的配置。
server:
port: 8080
spring:
datasource:
one:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间,单位是毫秒
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
# 用来检测连接是否有效的sql
validationQuery: select 'x'
# 申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testWhileIdle: true
# 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testOnBorrow: false
# 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
two:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间,单位是毫秒
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
# 用来检测连接是否有效的sql
validationQuery: select 'x'
# 申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
testWhileIdle: true
# 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testOnBorrow: false
# 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
(3)在代码中配置多数据源
在工程中有多少个数据源就需要配置多少个DataSourceConfig
OneDataSourceConfig
package microapps.com.cn.conf;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
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.sql.SQLException;
/**
* 主数据源配置
*/
@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.one")
@MapperScan(basePackages = OneDataSourceConfig.PACKAGE
, sqlSessionFactoryRef = "oneSqlSessionFactory")
public class OneDataSourceConfig {
/**
* 配置多数据源 关键就在这里 这里配置了不同的数据源扫描不同mapper
*/
/**
* 配置多数据源 关键就在这里 这里配置了不同的数据源扫描不同mapper
*/
static final String PACKAGE = "microapps.com.cn.**.mapper.one";
static final String MAPPER_LOCATION = "classpath:mapper/one.xml";
/**
* 连接数据库信息 这个其实更好的是用配置中心完成
*/
private String driverClassName;
private String url;
private String username;
private String password;
private int initialSize;
private int minIdle;
private int maxActive;
private int maxWait;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
//@Primary //标志这个 Bean 如果在多个同类 Bean 候选时,该 Bean 优先被考虑。
@Bean(name = "oneDataSource")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);
//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
/**
* 这个是用来配置 druid 监控sql语句的 非常有用 如果你有两个数据源 这个配置哪个数据源就监控哪个数据源的sql 同时配置那就都监控
*/
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
//@Primary
@Bean(name = "oneSqlSessionFactory")
public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource)
throws Exception {
MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
mybatisPlus.setDataSource(dataSource);
mybatisPlus.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(OneDataSourceConfig.MAPPER_LOCATION));
return mybatisPlus;
}
@Bean(name = "oneTransactionManager")
public DataSourceTransactionManager ftransactionManager() {
return new DataSourceTransactionManager(dataSource());
}
/**
* 配置 Druid 监控界面
*/
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean srb =
new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
//设置控制台管理用户
srb.addInitParameter("loginUsername","root");
srb.addInitParameter("loginPassword","root");
//是否可以重置数据
srb.addInitParameter("resetEnable","false");
return srb;
}
}
TwoDataSourceConfig
package microapps.com.cn.conf;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
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.sql.SQLException;
/**
* 主数据源配置
*/
@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.two")
@MapperScan(basePackages = TwoDataSourceConfig.PACKAGE
, sqlSessionFactoryRef = "twoSqlSessionFactory")
public class TwoDataSourceConfig {
/**
* 配置多数据源 关键就在这里 这里配置了不同的数据源扫描不同mapper
*/
/**
* 配置多数据源 关键就在这里 这里配置了不同的数据源扫描不同mapper
*/
static final String PACKAGE = "microapps.com.cn.**.mapper.two";
static final String MAPPER_LOCATION = "classpath:mapper/two.xml";
/**
* 连接数据库信息 这个其实更好的是用配置中心完成
*/
private String driverClassName;
private String url;
private String username;
private String password;
private int initialSize;
private int minIdle;
private int maxActive;
private int maxWait;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
@Bean(name = "twoDataSource")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);
//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
/**
* 这个是用来配置 druid 监控sql语句的 非常有用 如果你有两个数据源 这个配置哪个数据源就监控哪个数据源的sql 同时配置那就都监控
*/
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
@Bean(name = "twoSqlSessionFactory")
public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource)
throws Exception {
MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
mybatisPlus.setDataSource(dataSource);
mybatisPlus.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(TwoDataSourceConfig.MAPPER_LOCATION));
return mybatisPlus;
}
@Bean(name = "twoTransactionManager")
public DataSourceTransactionManager ftransactionManager() {
return new DataSourceTransactionManager(dataSource());
}
}
Service
在使用时可以在具体方法上显示声明使用的是哪个事务,本例子中定义了one和two两个方法并分别声明了不同的事务。
package microapps.com.cn.service.impl;
import microapps.com.cn.mapper.one.OneMapper;
import microapps.com.cn.mapper.two.TwoMapper;
import microapps.com.cn.service.IMSService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
/**
* @Description: 测试多数据源服务接口
* @Author: liuhe
* @Date: 2020/11/19
*/
@Service
public class MSServiceImpl implements IMSService {
@Autowired
private OneMapper oneMapper;
@Autowired
private TwoMapper twoMapper;
@Transactional(value="oneTransactionManager")
public void saveOne() {
oneMapper.insert("1","aa");
int i = 1/0;
oneMapper.insert("2","bb");
}
@Transactional(value="twoTransactionManager")
public void saveTwo() {
twoMapper.insert("1","aa");
int i = 1/0;
twoMapper.insert("2","bb");
}
}