spring boot+mybatis+atomikos实现多数据源分布式事务

1、项目上遇到的问题

　　最近在做一个项目，需要同时用到oracle和mysql两个数据库，那么问题就来了，怎么实现多数据源呢？数据源之间是怎么切换呢？多数据源事务怎么控制呢？

　　以下demo都是基于springboot。

2、其实实现多数据源还是很简单的，主要是以下步骤

　　1）配置application.yml，把之前的单数据源配置成多个

　　2）手动配置每个数据源，包含sqlsessionfactory ， transactionmanager，datasource， sqlsessiontemplate

3、那数据源怎么切换呢？

　　我采用的分包的方法来实现的，找过网上很多资料，有通过注解切换的，有兴趣的可以网上找找。

4、那多数据源事务怎么控制？

　　因为是多数据源，所以是跨库操作，单数据源不能保证两个事务都回滚，这里可以说是分布式事务。

　　关于分布式事务，XA ----->> 分布式事务协议 ，这里暂不多说

开始写代码吧

application.yml

spring:
  datasource:
    druid:
      m1:  #数据源1
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
        username: root
        password: 123456
        #初始化时建立物理连接的个数
        initialSize: 1
        #池中最大连接数
        maxActive: 20
        #最小空闲连接
        minIdle: 1
        #获取连接时最大等待时间，单位毫秒
        maxWait: 60000
        #有两个含义：
        #1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
        #2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
        timeBetweenEvictionRunsMillis: 60000
        #连接保持空闲而不被驱逐的最小时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        #使用该SQL语句检查链接是否可用。如果validationQuery=null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
        validationQuery: SELECT 1 FROM DUAL
        #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        testWhileIdle: true
        #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        testOnBorrow: false
        #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        testOnReturn: false
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        #connectionProperties.druid.stat.mergeSql: true
        #connectionProperties.druid.stat.slowSqlMillis: 5000
        # 合并多个DruidDataSource的监控数据
        #useGlobalDataSourceStat: true
        #default-auto-commit: true 默认
        #default-auto-commit: false
      m2: #数据源2
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
        username: root
        password: 123456
        #初始化时建立物理连接的个数
        initialSize: 1
        #池中最大连接数
        maxActive: 20
        #最小空闲连接
        minIdle: 1
        #获取连接时最大等待时间，单位毫秒
        maxWait: 60000
        #有两个含义：
        #1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
        #2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
        timeBetweenEvictionRunsMillis: 60000
        #连接保持空闲而不被驱逐的最小时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        #使用该SQL语句检查链接是否可用。如果validationQuery=null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
        validationQuery: SELECT 1 FROM DUAL
        #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        testWhileIdle: true
        #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        testOnBorrow: false
        #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
        testOnReturn: false
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        #connectionProperties.druid.stat.mergeSql: true
        #connectionProperties.druid.stat.slowSqlMillis: 5000
        # 合并多个DruidDataSource的监控数据
        #useGlobalDataSourceStat: true
        #default-auto-commit: true 默认

m1  数据库1

m2  数据库2

pom

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!--状态监控-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <!-- spring web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.vesion}</version>
        </dependency>
        <dependency>
            <groupId>org.javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.15.0-GA</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.spring.boot.starter.vesion}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.vesion}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis.spring.vesion}</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatis 分页 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.2</version>
        </dependency>
        <!-- swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${swagger.vesion}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>${swagger.vesion}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

        <!-- XA协议  支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.typesafe.akka/akka-actor -->
        <dependency>
            <groupId>com.typesafe.akka</groupId>
            <artifactId>akka-actor_2.13</artifactId>
            <version>2.6.3</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

数据库1的配置：

@Component
@ConfigurationProperties(prefix = "spring.datasource.druid.m1")
public class M1DataSourceProperties {
    private String driverClassName;
 
    private String url;
 
    private String username;
 
    private String password;
 
    private Integer initialSize;

    private Integer maxActive;

    private Integer minIdle;
    private Integer maxWait;
    private Integer timeBetweenEvictionRunsMillis;

    private Integer minEvictableIdleTimeMillis;

    private String validationQuery;
    private Boolean testWhileIdle;
    private Boolean testOnBorrow;
    private Boolean testOnReturn;

    private String filters;
}

数据库1的datasource配置：

import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.github.pagehelper.PageInterceptor;
import com.test.maven.user.common.BeanUtils;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.transaction.jta.JtaTransactionManager;

import javax.sql.DataSource;
import javax.transaction.UserTransaction;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = M1DataSourceConfig.PACKAGE, sqlSessionFactoryRef = "m1SqlSessionFactory",sqlSessionTemplateRef = "m1SqlSessionTemplate")
public class M1DataSourceConfig {


    static final String PACKAGE = "com.test.maven.user.dao.m1";
    static final String MAPPER_LOCATION = "classpath:mapper/m1/*.xml";

    @Autowired
    private M1DataSourceProperties m1DataSourceProperties;


    @Bean(name = "m1DataSource")
    @Primary
    public DataSource m1DataSource() {
        DruidXADataSource datasource = new DruidXADataSource();
        BeanUtils.copyProperties(m1DataSourceProperties,datasource);
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(datasource);
        xaDataSource.setUniqueResourceName("m1DataSource");
        System.out.println("数据源1初始化完成================");
        return xaDataSource;
    }

    @Bean(name = "transactionManager")
    public JtaTransactionManager transactionManager() {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        UserTransaction userTransaction = new UserTransactionImp();
        return new JtaTransactionManager(userTransaction, userTransactionManager);
    }


    @Bean(name = "m1SqlSessionFactory")
    @Primary
    public SqlSessionFactory m1SqlSessionFactory(@Qualifier("m1DataSource") DataSource m1DataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(m1DataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(M1DataSourceConfig.MAPPER_LOCATION));

        //分页插件
        Interceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        //数据库
        properties.setProperty("helperDialect", "mysql");
        //是否将参数offset作为PageNum使用
        properties.setProperty("offsetAsPageNum", "true");
        //是否进行count查询
        properties.setProperty("rowBoundsWithCount", "true");
        //是否分页合理化
        properties.setProperty("reasonable", "false");
        interceptor.setProperties(properties);
        sessionFactory.setPlugins(new Interceptor[] {interceptor});
        return sessionFactory.getObject();
    }

    @Bean(name = "m1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("m1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

数据库2的属性配置：

@Component
@ConfigurationProperties(prefix = "spring.datasource.druid.m2")
public class M2DataSourceProperties {
    private String driverClassName;
 
    private String url;
 
    private String username;
 
    private String password;
 
    private Integer initialSize;

    private Integer maxActive;

    private Integer minIdle;
    private Integer maxWait;
    private Integer timeBetweenEvictionRunsMillis;

    private Integer minEvictableIdleTimeMillis;

    private String validationQuery;
    private Boolean testWhileIdle;
    private Boolean testOnBorrow;
    private Boolean testOnReturn;

    private String filters;
}

数据库2的DataSource配置：

import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.github.pagehelper.PageInterceptor;
import com.test.maven.user.common.BeanUtils;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

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

@Configuration
@MapperScan(basePackages = M2DataSourceConfig.PACKAGE, sqlSessionFactoryRef = "m2SqlSessionFactory",sqlSessionTemplateRef = "m2SqlSessionTemplate")
public class M2DataSourceConfig {

    static final String PACKAGE = "com.test.maven.user.dao.m2";
    static final String MAPPER_LOCATION = "classpath:mapper/m2/*.xml";

    @Autowired
    private M2DataSourceProperties m2DataSourceProperties;


    @Bean(name = "m2DataSource")
    public DataSource m2DataSource() {
        DruidXADataSource datasource = new DruidXADataSource();
        BeanUtils.copyProperties(m2DataSourceProperties,datasource);
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(datasource);
        xaDataSource.setUniqueResourceName("m2DataSource");
        System.out.println("数据源2初始化完成================");
        return xaDataSource;
    }

    @Bean(name = "m2SqlSessionFactory")
    public SqlSessionFactory m2SqlSessionFactory(@Qualifier("m2DataSource") DataSource m2DataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(m2DataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(M2DataSourceConfig.MAPPER_LOCATION));
        //分页插件
        Interceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        //数据库
        properties.setProperty("helperDialect", "mysql");
        //是否将参数offset作为PageNum使用
        properties.setProperty("offsetAsPageNum", "true");
        //是否进行count查询
        properties.setProperty("rowBoundsWithCount", "true");
        //是否分页合理化
        properties.setProperty("reasonable", "false");
        interceptor.setProperties(properties);
        sessionFactory.setPlugins(new Interceptor[]{interceptor});
        return sessionFactory.getObject();
    }

    @Bean(name = "m2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("m2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

druid配置(状态监控页面)：

@Configuration
public class DruidConfig {

    /**
     * 注册一个StatViewServlet
     *
     * @return servlet registration bean
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
            new StatViewServlet(), "/druid/*");

        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * 注册一个：filterRegistrationBean
     *
     * @return filter registration bean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {

        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(
            new WebStatFilter());

        // 添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");

        // 添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

项目结构:

这样就基本完事了，可以实现数据源和事务控制了

上面m1的事务管理器只有一个，而在m2中是没有配置的，因为是交给了atomikos，由它来管理事务，他实现了JTA/XA规范中的事务管理器，这里也是简单的实现了功能.

以上是借鉴了网上资料和自己整理的，如有侵权，敬请谅解，谢谢。