SpringBoot 配置多数据源

1. 实现MySql，clickHouse多数据源切换
2. 支持注解切换数据源，代码切换数据源，事务
3. 符合生产开发的需求规范

1. 基于AbstractRoutingDataSource实现动态数据源切换

1.1 maven 依赖

<properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.3.7.RELEASE</spring-boot.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><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><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- Druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.23</version></dependency><!-- Mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><!-- Mybatisd的依赖  --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.0</version></dependency><!-- Aspect --><dependency><groupId>org.aspectj</groupId><artifactId>aspectjweaver</artifactId><version>1.9.5</version></dependency><!-- clickhouse --><dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.2</version></dependency>

1.2 项目准备点（实际操作略）

1. 创建SpringBoot项目
2. 创建MySql数据库db1_name，db2_name
3. 创建ClickHouse,db1_name
4. 整合MyBatis逆向工程
5. 映射文件配置等操作
   

1.3 整合多数据源

1.3.1 application.properties

## mysql database zta
spring.datasource.mysql.zta.jdbcUrl=jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
spring.datasource.mysql.zta.username=root
spring.datasource.mysql.zta.password=123456
spring.datasource.mysql.zta.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mysql.zta.connectionTimeout=10000
spring.datasource.mysql.zta.maximumPoolSize=5
spring.datasource.mysql.zta.test-on-borrow=true
spring.datasource.mysql.zta.auto-commit=true
spring.datasource.mysql.zta.connection-test-query=select1## mysql database tms
spring.datasource.mysql.tms.jdbcUrl=jdbc:mysql://127.0.0.1:3306/db2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
spring.datasource.mysql.tms.username=root
spring.datasource.mysql.tms.password=123456
spring.datasource.mysql.tms.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mysql.tms.connectionTimeout=10000
spring.datasource.mysql.tms.maximumPoolSize=5
spring.datasource.mysql.tms.test-on-borrow=true
spring.datasource.mysql.tms.auto-commit=true
spring.datasource.mysql.tms.connection-test-query=select1## clickhouse database
spring.datasource.clickhouse.jdbcUrl=jdbc:clickhouse://127.0.0.1:8123/db3
spring.datasource.clickhouse.username=root
spring.datasource.clickhouse.password=123456
spring.datasource.clickhouse.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.clickhouse.connectionTimeout=10000
spring.datasource.clickhouse.maximumPoolSize=5
spring.datasource.clickhouse.test-on-borrow=true
spring.datasource.clickhouse.auto-commit=true
spring.datasource.clickhouse.connection-test-query=select1

1.3.2 DataSourceConfig.java 数据源配置类

import com.zaxxer.hikari.HikariDataSource;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.SqlSessionTemplate;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;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 org.springframework.transaction.PlatformTransactionManager;import javax.sql.DataSource;import java.util.HashMap;import java.util.Map;

/**
 * @describe
 * @MapperScan 注入动态代理的sqlSessionFactory
 */
@Configuration
public class DataSourceConfig{

    /**
     * @describe
     *1. 创建数据源dataSource
     *    ConfigurationProperties: application.properties prefix前缀自动映射数据库连接信息
     */
    @Bean(name="ztaMySqlDataSource")
    @ConfigurationProperties(prefix="spring.datasource.mysql.zta")
    public DataSourcecreateZtaMySqlDataSource(){return DataSourceBuilder.create().type(HikariDataSource.class).build();}

    @Bean(name="tmsMySqlDataSource")
    @ConfigurationProperties(prefix="spring.datasource.mysql.tms")
    public DataSourcecreateTmsMySqlDataSource(){return DataSourceBuilder.create().type(HikariDataSource.class).build();}

    @Bean(name="clickHouseDataSource")
    @ConfigurationProperties(prefix="spring.datasource.clickhouse")
    public DataSourcecreateClickHouseDataSource(){return DataSourceBuilder.create().type(HikariDataSource.class).build();}


    /**
     * @describe
     *2 基于AbstractRoutingDataSource实现动态数据源切换
     *    Primary表示注入优先级最高
     *2.1 创建动态代理数据源 dataSource
     *2.2 创建动态代理 dynamicSqlSessionFactory,dynamicSqlSessionTemplate
     *2.3 创建动态代理事管理器 dynamicTransactionManager
     * @param ztaMySqlDataSource
     * @param clickHouseDataSource
     * @param tmsMysqlDataSource
     * @return
     */
    @Primary
    @Bean(name="dataSource")
    public DynamicDataSource dataSource(@Qualifier("ztaMySqlDataSource") DataSource ztaMySqlDataSource,
                                        @Qualifier("clickHouseDataSource") DataSource clickHouseDataSource,
                                        @Qualifier("tmsMySqlDataSource") DataSource tmsMysqlDataSource){
        Map<Object, Object> targetDataSource= new HashMap<>();
        targetDataSource.put(DataBaseType.ZTAMYSQL, ztaMySqlDataSource);
        targetDataSource.put(DataBaseType.CLICKHOUSE, clickHouseDataSource);
        targetDataSource.put(DataBaseType.TMSMYSQL, tmsMysqlDataSource);
        DynamicDataSource dynamicDataSource= new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(targetDataSource);
        // 设置默认数据源
        dynamicDataSource.setDefaultTargetDataSource(ztaMySqlDataSource);return dynamicDataSource;}

    @Primary
    @Bean(name="dynamicSqlSessionFactory")
    public SqlSessionFactory createSqlSessionFactoryBean(@Qualifier("dataSource")DataSource dataSource)
            throws Exception{
        SqlSessionFactoryBean bean= new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/**/*.xml"));return bean.getObject();}

    @Primary
    @Bean(name="dynamicSqlSessionTemplate")
    public SqlSessionTemplate createDynamicSqlSessionTemplate(@Qualifier("dynamicSqlSessionFactory")SqlSessionFactory sqlSessionFactory){return new SqlSessionTemplate(sqlSessionFactory);}

    @Primary
    @Bean(name="dynamicTransactionManager")
    public PlatformTransactionManager creatProxyTransactionManager(@Qualifier("dataSource")DataSource dataSource) throws Exception{return new DataSourceTransactionManager(dataSource);}}

1.3.3 DynamicDataSource.Java 动态代理对象

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource{

    @Override
    protected ObjectdetermineCurrentLookupKey(){return DataSourceType.getDataBaseType();}}

1.3.4 注解切换数据源（直接复制，任意SpringBoot可用）

1. DataBaseType.java

public enum DataBaseType{
	ZTAMYSQL, CLICKHOUSE, TMSMYSQL}

2. DataSource

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
@Documented
public @interface DataSource{
	public DataBaseType value() default DataBaseType.CLICKHOUSE;}

3. DataSourceType.java

import org.slf4j.Logger;import org.slf4j.LoggerFactory;


public class DataSourceType{

	private static final Logger logger= LoggerFactory.getLogger(DataSourceType.class);

	privateDataSourceType(){}

	// 使用ThreadLocal保证线程安全
	private static final ThreadLocal<DataBaseType> TYPE= new ThreadLocal<>();

	// 往当前线程里设置数据源类型
	public static void setDataBaseType(DataBaseType dataBaseType){if(dataBaseType== null){
			throw new NullPointerException();}
		logger.info("Change the current data source to：" + dataBaseType);
		TYPE.set(dataBaseType);}


	// 获取数据源类型
	public static DataBaseTypegetDataBaseType(){
		DataBaseType dataBaseType= TYPE.get()== null ? DataBaseType.ZTAMYSQL: TYPE.get();
		logger.info("Get the current data source：" + dataBaseType);return dataBaseType;}

	// 清空数据类型
	public static voidclearDataBaseType(){
		TYPE.remove();}

	//判断设置的数据源是否存在
	public static boolean existDateSoure(DataBaseType dateBaseType){return dateBaseType.ZTAMYSQL==  dateBaseType|| dateBaseType.CLICKHOUSE== dateBaseType|| dateBaseType.TMSMYSQL== dateBaseType;}}

4. DynamicDataSourceAspect.java

import org.aspectj.lang.JoinPoint;import org.aspectj.lang.annotation.After;import org.aspectj.lang.annotation.Aspect;import org.aspectj.lang.annotation.Before;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.core.annotation.Order;import org.springframework.stereotype.Component;import java.lang.reflect.Method;

@Aspect
@Order(-1)
@Component
public class DynamicDataSourceAspect{

	private static final Logger logger= LoggerFactory.getLogger(DataSourceType.class);
	
	
	private Method getImplementationMethod(JoinPoint point){
		Object[] args= point.getArgs();
		Class<?>[] argTypes= new Class[point.getArgs().length];for(int i=0; i< args.length; i++){
			argTypes[i]= args[i].getClass();}
		Method method= null;
		try{
			method= point.getTarget().getClass()
					.getMethod(point.getSignature().getName(), argTypes);} catch(NoSuchMethodException e){
			logger.error("NoSuchMethodException",e);} catch(SecurityException e){
			logger.error("SecurityException",e);}return method;}
	
	
	/**
	 * 切换数据库
	 * @param point
	 * @param dataSourceAnnotation
	 * @return
	 * @throws Throwable
	 */
	@Before("@annotation(DataSource)")
	public void changeDataSource(JoinPoint point){
		Method method= getImplementationMethod(point);
		if(method!= null&& method.isAnnotationPresent(DataSource.class)){    
			DataSource anno=(DataSource)method.getAnnotation(DataSource.class);
			DataBaseTypetype= anno.value();
			if(DataSourceType.existDateSoure(type)){
				DataSourceType.setDataBaseType(type);}else{
				logger.error("No data source found ...【"+type+"】");}}}

	/**
	 * @Title: destroyDataSource
	 * @Description: 销毁数据源  在所有的方法执行执行完毕后
	 * @param point
	 * @param dataSourceAnnotation
	 * @return void
	 * @throws
	 */
	@After("@annotation(DataSource)")
	public void destroyDataSource(JoinPoint point){
		DataSourceType.clearDataBaseType();}}

1.4 demo-案例

1.4.1 注解式事务 @Transactional

    /**
     * @describe 注解式事务
     */
    @Transactional
    @Override
    public voidtransactionalInsert(){
        // 注意：开启事务之后无法切换数据源
        // DataSourceType.setDataBaseType(DataBaseType.ZTAMYSQL);
        TransOrderEvent transOrderEvent= new TransOrderEvent();
        transOrderEvent.setEventType(0);
        transOrderEvent.setCompanyId(0L);
        transOrderEvent.setCreatorId(0L);
        transOrderEvent.setModifyId(0L);
        transOrderEvent.setEventTime(0L);
        transOrderEvent.setCreateTime(0L);
        transOrderEvent.setModifyTime(0L);
        transOrderEventMapperExt.insertSelective(transOrderEvent);
        // 在此处打断点查看数据库
        System.out.println("insert");}

注意：@Transactional必须是代理执行才会生效，开启事务后必须提交事务再切换数据源才会生效

1.4.2 编程式事务

    /**
     * @describe 编程式事务
     */
    @Override
    public voidinsertCommit(){
        DefaultTransactionDefinition def= new DefaultTransactionDefinition();
        def.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
        TransactionStatus status= transactionManager.getTransaction(def);
        try{
            TransOrderEvent transOrderEvent= new TransOrderEvent();
            transOrderEvent.setEventType(0);
            transOrderEvent.setCompanyId(0L);
            transOrderEvent.setCreatorId(0L);
            transOrderEvent.setModifyId(0L);
            transOrderEvent.setEventTime(0L);
            transOrderEvent.setCreateTime(0L);
            transOrderEvent.setModifyTime(0L);
            transOrderEventMapperExt.insertSelective(transOrderEvent);
            // 在此处打断点查看数据库
            transactionManager.commit(status);} catch(Exception e){
            transactionManager.rollback(status);}}