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规范中的事务管理器，这里也是简单的实现了功能.
以上是借鉴了网上资料和自己整理的，如有侵权，敬请谅解，谢谢。
