SpringBoot动态多数据源实现方式

SpringBoot动态多数据源实现方式

一、概述

有时在项目中可能会使用到多个数据源，并且生产与开发、测试环境所需配置的个数均不一样，这种场景下就需要使用动态多数据源的方式去解决。

二、实现

本文将介绍如何通过Springboot的application.properties的配置+代码，实现通过注解或者动态数据源bean动态切换多数据源。

话不多说直接贴代码。

application.properties配置

# 主数据源
spring.datasource.db-type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.driverClassName = com.mysql.jdbc.Driver
spring.datasource.druid.url = jdbc:mysql://127.0.0.1/master?useUnicode=true&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
spring.datasource.druid.username = root
spring.datasource.druid.password = 
spring.datasource.druid.locations = classpath:/mapper/*.xml

# 动态数据源1 在项目中所需切换进行业务处理的特殊数据源1
platform.data-source-model-config.ali.url = jdbc:mysql://127.0.0.1:3306/dynamic1?useUnicode=true&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
platform.data-source-model-config.ali.username = root
platform.data-source-model-config.ali.password = 
platform.data-source-model-config.ali.driver-class-name = com.mysql.jdbc.Driver
# 动态数据源2 在项目中所需切换进行业务处理的特殊数据源2
platform.data-source-model-config.hw.url = jdbc:mysql://127.0.0.1:3306/dynamic2?useUnicode=true&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
platform.data-source-model-config.hw.username = root
platform.data-source-model-config.hw.password = 
platform.data-source-model-config.hw.driver-class-name = com.mysql.jdbc.Driver

多数据源信息读取配置类

/**
 * 多数据源配置信息
 */
@Data
@Configuration
@ConfigurationProperties(prefix = "platform")
public class DataSourceModelConfig {
    public Map<String, DataSourceModel> dataSourceModelConfig;

    @Data
    public static class DataSourceModel {
        private String url;
        private String username;
        private String password;
        private String driverClassName;
    }


    /**
     * 获取该平台是否有配置数据源
     * @param dbTypeEnum
     * @return
     */
    public boolean getEnabled(DBTypeEnum dbTypeEnum) {
        return !ObjectUtil.isNull(this.dataSourceModelConfig.get(dbTypeEnum.getValue()));
    }
}

public enum DBTypeEnum {

    /**
     * 主数据源
     */
    master("master"),

    hw("hw"),
    /**
     * 动态数据源
     */
    ali("ali");


    private String value;

    DBTypeEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }

}

DataSourceConfigurer.class修改德鲁伊动态数据源相关配置

@Configuration
@Slf4j
@EnableConfigurationProperties({DruidDataSourceProperties.class})
public class DataSourceConfigurer {

    @Autowired
    private DruidConfigure druidConfigure;

    /**
     * 动态数据源配置
     *
     * @return
     */
    @Bean
    @Primary
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
        //自定义数据源key值，将创建好的数据源对象，赋值到targetDataSources中,用于切换数据源时指定对应key即可切换
        DruidDataSource defaultDruidDataSource = druidConfigure.druidDataSource();

        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.operation.getValue(), defaultDruidDataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(defaultDruidDataSource);
        return dynamicDataSource;
    }

    /**
     * 　配置mybatis的sqlSession连接动态数据源
     *
     * @param dynamicDataSource
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
//        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*Mapper.xml"));
        sqlSessionFactoryBean.setMapperLocations(resolveMapperLocations("classpath:/mapper/*.xml"));
        sqlSessionFactoryBean.setTransactionFactory(new JdbcTransactionFactory());
        //mybatis配置
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        mybatisConfiguration.setJdbcTypeForNull(JdbcType.NULL);
        mybatisConfiguration.setCallSettersOnNulls(true);
        mybatisConfiguration.setLogImpl(MybatisCustomLogConfigure.class);

        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        sqlSessionFactoryBean.setTransactionFactory(new SpringManagedTransactionFactory());
        GlobalConfig config = new GlobalConfig();
        GlobalConfig.DbConfig dbConfig = new GlobalConfig.DbConfig();
        dbConfig.setSelectStrategy(FieldStrategy.NOT_EMPTY);
        dbConfig.setInsertStrategy(FieldStrategy.NOT_EMPTY);
        dbConfig.setUpdateStrategy(FieldStrategy.NOT_EMPTY);
        config.setDbConfig(dbConfig);
        //去掉banner
        config.setBanner(false);
        sqlSessionFactoryBean.setGlobalConfig(config);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

    private Resource[] resolveMapperLocations(String locations) {
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        List<String> mapperLocations = Arrays.asList(locations.split(";"));
        List<Resource> resources = new ArrayList<>();
        if (!CollectionUtils.isEmpty(mapperLocations)) {
            for (String mapperLocation : mapperLocations) {
                Resource[] mappers = null;
                try {
                    mappers = resourceResolver.getResources(mapperLocation);
                } catch (IOException e) {
                    log.warn("mybatis locations load fail...");
                    continue;
                }
                resources.addAll(Arrays.asList(mappers));
            }
        }
        return resources.toArray(new Resource[resources.size()]);
    }





    public DruidDataSource createDataSource(String dbUrl, String userName, String password) {

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(dbUrl);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        // 初始化大小，最小，最大
        dataSource.setInitialSize(1);
        dataSource.setMinIdle(1);
        dataSource.setMaxActive(20);
        // 配置获取连接等待超时的时间
        dataSource.setMaxWait(60000);
        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        // 配置一个连接在池中最小生存的时间，单位是毫秒
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("SELECT 1 FROM DUAL");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        // 打开PSCache，并且指定每个连接上PSCache的大小
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        //排查问题，之后代码要删除，影响性能
//        dataSource.setRemoveAbandoned(true);
//        dataSource.setRemoveAbandonedTimeout(180);
//        dataSource.setLogAbandoned(true);
        // 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        Properties properties = new Properties();
        properties.setProperty("druid.stat.mergeSql", "true");
        properties.setProperty("druid.stat.slowSqlMillis", "5000");
        dataSource.setConnectProperties(properties);
        try {
            List<Filter> proxyFilters = new ArrayList<>();
            WallFilter wallFilter = new WallFilter();
            WallConfig config = new WallConfig();
            // 批量操作
            config.setMultiStatementAllow(true);
            config.setNoneBaseStatementAllow(true);
            config.setCommentAllow(true);
            wallFilter.setConfig(config);
            proxyFilters.add(wallFilter);
            dataSource.setFilters("stat,config");
            dataSource.setProxyFilters(proxyFilters);
            dataSource.init();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataSource;

    }

#DynamicDataSource.class 通过继承AbstractRoutingDataSource重写determineCurrentLookupKey()方法实现多数据源动态切换
public class DynamicDataSource extends AbstractRoutingDataSource {

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

    private static DynamicDataSource instance;

    // 用于存放已实例的数据源map
    private static Map<Object, Object> dataSourceMap = new ConcurrentHashMap<>();

    /**
     * 获取当前数据源
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        logger.debug("current DataSource is [{}]", DbContextHolder.getDbType());
        return DbContextHolder.getDbType();
    }

    /**
     * 设置数据源
     *
     * @param targetDataSources
     */
    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        dataSourceMap.putAll(targetDataSources);
        super.setTargetDataSources(dataSourceMap);
        // 必须添加该句，否则新添加数据源无法识别到
        super.afterPropertiesSet();
    }


    /**
     * 获取存储已实例的数据源map
     *
     * @return
     */
    public Map<Object, DataSource> getDataSourceMap() {
        return super.getResolvedDataSources();
    }

    /**
     * 是否存在当前key的 DataSource
     *
     * @param key
     * @return 存在返回 true, 不存在返回 false
     */
    public static boolean isExistDataSource(String key) {
        return dataSourceMap.containsKey(key);
    }

    /**
     * 单例方法
     *
     * @return
     */
    public static synchronized DynamicDataSource getInstance() {
        if (instance == null) {
            synchronized (DynamicDataSource.class) {
                if (instance == null) {
                    instance = new DynamicDataSource();
                }
            }
        }
        return instance;
    }
    
    

DbContextHolder.class 数据源信息上下文

public class DbContextHolder {

    private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<String>() {
        @Override
        protected String initialValue() {
            return DBTypeEnum.operation.getValue();
        }
    };

    /**
     * 设置数据源
     *
     * @param db
     */
    public static void setDbType(String db) {
        contextHolder.set(db);
    }

    /**
     * 取得当前数据源
     *
     * @return
     */
    public static String getDbType() {
        return (String) contextHolder.get();
    }

    /**
     * 清除上下文数据
     */
    public static void clearDbType() {
        contextHolder.remove();
    }


}

#动态数据源注解类
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD, ElementType.TYPE })
public @interface DataSourceSwitch {

    DBTypeEnum value() default DBTypeEnum.operation;
}

#动态数据源相关切面
@Aspect
public class DataSourceSwitchAspect {

    private static final Logger log = LoggerFactory.getLogger(DataSourceSwitchAspect.class);

    @Autowired
    private SwitchDB switchDB;

    @Pointcut("@annotation(com.test.master.config.DataSourceSwitch)")
    public void dbPointcut() {
    }


    @Before("dbPointcut()")
    public void doBefore(JoinPoint joinPoint) {
        MethodSignature method = (MethodSignature) joinPoint.getSignature();
        DataSourceSwitch dataSourceSwitch = method.getMethod().getAnnotation(DataSourceSwitch.class);
        switchDB.change(dataSourceSwitch.value());

    }

    @After("dbPointcut()")
    public void doAfter(JoinPoint joinPoint) {
        DbContextHolder.clearDbType();
    }

}

#动态数据源bean，可调用change方法进行切换数据源
@Component("switchDB")
public class SwitchDB {

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

    @Autowired
    private DataSourceConfigurer dataSourceConfigurer;
    @Autowired
    private DataSourceModelConfig dataSourceModelConfig;


    /**
     * 切换数据源
     *
     * @return
     */
    public void change(DBTypeEnum dbType) {
        //获取当前连接的数据源对象的key
        String currentKey = DbContextHolder.getDbType();
        //如果当前数据库连接已经是想要的连接，则直接返回
        if (!currentKey.equals(dbType.getValue())) {
            //判断储存动态数据源实例的map中key值是否存在
            if (DynamicDataSource.isExistDataSource(dbType.getValue())) {
                DbContextHolder.setDbType(dbType.getValue());
                logger.info("库名：[{}], 切换完毕",dbType.getValue());
            } else {
                DbContextHolder.setDbType(dbType.getValue());
                logger.info("数据源key:{} 不存在,开始创建对应数据源", dbType.getValue());
                initDataSource();
                DbContextHolder.setDbType(dbType.getValue());
            }
        }
    }

    /**
     * 新增租户库
     *
     */
    public void initDataSource() {
        Map<Object, Object> result = new HashMap<>();
        dataSourceModelConfig.dataSourceModelConfig.forEach((key, dataSourceModel) -> {
            String testUrl = dataSourceModel.getUrl() + "&user=" + dataSourceModel.getUsername() + "&password=" + dataSourceModel.getPassword();
            if (dataSourceConfigurer.jdbcTest(testUrl)) {
                DruidDataSource druidDataSource = dataSourceConfigurer.createDataSource(dataSourceModel.getUrl(), dataSourceModel.getUsername(), dataSourceModel.getPassword());
                result.put(key, druidDataSource);
            }
        });
        DynamicDataSource.getInstance().setTargetDataSources(result);

    }

    /**
     * 清除当前线程数据源
     */
    public void clearCurrentDataSource() {
        DbContextHolder.clearDbType();
    }

相关测试类代码

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestApplication.class)
public class JobTest {

    @Resource
    private SwitchDB switchdb;

    @Test
    public void test1() {
        switchDB.change(DBTypeEnum.master);
        System.out.println("切换至master数据源");
    }

    @Test
    @DataSourceSwitch(value = DBTypeEnum.master) #通过注解方式将该方法切换至指定数据源
    public void test2() {
        System.out.println("切换至master数据源");
    }


}

三、实现原理

org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource实现了DataSource接口，在调用unwrap()方法时会调用AbstractRoutingDataSource的determineTargetDataSource()方法，从中获取数据源。而determineTargetDataSource()方法将调用determineCurrentLookupKey()方法来动态获取数据源，因此我们需要做的就是编写子类继承AbstractRoutingDataSource，重写其**determineCurrentLookupKey()**方法获取我们需要切换的数据源即可。