mybatis动态切换|增加数据源_如何在程序运行时动态的向mybatis添加数据源,并切换数据源-优快云博客

本文链接：https://blog.youkuaiyun.com/it_freshman/article/details/104830797

基于ssm实现的多数据源切换： ssm-dynamic-datasource

动态切换数据源

动态切换数据源，这些数据源是在程序启动前，已经预定义。
它的工作原理是根据AbstractRoutingDataSource这个特殊的数据源，每次在请求前设置数据源id,来达到切换数据源的目的。

DynamicRoutingDataSourceKeyHolder
我们可以通过一个ThreadLocal类来实现切换当前线程的数据源id的功能。

public class DynamicRoutingDataSourceKeyHolder {
    /**
     * 本地线程共享对象
     */
    private static final ThreadLocal<String> THREAD_LOCAL = new ThreadLocal<>();

    public static void putDataSource(String name) {
        THREAD_LOCAL.set(name);
    }

    public static String getDataSource() {
        return THREAD_LOCAL.get();
    }

    public static void removeDataSource() {
        THREAD_LOCAL.remove();
    }
}

DynamicRoutingDataSource
DynamicRoutingDataSource继承AbstractRoutingDataSource,通过上文DynamicRoutingDataSourceKeyHolder中的ThreadLocal属性，来获取key.

@Slf4j
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
   /**
     * 数据源路由，此方用于产生要选取的数据源逻辑名称
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        //从共享线程中获取数据源名称
        return DynamicRoutingDataSourceKeyHolder.getDataSource();
    }
}

application.yml

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      dataBase1:
        jdbc-url: jdbc:oracle:thin:@192.168.128.108:1521:orcl
        username: ****
        password: ****
        maximum-pool-size: 20
        max-lifetime: 1800000
        idle-timeout: 30000
        connection-timeout: 30000
        data-source-properties:
          prepStmtCacheSize: 250
          prepStmtCacheSqlLimit: 2048
          cachePrepStmts: true
          useServerPrepStmts: true
        pool-name: dd_pool1
      dataBase2:
        jdbc-url: jdbc:mysql://192.168.129.44:3306/db2?useUnicode=true&characterEncoding=utf-8
        username: ****
        password: ****
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          maximum-pool-size: 20
          max-lifetime: 1800000
          idle-timeout: 30000
          connection-timeout: 30000
          data-source-properties:
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            cachePrepStmts: true
            useServerPrepStmts: true
          pool-name: dd_pool2

properties配置bean

@Component
@Data
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public class DataSourceProperties {
    private HikariDataSource dataBase1;
    private HikariDataSource dataBase2;
}

配置Spring DataSourceBean

@Configuration
@EnableScheduling
@Slf4j
public class DataSourceConfigurer {
    @Autowired
    private DataSourceProperties properties;

    @Bean(name = "dataSource")
    public DataSource dataSource() {
        /**
         * 将yaml配置的数据源 放置在 dataSourceMap 中
         */
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("dataBase1", properties.getDataBase1());
        targetDataSources.put("dataBase2", properties.getDataBase2());

        //采用是想AbstractRoutingDataSource的对象包装多数据源
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setTargetDataSources(targetDataSources);

        //设置默认的数据源，当拿不到数据源时，使用此配置
        dataSource.setDefaultTargetDataSource(properties.getDataBase1());

        return dataSource;
    }

    @Bean
    public PlatformTransactionManager txManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

定义注解–标记需要切换数据源的方法

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface TargetDataSource {
    /**
     * 此处接收的是数据源的名称
     */
    String value();
}

通过AOP实现，在数据请求前切换数据源

@Component
@Aspect
@Slf4j
@Order(1)
public class DynamicRoutingDataSourceAspect{

    /**
     * 切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点
     */
    @Before("@annotation(targetDataSource)")
    public void before(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        String dataSourceName = targetDataSource.value();
        try {
            if (dataSourceName != null) {
                //获取注解配置的值
                DynamicRoutingDataSourceKeyHolder.putDataSource(dataSourceName);
                log.debug("current thread " + Thread.currentThread().getName() + " add " + dataSourceName + " to ThreadLocal");
            } else {
                log.debug("switch datasource fail,use default");
            }
        } catch (Exception e) {
            log.error("current thread " + Thread.currentThread().getName() + " add data to ThreadLocal error", e);
        }
    }

    /**
     * 执行完切面后，将线程共享中的数据源名称清空
     */
    @After("@annotation(targetDataSource)")
    public void after(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        DynamicRoutingDataSourceKeyHolder.removeDataSource();
    }

}

动态增加数据源(运行中添加)

上文中的数据源，是在应用程序启动前就已经预定义好了。
我们还有类似这种需求：在运行过程中动态的添加数据源，并执行sql。
这种需求，第一时间会想到使用jdbc，但是结合上一节的内容，我们可以尝试下能否对上一节的内容做扩展，来实现功能。
配置数据源
这些数据源通常都是配置在第三方存储中，这里为了方便，也将它定义在配置文件中。

addition:
  hikari:
    addition1:
      jdbc-url: jdbc:oracle:thin:@192.168.128.108:1521:orcl2
      username: ****
      password: ****
      maximum-pool-size: 2
      max-lifetime: 1800000
      idle-timeout: 30000
      connection-timeout: 30000
      data-source-properties:
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        cachePrepStmts: true
        useServerPrepStmts: true
      pool-name: dd_pool_add1
    addition2:
      jdbc-url: jdbc:mysql://192.168.129.44:3306/db3?useUnicode=true&characterEncoding=utf-8
      username: ****
      password: ****
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        maximum-pool-size: 2
        max-lifetime: 1800000
        idle-timeout: 30000
        connection-timeout: 30000
        data-source-properties:
          prepStmtCacheSize: 250
          prepStmtCacheSqlLimit: 2048
          cachePrepStmts: true
          useServerPrepStmts: true
        pool-name: dd_pool_add2

properties配置bean

@Component
@Data
@ConfigurationProperties(prefix = "addition.hikari")
public class AdditionalDataSourceProperties {
    private HikariDataSource addition1;
    private HikariDataSource addition2;
}

定义注解–标记需要使用第三方数据源的方法

/**
 * 使用第三方数据源
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface AdditionalDataSource {
}

通过AOP实现，在数据请求前切换数据源

@Component
@Aspect
@Slf4j
public class AdditionalDataSourceAspect {
    /**
     * 切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点
     */
    @Before("@annotation(additionalDataSource)")
    public void before(JoinPoint joinPoint, AdditionalDataSource additionalDataSource) {
        String databaseId = String.valueOf(joinPoint.getArgs()[0]);
        try {
            DataSource dataSource = AdditionalDataSourceConfigDB.getInstance().getOrInitDataSource(databaseId);
            //如果方法上存在切换数据源的注解，则根据注解内容进行数据源切换
            if (dataSource != null ) {
                DynamicRoutingDataSourceKeyHolder.putDataSource(databaseId);
                log.debug("current thread " + Thread.currentThread().getName() + " add " + databaseId + " to ThreadLocal");
            } else {
                log.debug("switch datasource fail,use default");
            }
        } catch (Exception e) {
            log.error("current thread " + Thread.currentThread().getName() + " add data to ThreadLocal error", e);
        }
    }

    /**
     * 执行完切面后，将线程共享中的数据源名称清空
     */
    @After("@annotation(additionalDataSource)")
    public void after(JoinPoint joinPoint, AdditionalDataSource additionalDataSource) {
        DynamicRoutingDataSourceKeyHolder.removeDataSource();
    }
}

AdditionalDataSourceConfigDB

public class AdditionalDataSourceConfigDB {

    private static ConcurrentHashMap<String, DataSource> additionalDataSourceCache = new ConcurrentHashMap();

    public DataSource getOrInitDataSource(String databaseId){
        DataSource dataSource= additionalDataSourceCache.get(databaseId);
        if(dataSource != null) {
            return dataSource;
        }

        if("addition1".equals(databaseId)) {
            dataSource = ApplicationContextHolder.get().getBean(AdditionalDataSourceProperties.class).getAddition1();
        }else if("addition2".equals(databaseId)) {
            dataSource = ApplicationContextHolder.get().getBean(AdditionalDataSourceProperties.class).getAddition2();
        }else{
            log.warn("未配置的数据源{}",databaseId);
            return null;
        }
        additionalDataSourceCache.put(databaseId, dataSource);

        return dataSource;
    }

    public DataSource getAdditionalDataSource(Object databaseId) {
        return additionalDataSourceCache.get(databaseId);
    }
}

验证

Mapper

@Mapper
public interface OrderMapper /*extends tk.mybatis.mapper.common.Mapper*/ {
    /**
     * 获取数据库系统时间
     *
     * @return
     */
    @TargetDataSource("dataBase1")
    @Select("select sysdate from dual")
    LocalDateTime getDataBase1();


    @TargetDataSource("dataBase2")
    @Select("select now()")
    LocalDateTime getDataBase2();

    /**
     * 执行指定数据源iD的 给定SQL
     *
     * @param databaseId 数据源ID
     * @param sql        sql
     * @return
     */
    @AdditionalDataSource
    @Select("${sql}")
    Map additionalGet(String databaseId, @Param("sql") String sql);
}

OrderController

@RestController
@RequestMapping("/orders")
public class OrderController {

    @Autowired
    private OrderMapper orderMapper;

    @GetMapping("/dataBase1")
    public String getDataBase1(){
        return  "dataBase1:::"+orderMapper.getDataBase1().format(DateTimeFormatter.BASIC_ISO_DATE);
    }


    @GetMapping("/dataBase2")
    public String getDataBase2(){
        return "dataBase2:::"+orderMapper.getDataBase2().format(DateTimeFormatter.BASIC_ISO_DATE);
    }


    @GetMapping("/addition1")
    public Map addition1(){
        String databaseId = "addition1";
        String sql = "select * from XXX where app_id='791FD9301B6390DDE055000000000001'";
        return orderMapper.additionalGet(databaseId,sql);
    }

    @GetMapping("/addition2")
    public Map addition2(){
        String databaseId = "addition2";
        String sql = "SELECT * FROM `YYYY` where IT_NEED_NUM='IT_XQ201901020931150001'";
        return orderMapper.additionalGet(databaseId,sql);
    }
}