spring boot多数据源配置,多数据源事务

扫码关注公众号:Java 技术驿站

发送:vip
将链接复制到本浏览器,永久解锁本站全部文章

【公众号:Java 技术驿站】 【加作者微信交流技术,拉技术群】

springboot系列学习笔记全部文章请移步值博主专栏**: spring boot 2.X/spring cloud Greenwich
由于是一系列文章,所以后面的文章可能会使用到前面文章的项目。springboot系列代码全部上传至GitHub:https://github.com/liubenlong/springboot2_demo
本系列环境:Java11;springboot 2.1.1.RELEASE;springcloud Greenwich.RELEASE;MySQL 8.0.5;

文章目录

需求描述

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

想知道springboot如何加载配置以及选择使用哪个连接池,请移步springboot 中数据源配置,连接池配置,源码剖析,如何选择连接池

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 DataSource testDataSource() {
            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 ;

#事务
接下来说一下事务问题。上面主从两个数据源的配置类中,各自配置了一个事务管理器masterSqlSessionTemplateslaveSqlSessionTemplate
我们在写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

springboot系列学习笔记全部文章请移步值博主专栏**: spring boot 2.X/spring cloud Greenwich
由于是一系列文章,所以后面的文章可能会使用到前面文章的项目。springboot系列代码全部上传至GitHub:https://github.com/liubenlong/springboot2_demo
本系列环境:Java11;springboot 2.1.1.RELEASE;springcloud Greenwich.RELEASE;MySQL 8.0.5;

参考资料

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


来源:http://ddrv.cn

赞(0) 打赏
版权归原创作者所有,任何形式的转载请联系博主:daming_90:Java 技术驿站 » spring boot多数据源配置,多数据源事务

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏