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

浅浅的花香味﹌ 2023-02-17 05:03 142阅读 0赞

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

如图是两种解决方案：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBU3RyaXZlcg_size_16_color_FFFFFF_t_70][]

**以下，以第二种方式总结，部分参考：[SpringBoot之多数据源动态切换数据源][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)

![20200612155955506.png][]

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][Maven_jar]

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

**附**：

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBU3RyaXZlcg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200612150850368.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBU3RyaXZlcg==,size_16,color_FFFFFF,t_70
[SpringBoot]: https://blog.csdn.net/xp541130126/article/details/81739760?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-1
[20200612155955506.png]: https://img-blog.csdnimg.cn/20200612155955506.png
[Maven_jar]: https://blog.csdn.net/BAStriver/article/details/106673694
[SpringBoot 1]: https://www.jianshu.com/p/dfd5ae340011
[_springboot]: https://blog.csdn.net/qq_41076797/article/details/82889770