spring boot多数据源配置，多数据源事务 - 好文

更多springboot2.X文章请移步至博主专栏： spring boot 2.1 专栏
<https://blog.csdn.net/liubenlong007/article/details/85408338>

文章目录

* 需求描述 <https://blog.csdn.net/fgyibupi/article/details/79746120#_7>
* 配置项说明 <https://blog.csdn.net/fgyibupi/article/details/79746120#_190>
* 备注：单库的配置 <https://blog.csdn.net/fgyibupi/article/details/79746120#_209>
* 参考资料 <https://blog.csdn.net/fgyibupi/article/details/79746120#_230>

* 阅读本文前，请先阅读笔者另一片文章Spring Boot 源码深入分析
<https://blog.csdn.net/liubenlong007/article/details/79744376>
<>需求描述

在业务开发中，需要读写分离，或者需要配置多个数据源，接下来我们看看在springboot中如何配置多数据源，支持事务。

想知道springboot如何加载配置以及选择使用哪个连接池，请移步springboot 中数据源配置，连接池配置，源码剖析，如何选择连接池
<https://blog.csdn.net/liubenlong007/article/details/79748623>

application.properties修改如下，主要是添加双数据源：
spring.datasource.master.url=${jdbc.master.url}
spring.datasource.master.username=${jdbc.username}
spring.datasource.master.password=${jdbc.password}
spring.datasource.master.driverClassName=com.mysql.jdbc.Driver
spring.datasource.master.max-idle=30
spring.datasource.master.max-wait-millis=10000
spring.datasource.master.min-idle=5 spring.datasource.master.initial-size=5
spring.datasource.master.initSQL=SELECT 2
spring.datasource.master.connection-init-sqls=SELECT 1
spring.datasource.master.validation-query=SELECT 1
spring.datasource.master.validation-query-timeout=3000
#spring.datasource.master.test-on-borrow=true
spring.datasource.master.test-while-idle=true
spring.datasource.master.time-between-eviction-runs-millis=10000
spring.datasource.master.max-active=30
spring.datasource.slave.url=${jdbc.slave.url}
spring.datasource.slave.username=${jdbc.username}
spring.datasource.slave.password=${jdbc.password}
spring.datasource.slave.driverClassName=com.mysql.jdbc.Driver
spring.datasource.slave.max-idle=30
spring.datasource.slave.max-wait-millis=10000
spring.datasource.slave.min-idle=5 spring.datasource.slave.initial-size=5
spring.datasource.slave.initSQL=SELECT 2
spring.datasource.slave.connection-init-sqls=SELECT 1
spring.datasource.slave.validation-query=SELECT 1
spring.datasource.slave.validation-query-timeout=3000
#spring.datasource.slave.test-on-borrow=true
spring.datasource.slave.test-while-idle=true
spring.datasource.slave.time-between-eviction-runs-millis=10000
spring.datasource.slave.max-active=30
然后如果想要springboot加载到这两个配置，需要自定义数据源以及事务管理器等：

主库的配置：
import com.netease.generator.EncryptionInterceptor; import org.apache.ibatis.
plugin.Interceptor; import org.apache.ibatis.session.SqlSessionFactory; import
org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.
SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.
springframework.beans.factory.annotation.Qualifier; import org.springframework.
boot.autoconfigure.jdbc.DataSourceBuilder; 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; @Configuration @MapperScan(basePackages = {"repository的包名"},
sqlSessionTemplateRef= "masterSqlSessionTemplate") public class
MybatisMasterDbConfig { @Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master") @Primary public
DataSourcetestDataSource() { return DataSourceBuilder.create().build(); } @Bean(
name= "masterSqlSessionFactory") @Primary public SqlSessionFactory
testSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource)
throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource); bean.setMapperLocations(new
PathMatchingResourcePatternResolver().getResources(
"classpath:mybatis/mapper/master/*.xml")); bean.setPlugins(new Interceptor[]{new
EncryptionInterceptor()}); return bean.getObject(); } @Bean(name =
"masterTransactionManager") @Primary public DataSourceTransactionManager
testTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource); } @Bean(name =
"masterSqlSessionTemplate") @Primary public SqlSessionTemplate
testSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory
sqlSessionFactory) throws Exception { return new SqlSessionTemplate(
sqlSessionFactory); } }
从库的配置类：
import com.netease.generator.EncryptionInterceptor; import org.apache.ibatis.
plugin.Interceptor; import org.apache.ibatis.session.SqlSessionFactory; import
org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.
SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.
springframework.beans.factory.annotation.Qualifier; import org.springframework.
boot.autoconfigure.jdbc.DataSourceBuilder; 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; @Configuration @MapperScan(basePackages = {"repository的包名"},
sqlSessionTemplateRef= "slaveSqlSessionTemplate") public class
MybatisSlaveDbConfig { @Bean(name = "slaveDataSource") @ConfigurationProperties(
prefix= "spring.datasource.slave") public DataSource testDataSource() { return
DataSourceBuilder.create().build(); } @Bean(name = "sqlSessionFactory") @Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("slaveDataSource")
DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new
SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations
(new PathMatchingResourcePatternResolver().getResources(
"classpath:mybatis/mapper/slave/*.xml")); bean.setPlugins(new Interceptor[]{new
EncryptionInterceptor()}); return bean.getObject(); } @Bean(name =
"slaveTransactionManager") public DataSourceTransactionManager
testTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource); } @Bean(name =
"slaveSqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws
Exception{ return new SqlSessionTemplate(sqlSessionFactory); } }

可以看到，上述两个配置类，是根据包的路径来区分mapper的【我们这里使用的是mybatis，并且是使用的mapper.xml文件】。所以只需要建两个包路径即可：
**.**.master.mapper **.**.slave.mapper
假设master的mapper类下有UserMasterMapper.java
假设slave的mapper类下有 UserSlaveMapper.java
这两个mapper读者可以自行使用mysql的自动生成工具生成；对应上面配置类中的@MapperScan指定的basepackage地址。
然后mapper.xml也应该有两个目录【在 masterSqlSessionFactory 中指定的目录地址】：
假设resource/mapper/master下有UserMasterMapper.xml
假设resource/mapper/slave下有UserMasterMapper.xml

经过上面的配置，我们就可以愉快的撸代码了：
@Autowired private UserMasterMapper userMasterMapper; @Autowired private
UserSlaveMapper userSlaveMapper;
#事务
接下来说一下事务问题。上面主从两个数据源的配置类中，各自配置了一个事务管理器masterSqlSessionTemplate和
slaveSqlSessionTemplate。
我们在写service方法的时候，一定要标识是使用的哪个事务管理器
@Transactional(rollbackFor = Exception.class, transactionManager =
"slaveTransactionManager") public void testMultiDST(String mobile) { }
如果不加事务管理器，默认是第一个加载的数据源的事务。为了不出一些不可描述的BUG，强烈建议明确指定使用哪个事务管理器。

<>配置项说明

在application.properties配置文件中，我们可以看到
#使用DBCP connection pool时，指定初始化时要执行的sql
spring.datasource.slave.connection-init-sqls=SELECT 'x' #指定获取连接时连接校验的sql查询语句.
spring.datasource.slave.validation-query=SELECT 'x' # 指定连接校验查询的超时时间.
spring.datasource.slave.validation-query-timeout=3000 #获取连接时候验证，会影响性能（不建议true）
spring.datasource.slave.test-on-borrow=false #验证连接的有效性
spring.datasource.slave.test-while-idle=true
#空闲连接回收的时间间隔，与test-while-idle一起使用，设置5分钟
spring.datasource.slave.time-between-eviction-runs-millis=10000
master也同样配置了。这几个参数是为了探测MySQL的链接是否存活，

<>备注：单库的配置
spring.datasource.url=${star.jdbc.url}
spring.datasource.username=${jdbc.username}
spring.datasource.password=${jdbc.password}
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.tomcat.max-active=30 spring.datasource.tomcat.max-idle=30
spring.datasource.tomcat.max-wait=10000 spring.datasource.tomcat.min-idle=5
spring.datasource.tomcat.initial-size=5 spring.datasource.tomcat.initSQL=SELECT
2 spring.datasource.tomcat.validation-query=SELECT 'x'
spring.datasource.tomcat.validation-query-timeout=3000
#spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.time-between-eviction-runs-millis=10000
logging.level.com.dy.springboot.server.mapper=debug
更多springboot2.X文章请移步至博主专栏： spring boot 2.1 专栏
<https://blog.csdn.net/liubenlong007/article/details/85408338>

<>参考资料

https://www.cnblogs.com/xingzc/p/6022956.html
<https://www.cnblogs.com/xingzc/p/6022956.html>

热门工具换一换