【springboot实现多数据源切换】

码农最酷

已于 2022-11-18 15:55:41 修改

阅读量1.2k

点赞数

于 2022-11-18 15:51:20 首次发布

本文链接：https://blog.csdn.net/fangchen1007/article/details/127923740

版权

1、背景

很多时候，项目中需要实现多数据源切换的需求，本文简单明了的总结如何在项目中实现多数据源的灵活切换

2、自定义多数据源切换注解

/**
 * 自定义多数据源切换注解
 * <p>
 * 优先级：先方法，后类，如果方法覆盖了类上的数据源类型，以方法的为准，否则以类上的为准
 *
 * @author fangchen
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataSource {
    /**
     * 切换数据源名称
     */
    public DataSourceType value() default DataSourceType.ODSDB;
}

3、定义数据源配置属性加载类

package com.sfpay.merchant.query.config.properties;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

/**
 * druid 配置属性
 *
 * @author fangchen
 */
@Configuration
public class DruidProperties {
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;

    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;

  //  @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
    //private int maxEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;

    public DruidDataSource dataSource(DruidDataSource datasource) {
        /** 配置初始化大小、最小、最大 */
        datasource.setInitialSize(initialSize);
        datasource.setMaxActive(maxActive);
        datasource.setMinIdle(minIdle);

        /** 配置获取连接等待超时的时间 */
        datasource.setMaxWait(maxWait);

        /** 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 */
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

        /** 配置一个连接在池中最小、最大生存的时间，单位是毫秒 */
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        //datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);

        /**
         * 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
         */
        datasource.setValidationQuery(validationQuery);
        /** 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 */
        datasource.setTestWhileIdle(testWhileIdle);
        /** 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnBorrow(testOnBorrow);
        /** 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnReturn(testOnReturn);
        return datasource;
    }
}

4、定义动态数据源类

package com.sfpay.merchant.query.config.properties;

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

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

/**
 * 动态数据源
 *
 * @author chen
 */
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.getDataSourceType();
    }
}

5、数据源切换处理

package com.sfpay.merchant.query.config.properties;

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

/**
 * 数据源切换处理
 *
 * @author chen
 */
public class DynamicDataSourceContextHolder {
    public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 设置数据源的变量
     */
    public static void setDataSourceType(String dsType) {
        log.info("切换到{}数据源", dsType);
        CONTEXT_HOLDER.set(dsType);
    }

    /**
     * 获得数据源的变量
     */
    public static String getDataSourceType() {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空数据源变量
     */
    public static void clearDataSourceType() {
        CONTEXT_HOLDER.remove();
    }
}

6、数据源配置处理类

package com.sfpay.merchant.query.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.config.ConfigFilter;
import com.alibaba.druid.filter.config.ConfigTools;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.sfpay.merchant.common.util.spring.SpringUtils;
import com.sfpay.merchant.query.common.enums.DataSourceType;
import com.sfpay.merchant.query.config.properties.DruidProperties;
import com.sfpay.merchant.query.config.properties.DynamicDataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.*;

/**
 * druid 配置多数据源
 *
 * @author chen
 */
@Configuration
public class DruidConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.druid.odsdb")
    public DataSource odsdbDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        //注意如果用了druid对数据库密码做了j
        dataSource.getConnectProperties().setProperty("config.decrypt","true");
        dataSource.getConnectProperties().setProperty("config.decrypt.key","MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIqNTV3TdwQJz4u8AassP5hmC0ppcEvl/U/Mt44N+VI5wnl5ZgemUGuO4Yz1n9xlEZsJKEfudW5nz+270wCCYAsCAwEAAQ==");
        ConfigFilter configFilter=new ConfigFilter();
        dataSource.setName("dm_alluser");
        dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
        List<Filter> filters = new ArrayList<>();
        filters.add(configFilter);
        dataSource.setProxyFilters(filters);
        return druidProperties.dataSource(dataSource);
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.dorisdb")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.dorisdb", name = "enabled", havingValue = "true")
    public DataSource dorisdbDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        dataSource.setName("dm_alluser1");
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource odsdbDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.ODSDB.name(), odsdbDataSource);
        setDataSource(targetDataSources, DataSourceType.DORISDB.name(), "dorisdbDataSource");
        return new DynamicDataSource(odsdbDataSource, targetDataSources);
    }

    /**
     * 设置数据源
     *
     * @param targetDataSources 备选数据源集合
     * @param sourceName        数据源名称
     * @param beanName          bean名称
     */
    public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName) {
        try {
            DataSource dataSource = SpringUtils.getBean(beanName);
            targetDataSources.put(sourceName, dataSource);
        } catch (Exception e) {
        }
    }
}

7、测试类

package com.sfpay.merchant.query.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sfpay.merchant.query.common.enums.DataSourceType;
import com.sfpay.merchant.query.config.annotation.DataSource;
import com.sfpay.merchant.query.dal.dao.AccountWaterMapper;
import com.sfpay.merchant.query.dal.entity.AccountWaterDO;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * @describe:
 * @author:01410843
 * @Date 2022/11/18 10:27
 **/
@Controller
@RequestMapping("/test")
public class TestController {

    @Resource
    private AccountWaterMapper accountWaterMapper;

    @RequestMapping(value = "/odsdb")
    @ResponseBody
    public  List<AccountWaterDO> odsdb(HttpServletRequest request){
        QueryWrapper<AccountWaterDO>  queryWrapper =new QueryWrapper();
        queryWrapper.lambda().eq(AccountWaterDO::getBusinessSn,"83262000002001923301");
        List<AccountWaterDO> list=accountWaterMapper.selectList(queryWrapper);
        return list;
    }

    @RequestMapping(value = "/dorisdb")
    @ResponseBody
    @DataSource(value = DataSourceType.DORISDB)
    public  List<AccountWaterDO> dorisdb(HttpServletRequest request){
        QueryWrapper<AccountWaterDO>  queryWrapper =new QueryWrapper();
        queryWrapper.lambda().eq(AccountWaterDO::getBusinessSn,"20210915103002159572");
        List<AccountWaterDO> list=accountWaterMapper.selectList(queryWrapper);
        return list;
    }
}

8、apllication.rpoperties配置项

#ods数据源(ORACLE)
spring.datasource.druid.odsdb.url=jdbc:oracle:thin:@//xxx:1521/odsdb
spring.datasource.druid.odsdb.username=dm_alluser
spring.datasource.druid.odsdb.password=QnZ86RsUeM0r3by9SwWJhwWwbDmC3kpPW3YAOtsj7tySxAl5MkcOkFGQaZWMcYHSEpRZFMDs8MO1/fxxh3PihA==

#doris数据源(MYSQL)
spring.datasource.druid.dorisdb.url=jdbc:mysql://xxxx:9030/fis_pay_bdp?useUnicode=true
spring.datasource.druid.dorisdb.username=fis_pay_bdp
spring.datasource.druid.dorisdb.password=fis_pay_bdp@123
spring.datasource.druid.dorisdb.enabled=true
#连接池的设置
spring.datasource.druid.name=dm_alluser
#初始化时建立物理连接的个数
spring.datasource.druid.initial-size=300
#最小连接池数量
spring.datasource.druid.min-idle=300
#最大连接池数量 maxIdle已经不再使用
spring.datasource.druid.max-active=300
#获取连接时最大等待时间，单位毫秒
spring.datasource.druid.max-wait=120000
#申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
spring.datasource.druid.test-while-idle=true
#既作为检测的间隔时间又作为testWhileIdel执行的依据
spring.datasource.druid.time-between-eviction-runs-millis=120000
#销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接
spring.datasource.druid.min-evictable-idle-time-millis=120000
#用来检测连接是否有效的sql 必须是一个查询语句
#mysql中为 select 'x'
#oracle中为 select 1 from dual
spring.datasource.druid.validation-query=select 1 from dual
#申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-borrow=false
#归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-return=false
#当数据库抛出不可恢复的异常时,抛弃该连接
#spring.datasource.druid.exception-sorter=true
#是否缓存preparedStatement,mysql5.5+建议开启
#spring.datasource.druid.pool-prepared-statements=true
#当值大于0时poolPreparedStatements会自动修改为true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20