SpringBoot+Mybatis+atomikos实现动态切换数据源+分布式事务

SpringBoot+Mybatis+atomikos实现动态切换数据源+分布式事务

实现以上功能需要按照步骤一步步执行：

1、引入依赖

2、配置文件

3、自定义数据切换的注解

4、继承Spring中AbstractRoutingDataSource，重写方法和构造函数

5、生成不同的数据库实例（bean）

6、利用AOP实现注解拦截，切换数据源

7、解决分布式事务控制下数据源无法动态切换的问题，改写SpringManagedTransaction获取Connection的方法。

1、引入依赖

 <parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.6.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <scope>provided</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>fastjson</artifactId>
		    <version>1.2.62</version>
		</dependency>
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
		    <version>2.0.0</version>
		</dependency>
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		    <version>6.0.6</version>
		</dependency>
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid</artifactId>
		    <version>1.1.19</version>
		</dependency>
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <scope>provided</scope>
		</dependency>
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
		    <version>2.2.6.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

2、配置文件

server:
  port: 8025
   
spring:
  application:
        name: datasource_test
  datasource:
     master:
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://127.0.0.1:3306/citex_fusion?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC&useSSL=true
     slave1:
         username: root
         password: root
         driver-class-name: com.mysql.cj.jdbc.Driver
         jdbc-url: jdbc:mysql://127.0.0.1:3306/citex_guess?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC&useSSL=true
     druid:
        initial-size: 5
        max-active: 20
        min-idle: 5
        test-on-borrow: true
        max-wait: -1
        min-evictable-idle-time-millis: 30000
        max-evictable-idle-time-millis: 30000
        time-between-eviction-runs-millis: 0   
mybatis:
  configuration:
    map-underscore-to-camel-case: true

 

3、自定义数据切换的注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.TYPE, ElementType.METHOD })
@Documented
public @interface DS {
	String value() default "master";
}

4、继承Spring中AbstractRoutingDataSource，重写方法和构造函数

@Slf4j
public class DynamicDataSourceContextHolder {
	private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();

	/**
	 * 设置数据库来源
	 */
	public static void setDateSoureType(String dsType) {
		log.info("获取到的数据类型是{}", dsType);
		CONTEXT_HOLDER.set(dsType);
	}

	/**
	 * 获取数据库来源
	 */
	public static String getDateSoureType() {
		return CONTEXT_HOLDER.get();
	}

	/**
	 * 清除数据库来源
	 */
	public static void clearDateSoureType() {
		CONTEXT_HOLDER.remove();
	}

}

public class DynamicDataSource extends AbstractRoutingDataSource {
	public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
		super.setDefaultTargetDataSource(defaultTargetDataSource);
		super.setTargetDataSources(targetDataSources);
		super.afterPropertiesSet();
	}

	@Override
	protected Object determineCurrentLookupKey() {
		return DynamicDataSourceContextHolder.getDateSoureType();
	}

	public DynamicDataSource() {
		// TODO Auto-generated constructor stub
	}
}

5、加载不同的数据库配置，并实现数据的动态切换

package com.hongyu.config.datasource;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;
import javax.transaction.TransactionManager;
import javax.transaction.UserTransaction;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;

@Configuration
public class DynamicDataSourceConfiguration {
	@Primary
	@Bean(name = "master")
	public DataSource masterDataSource(Environment env) {
		AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
		Properties prop = build(env, "spring.datasource.master.");
		ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
		ds.setUniqueResourceName("master");
		ds.setXaProperties(prop);
		return ds;
	}

	@Bean(name = "slaveOne")
	public DataSource slave_1_DataSource(Environment env) {
		AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
		Properties prop = build(env, "spring.datasource.slave1.");
		ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
		ds.setUniqueResourceName("slaveOne");
		ds.setPoolSize(5);
		ds.setXaProperties(prop);
		return ds;
	}

	@Bean(name = "dynamicDataSource")
	public DataSource dataSource(@Autowired @Qualifier("master") DataSource primery,
			@Autowired @Qualifier("slaveOne") DataSource coocon) {
		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		dynamicDataSource.setDefaultTargetDataSource(primery);
		Map<Object, Object> dsMap = new HashMap<Object, Object>(2);
		dsMap.put("master", primery);
		dsMap.put("slaveOne", coocon);
		dynamicDataSource.setTargetDataSources(dsMap);
		return dynamicDataSource;
	}

	@Bean(name = "userSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Autowired @Qualifier("dynamicDataSource") DataSource dynamicDataSource)
			throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		// 注意这里替换成我们写的DynamicDatasource
		sqlSessionFactoryBean.setDataSource(dynamicDataSource);
		// 替换原有的事物工厂，不然无法切换数据库
		sqlSessionFactoryBean.setTransactionFactory(new MyTransactionsFactory());
		sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
		return sqlSessionFactoryBean.getObject();
	}

	/***
	 * 分布式事物的配置
	 * 
	 * @return
	 * @throws Throwable
	 */
	@Bean(name = "userTransaction")
	public UserTransaction userTransaction() throws Throwable {
		UserTransactionImp userTransactionImp = new UserTransactionImp();
		userTransactionImp.setTransactionTimeout(10000);
		return userTransactionImp;
	}

	@Bean(name = "atomikosTransactionManager", initMethod = "init", destroyMethod = "close")
	public TransactionManager atomikosTransactionManager() throws Throwable {
		UserTransactionManager userTransactionManager = new UserTransactionManager();
		userTransactionManager.setForceShutdown(false);
		return userTransactionManager;
	}

	@Bean(name = "transactionManager")
	@DependsOn({ "userTransaction", "atomikosTransactionManager" })
	public PlatformTransactionManager transactionManager() throws Throwable {
		UserTransaction userTransaction = userTransaction();
		JtaTransactionManager manager = new JtaTransactionManager(userTransaction, atomikosTransactionManager());
		return manager;
	}

	private Properties build(Environment env, String prefix) {
		String druidStr = "spring.datasource.druid.";
		Properties prop = new Properties();
		prop.put("username", env.getProperty(prefix + "username"));
		prop.put("password", env.getProperty(prefix + "password"));
		prop.put("url", env.getProperty(prefix + "jdbc-url"));
		prop.put("driverClassName", env.getProperty(prefix + "driver-class-name", ""));
		prop.put("initialSize", env.getProperty(druidStr + "initial-size", Integer.class));
		prop.put("maxActive", env.getProperty(druidStr + "max-active", Integer.class));
		prop.put("minIdle", env.getProperty(druidStr + "min-idle", Integer.class));
		prop.put("maxWait", env.getProperty(druidStr + "max-wait", Integer.class));
		prop.put("testOnBorrow", env.getProperty(druidStr + "test-on-borrow", Boolean.class));
		return prop;
	}
}

6、AOP实现数据的动态切换

@Aspect
@Component
@Slf4j
public class DataSourceAspect {
	@Pointcut("@annotation(com.hongyu.config.datasource.DS)")
	public void dataSourcePointCut() {

	}

	@Around("dataSourcePointCut()")
	public Object around(ProceedingJoinPoint point) throws Throwable {
		MethodSignature signature = (MethodSignature) point.getSignature();
		Method method = signature.getMethod();
		DS annotation = method.getAnnotation(DS.class);
		DynamicDataSourceContextHolder.setDateSoureType(annotation.value());
		try {
			return point.proceed();
		} finally {
			log.info("清除的数据库是{}", annotation.value());
			DynamicDataSourceContextHolder.clearDateSoureType();
		}
	}
}

7、解决分布式事务控制下数据源无法动态切换的问题，改写SpringManagedTransaction获取Connection的方法。

public class MyManagedTransaction extends SpringManagedTransaction {
	DataSource dataSource;
	ConcurrentHashMap<String, Connection> map = new ConcurrentHashMap<>();

	public MyManagedTransaction(DataSource dataSource) {
		super(dataSource);
		this.dataSource = dataSource;
	}

	@Override
	public Connection getConnection() throws SQLException {
		String key = DynamicDataSourceContextHolder.getDateSoureType();
		if (map.containsKey(key)) {
			return map.get(key);
		}
		Connection con = dataSource.getConnection();
		map.put(key, con);
		return con;
	}
}

 

public class MyTransactionsFactory extends SpringManagedTransactionFactory {
	@Override
	public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
		return new MyManagedTransaction(dataSource);
	}
}

下面我们编写测试的例子

public interface FusionMapper {
	@DS("master")
	@Select("select  * from  fusion_invitee limit 10 ")
	List<FusionInvite> getFusionInviteList();

	@DS("master")
	@Insert("INSERT INTO fusion_invitee(user_id, invitee_id, code, create_time, update_time)\n"
			+ "VALUES (50018, 20597, 'FV3NEA', NOW(), NOW())")
	int insertfusionInvite(FusionInvite fusionInvite);

}

 

public interface GuessMapper {
	@DS("slaveOne")
	@Select("select  * from  guess_account limit 10")
	List<GuessAccount> getGuessAccountList();

	@DS("slaveOne")
	@Insert("INSERT INTO guess_account(USER_ID, CURRENCY_ID, CURRENCY, AVAILABLE_QTY, FROZEN_QTY, CREATE_TIME, UPDATE_TIME) VALUES "
			+ "(27491, 3, 'USDT', 0.007263000000000000, 0.000000000000000000, NOW(), NOW())")
	int insertguessAccount(GuessAccount guessAccount);
}

	@Transactional
	public void testJtaTransactional() {
		GuessAccount guessAccount = new GuessAccount();
		int row = guessMapper.insertguessAccount(guessAccount);
		FusionInvite fusionInvite = new FusionInvite();
		int row2 = fusionMapper.insertfusionInvite(fusionInvite);
		int i = 1 / 0;
	}

特别说明，如果没有进行第7步的改造的话，会出现无法切换数据源的情况，是由于我们使用了    @Transactional注解。

为了保证事物的一致性，它需要保证同一个线程的数据库执行Connection和事物执行的Connection必须保持一致，因此去调用下一个Mapper时仍然保持了上一个Mapper的连接。所以就报错了。

需要解决这个问题，就需要实现事物中的Connection动态切换。这样我们两段提交协议才能生效。

在启动了上面必须要排除SpringBoot自带数据库配置加载

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableTransactionManagement
@MapperScan(basePackages = { "com.hongyu.mapper" })