Springboot中如何配置多个数据源 小结

1. 最近有这样一个需求，项目中涉及到了两种数据库，分别是：Postsgres和Sqlserver。每次从Postsgres中取1w条数据然后通过Mybatis调用存储过程写入到Sqlserver，所以在这个流程中，我们需要使用到两个数据源，并且在数据轮询中不断地进行切换。

如图是两种解决方案：

    以下，以第二种方式总结，部分参考：SpringBoot之多数据源动态切换数据源。

3. 假设数据源1是默认的数据源，两者都包含有User表，那么，首先配置properties。

spring.datasource.default.driverClassName = org.postgresql.Driver
spring.datasource.default.url = jdbc:postgresql://localhost:5432/postgres
spring.datasource.default.username = postgres
spring.datasource.default.password = abc123

spring.datasource.others[0].key = sqlserver
spring.datasource.others[0].driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.others[0].url = jdbc:sqlserver://localhost:1433;databaseName=testDB
spring.datasource.others[0].username = admin
spring.datasource.others[0].password = abc123

注：如果有第三个的话，可以追加为 spring.datasource.others[1].key = something 这种格式。

4. 接下来就是通过实现ImportBeanDefinitionRegistrar接口，注册数据源。

package com.bas.configuration;

import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.MutablePropertyValues;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.GenericBeanDefinition;
import org.springframework.boot.context.properties.bind.Bindable;
import org.springframework.boot.context.properties.bind.Binder;
import org.springframework.boot.context.properties.source.ConfigurationPropertyName;
import org.springframework.boot.context.properties.source.ConfigurationPropertyNameAliases;
import org.springframework.boot.context.properties.source.ConfigurationPropertySource;
import org.springframework.boot.context.properties.source.MapConfigurationPropertySource;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.ImportBeanDefinitionRegistrar;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotationMetadata;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @title: test
 * @author: bastriver
 * @create: 2020/6/9 18:16
 **/
public class DynamicDataSourceRegister implements ImportBeanDefinitionRegistrar, EnvironmentAware {

    private Environment evn;
    
	private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceRegister.class);
    
	private final static ConfigurationPropertyNameAliases aliases = new ConfigurationPropertyNameAliases();

    /**
     * add aliases here to avoid the situation that some parameters cannot be injected
     * when switching data sources because some data sources are configured differently.
     */
    static {
        aliases.addAliases("url", new String[]{"jdbc-url"});
        aliases.addAliases("username", new String[]{"user"});
    }

    /**
     * registered data sources
     */
    private Map<String, DataSource> customDataSources = new HashMap<String, DataSource>();

    private Binder binder;

    /**
     * ImportBeanDefinitionRegistrar
     * The implementation method of the interface, through which the bean can be registered in its own way
     */
    @Override
    public void registerBeanDefinitions(AnnotationMetadata annotationMetadata, BeanDefinitionRegistry beanDefinitionRegistry) {

        Map config, defauleDataSourceProperties;
        defauleDataSourceProperties = binder.bind("spring.datasource.default", Map.class).get();
        String typeStr = "com.zaxxer.hikari.HikariDataSource";
        Class<? extends DataSource> clazz = getDataSourceType(typeStr);
        // Bind the default data source parameter, master data source
        DataSource consumerDatasource, defaultDatasource = bind(clazz, defauleDataSourceProperties);
        DynamicDataSourceContextHolder.dataSourceIds.add("default");
        logger.info("Successfully registered default data source");
        // load the other data sources
        List<Map> configs = binder.bind("spring.datasource.others", Bindable.listOf(Map.class)).get();
        for (int i = 0; i < configs.size(); i++) {
            config = configs.get(i);
            clazz = getDataSourceType((String) config.get("type"));
            defauleDataSourceProperties = config;
            consumerDatasource = bind(clazz, defauleDataSourceProperties);
            String key = config.get("key").toString();
            customDataSources.put(key, consumerDatasource);
            // record registered data source keys
            DynamicDataSourceContextHolder.dataSourceIds.add(key);
            logger.info("Successfully registered data source {}", key);
        }
        GenericBeanDefinition define = new GenericBeanDefinition();
        define.setBeanClass(DynamicRoutingDataSource.class);
        MutablePropertyValues mpv = define.getPropertyValues();
        // set the default data source
        mpv.add("defaultTargetDataSource", defaultDatasource);
        // set the other data source
        mpv.add("targetDataSources", customDataSources);
        // Register the bean as a datasource without using the datasource automatically generated by springboot
        beanDefinitionRegistry.registerBeanDefinition("datasource", define);
        logger.info("Successfully registered data source, a total of {} data sources were registered", customDataSources.keySet().size() + 1);
    }

    private Class<? extends DataSource> getDataSourceType(String typeStr) {
        Class<? extends DataSource> type;
        try {
            if (StringUtils.hasLength(typeStr)) {
                type = (Class<? extends DataSource>) Class.forName(typeStr);
            } else {
                // default
                type = HikariDataSource.class;
            }
            return type;
        } catch (Exception e) {
            throw new IllegalArgumentException("can not resolve class with type: " + typeStr);
        }
    }

    private void bind(DataSource result, Map properties) {
        ConfigurationPropertySource source = new MapConfigurationPropertySource(properties);
        Binder binder = new Binder(new ConfigurationPropertySource[]{source.withAliases(aliases)});
        binder.bind(ConfigurationPropertyName.EMPTY, Bindable.ofInstance(result));
    }

    private <T extends DataSource> T bind(Class<T> clazz, Map properties) {
        ConfigurationPropertySource source = new MapConfigurationPropertySource(properties);
        Binder binder = new Binder(new ConfigurationPropertySource[]{source.withAliases(aliases)});
        return binder.bind(ConfigurationPropertyName.EMPTY, Bindable.of(clazz)).get();
    }

    private <T extends DataSource> T bind(Class<T> clazz, String sourcePath) {
        Map properties = binder.bind(sourcePath, Map.class).get();
        return bind(clazz, properties);
    }

    @Override
    public void setEnvironment(Environment environment) {
        logger.info("Start registering data source");
        this.evn = environment;
        binder = Binder.get(evn);
    }
}

注：

binder.bind("spring.datasource.default", Map.class).get(); 是默认数据源的url, username, password属性名的前缀，

binder.bind("spring.datasource.others", Bindable.listOf(Map.class)).get(); 指的是除了默认数据源的所有数据源配置。

如果这里配置错的话是会报异常：java.util.NoSuchElementException: No value bound...

另外，这个实现类需要在启动类加上：@Import(DynamicDataSourceRegister.class)

5. 因为需要记录当前数据源的使用状态，比如key是什么，以及注册成功了哪些数据源，现在需要一个数据源context来记录这些信息。

package com.bas.configuration;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;

/**
 * @title: test
 * @author: bastriver
 * @create: 2020/6/9 19:16
 **/
public class DynamicDataSourceContextHolder {

   	private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
	
	/**
     * store the registered data source key
     */
    public static List<String> dataSourceIds = new ArrayList<>();

    private static final ThreadLocal<String> HOLDER = new ThreadLocal<>();

    public static String getDataSourceRouterKey() {
        return HOLDER.get();
    }

    public static void setDataSourceRouterKey(String dataSourceRouterKey) {
        logger("Switch to {} data source.", dataSourceRouterKey);
        HOLDER.set(dataSourceRouterKey);
    }

    /**
     * Be sure to remove the data source before setting it up
     */
    public static void removeDataSourceRouterKey() {
        HOLDER.remove();
    }

    public static boolean containsDataSource(String dataSourceId) {
        return dataSourceIds.contains(dataSourceId);
    }

}

注：当我们需要切换使用的数据源的时候，就可以通过setDataSourceRouterKey()设置当前的数据源状态。

6. 虽然记录了数据源的信息以及状态，但是真正要修改spring容器的当前数据源状态的话，需要借助AbstractRoutingDataSource来进行以下的操作。

package com.bas.configuration;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @title: test
 * @author: bastriver
 * @create: 2020/6/9 19:30
 **/
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

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

    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceName = DynamicDataSourceContextHolder.getDataSourceRouterKey();
	logger.info("The current data source is {}", dataSourceName);
        return dataSourceName;
    }
}

注：查看日志的时候其实就能发现，spring会轮询determineCurrentLookupKey()，所以当我们修改了DynamicDataSourceContextHolder的key，那么当前数据源就被切换了。

另外，为了避免一堆日志文件，可以注释掉日志输出。

7. 按照以上的步骤，已经能够动态地切换数据源了。按照需求(每次从Postsgres中取1w条数据然后通过Mybatis调用存储过程写入到Sqlserver)，以下代码就是在service层进行手动切换数据源的。

@Override
public Boolean updateUser(User user) {

	int currentPageCount = 1;
	date = DateUtil.dateToString(date);

	while(true){
		DynamicDataSourceContextHolder.setDataSourceRouterKey("default");
		logger.info("query user data from page {} , till {}.", currentPageCount, pageCount*currentPageCount);
		List<User> userList = queryUserData(currentPageCount++, pageCount, date);
		logger.info("update {} user data in sqlserver.", userList.size());
		DynamicDataSourceContextHolder.setDataSourceRouterKey("sqlserver");
		userList.forEach(this::callInsertUserSp);

		if(userList.size() < pageCount)
			break;
	}

	return Boolean.TRUE;
}

8. 以上的方式不免有些麻烦，毕竟上面的service这一层最后是调用dao的mapper进行update的，所以说，还可以通过AOP的注解方式，在dao中增加注解的方式来动态切换数据源。

1) DataSource注解。

package com.bas.annotation;

import java.lang.annotation.*;

/**
 * @title: test
 * @author: bastriver
 * @create: 2020/6/9 20:16
 **/
@Target({ElementType.METHOD, ElementType.TYPE, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    String value() default "default"; // 默认是default数据源
}

2) 添加AOP，通过AOP拦截，获取注解中的key值，如果不存在就是用默认数据源。

<dependency>
	<groupId>org.aspectj</groupId>
	<artifactId>aspectjrt</artifactId>
	<version>1.9.2</version>
</dependency>

package com.bas.configuration;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.bas.annotation.DataSource;
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.springframework.stereotype.Component;

/**
 * @title: test
 * @author: bastriver
 * @create: 2020/6/9 20:30
 **/
@Aspect
@Component
public class DynamicDataSourceAspect {

    @Before("@annotation(com.bas.annotation.DataSource)")
    public void changeDataSource(JoinPoint point, DataSource ds) throws Throwable {
        String dsId = ds.value();
        if (DynamicDataSourceContextHolder.dataSourceIds.contains(dsId)) {
            logger.info("Use DataSource :{} > {}", dsId, point.getSignature());
        } else {
            logger.info("Data source [{}] does not exist, use the default data source. {}", dsId, point.getSignature());
            DynamicDataSourceContextHolder.setDataSourceRouterKey(dsId);
        }
    }

    @After("@annotation(com.bas.annotation.DataSource)")
    public void restoreDataSource(JoinPoint point, DataSource ds) {
        logger.info("Revert DataSource : {} > {}", ds.value(), point.getSignature());
        DynamicDataSourceContextHolder.removeDataSourceRouterKey();

    }
}

3) 修改mapper。

package com.bas.dao;

import com.bas.model.User;

import java.util.List;

public interface UserMapper {
    
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbg.generated
     */
    @DataSource("default")
    List<UserData> selectAll();

    // insert into SQL server
    @DataSource("sqlserver")
    int callInsertSP(UserData user);
}

 

注：

1. 因为使用到sqljdbc4依赖，pom中不能直接从库下载，所以需要下载到本地项目中然后引入，这个可以参考：Maven中添加本地包并且打包到jar

2. 如果在app启动类添加了@Import(DynamicDataSourceRegister.class)，在使用unit-test测试的时候，unit-test启动类就不需要添加这个import了。

 

附：

1. 如果需要按照步骤1中使用SqlSessionFactory那种方式可以参考：SpringBoot多数据源配置详细教程

2. 如果是简单地需要配置多个数据源，包含多个config和mapper，那么可以参考： 手把手教你用springboot配置多数据源