动态切换mysql和clickhouse

1.DatasourceHolder

public class DatasourceHolder {
private static final ThreadLocal DB_INFO = new ThreadLocal<>();

public static void setDbInfo(DataSourceType dataSourceType) {
    DB_INFO.set(dataSourceType);
}

public static DataSourceType getDbInfo() {
    return DB_INFO.get();
}

public static void remove() {
    DB_INFO.remove();
}

}

2。DataSourceInterceptor

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import saas.common.exception.ApplicationException;
import saas.common.utils.DingTalkUtil;

import java.util.Properties;

@Intercepts(
@Signature(type = Executor.class, method = “query”, args = {MappedStatement.class, Object.class, RowBounds.class, Result.class})
)
public class DataSourceInterceptor implements Interceptor {

@Override
public Object intercept(Invocation invocation) throws Throwable {
    logRecord(invocation);
    if (!DataSourceScan.ckEnable) {
        return invocation.proceed();
    }
    MappedStatement statement = (MappedStatement) (invocation.getArgs()[0]);
    DataSourceType dataSourceType = DataSourceScan.MAPPER_METHOD_MAP.get(statement.getId());
    if (dataSourceType != null) {
        DatasourceHolder.setDbInfo(dataSourceType);
    } else {
        DatasourceHolder.setDbInfo(DataSourceType.MYSQL);
    }
    if (DataSourceType.CK == dataSourceType) {
      
        try {
            return invocation.proceed();
        } catch (Exception e) {
            logger.error("ck 异常,关闭ck", e);
            DataSourceScan.ckEnable = false;
            DingTalkUtil.sendByAccessTokenOrDefault(DataSourceScan.getPageTitle() + ":CK异常:",
                    e.toString(),
                    DataSourceScan.getDingAccessToken());
            ApplicationException.Exception("重新查询！");
            return null;
        }
    } else {
        return invocation.proceed();
    }
}

private void logRecord(Invocation invocation){
    try {
        MappedStatement statement = (MappedStatement) (invocation.getArgs()[0]);

// logger.info(“调用接口：{}”,statement.getId());
}catch (Exception e){
logger.error(“获取调用接口异常”,e);
}
}

@Override
public Object plugin(Object target) {
    if (target instanceof Executor) {
        //返回代理
        return Plugin.wrap(target, this);
    } else {
        return target;
    }
}

@Override
public void setProperties(Properties properties) {

}

}

3.DataSourceScan

import cn.hutool.core.collection.CollectionUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternUtils;
import org.springframework.core.type.classreading.CachingMetadataReaderFactory;
import org.springframework.core.type.classreading.MetadataReader;
import org.springframework.core.type.classreading.MetadataReaderFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**

• 启动扫描注解类
  /
  @Component
  public class DataSourceScan implements InitializingBean {
  private static final Logger logger = LoggerFactory.getLogger(DataSourceScan.class);
  /*

  • 扫描路径
    /
    private static final String PATH = "classpath:saas/dao/**/.class";
    /**
  • mapper映射关系
    /
    public static final Map<String, DataSourceType> MAPPER_METHOD_MAP = new ConcurrentHashMap<>();
    /*
  • ck是否启用
    /
    public static boolean ckIsEnable = false;
    /*
  • token
    */
    private static String dingToken;

  /**

  • pageTitle
    */
    private static String pageTitle;

  private final ResourceLoader defaultResourceLoader = new PathMatchingResourcePatternResolver();

  private final ResourcePatternResolver resolver = ResourcePatternUtils.getResourcePatternResolver(defaultResourceLoader);
  private final MetadataReaderFactory metadataReaderFactory = new CachingMetadataReaderFactory(defaultResourceLoader);

  private ResourceLoader resourceLoader;
  @Autowired
  @Lazy
  private SysPageConfigServiceImpl configService;

  /**

  • ck是否启动
    /
    @Value(“${spring.datasource.clickhouse.enable:false}”)
    private boolean ckEnable;
    /*
  • ding
    */
    @Value(“${spring.datasource.clickhouse.dingToken:}”)
    private String dingToken;

  private void recordDataType(Class<?> clazz) {
  SpecifyDataSource specifyDataSource = clazz.getAnnotation(SpecifyDataSource.class);
  String className = clazz.getName();
  DataSourceType specifyDataSourceType = DataSourceType.MYSQL;
  if (specifyDataSource != null) {
  specifyDataSourceType = specifyDataSource.value();
  }
  for (Method method : clazz.getMethods()) {
  SpecifyDataSource methodDataSource = method.getAnnotation(SpecifyDataSource.class);
  if (methodDataSource != null) {
  MAPPER_METHOD_MAP.put(className + “.” + method.getName(), methodDataSource.value());
  } else {
  MAPPER_METHOD_MAP.put(className + “.” + method.getName(), specifyDataSourceType);
  }
  }
  }

  @Override
  public void afterPropertiesSet() throws Exception {
  ckIsEnable = this.ckEnable;
  dingAccessToken = accessToken;
  String dbPageTitle = this.configService.getColumnValue(“page_title”);
  if(StringUtils.isNotBlank(dbPageTitle)){
  pageTitle = dbPageTitle;
  }
  if (!ckIsEnable) {
  return;
  }
  logger.info(“ckEnable=true,开始扫描数据源注解！”);
  try {
  Resource[] resources = resolver.getResources(PATH);
  MetadataReader metadataReader = null;
  for (Resource resource : resources) {
  if (resource.isReadable()) {
  metadataReader = metadataReaderFactory.getMetadataReader(resource);
  Class<?> clazz = Class.forName(metadataReader.getClassMetadata().getClassName());
  recordDataType(clazz);
  }
  }
  } catch (Exception e) {
  ApplicationException.throwApplicationException(“动态数据源切换，初始化失败！”);
  }
  }

  public static String getDingAccessToken() {
  return dingAccessToken;
  }

  public static String getPageTitle() {
  return pageTitle;
  }
  }

4.DataSourceType

public enum DataSourceType {
/**
* mysql
/
MYSQL,
/*
* click house
*/
CK;
}

5.DynamicRoutingDataSource

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

/**

*/
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
private final static Logger logger = LoggerFactory.getLogger(DynamicRoutingDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
DataSourceType dbInfo = DatasourceHolder.getDbInfo();
if (dbInfo != null) {
logger.debug(“切换数据源为{}”,dbInfo);
DatasourceHolder.remove();
}
return dbInfo;
}
}

6.DataSourceProperties

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**

• clickhouse配置
  /
  @Component
  @ConfigurationProperties(prefix = “spring.datasource.clickhouse”)
  public class DataSourceProperties {
  /*

  • JDBC driver
    /
    private String driverClassName;
    /*
  • JDBC url 地址
    /
    private String url;
    /*
  • JDBC 用户名
    /
    private String username;
    /*
  • JDBC 密码
    /
    private String password;
    /*
  • 初始化链接
    /
    private Integer initialSize = 5;
    /*
  • 最小链接
    /
    private Integer minIdle = 5;
    /*
  • 最大链接
    /
    private Integer maxActive = 100;
    /*
  • 等待时间
    /
    private Integer maxWait = 5000;
    /*
  • 配置一个连接在池中最小生存的时间，单位是毫秒
    /
    private long timeBetweenEvictionRunsMillis = 300000L;
    /*
  • 配置一个连接在池中最小生存的时间，单位是毫秒
    */
    private long minEvictableIdleTimeMillis = 10000L;
    private boolean testWhileIdle = false;
    private boolean testOnBorrow = false;
    private boolean testOnReturn = false;
    private String validationQuery;

  public String getDriverClassName() {
  return driverClassName;
  }

  public void setDriverClassName(String driverClassName) {
  this.driverClassName = driverClassName;
  }

  public String getUrl() {
  return url;
  }

  public void setUrl(String url) {
  this.url = url;
  }

  public String getUsername() {
  return username;
  }

  public void setUsername(String username) {
  this.username = username;
  }

  public String getPassword() {
  return password;
  }

  public void setPassword(String password) {
  this.password = password;
  }

  public Integer getInitialSize() {
  return initialSize;
  }

  public void setInitialSize(Integer initialSize) {
  this.initialSize = initialSize;
  }

  public Integer getMinIdle() {
  return minIdle;
  }

  public void setMinIdle(Integer minIdle) {
  this.minIdle = minIdle;
  }

  public Integer getMaxActive() {
  return maxActive;
  }

  public void setMaxActive(Integer maxActive) {
  this.maxActive = maxActive;
  }

  public Integer getMaxWait() {
  return maxWait;
  }

  public void setMaxWait(Integer maxWait) {
  this.maxWait = maxWait;
  }

  public long getTimeBetweenEvictionRunsMillis() {
  return timeBetweenEvictionRunsMillis;
  }

  public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
  this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
  }

  public long getMinEvictableIdleTimeMillis() {
  return minEvictableIdleTimeMillis;
  }

  public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
  this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
  }

  public boolean getTestWhileIdle() {
  return testWhileIdle;
  }

  public void setTestWhileIdle(boolean testWhileIdle) {
  this.testWhileIdle = testWhileIdle;
  }

  public boolean getTestOnBorrow() {
  return testOnBorrow;
  }

  public void setTestOnBorrow(boolean testOnBorrow) {
  this.testOnBorrow = testOnBorrow;
  }

  public boolean getTestOnReturn() {
  return testOnReturn;
  }

  public void setTestOnReturn(boolean testOnReturn) {
  this.testOnReturn = testOnReturn;
  }

  public String getValidationQuery() {
  return validationQuery;
  }

  public void setValidationQuery(String validationQuery) {
  this.validationQuery = validationQuery;
  }
  }

7.DataSourceConfig

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import saas.dynamicdatasource.DataSourceType;
import saas.dynamicdatasource.DynamicRoutingDataSource;

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

/**

**/
@Configuration
@MapperScan(basePackages = {“saas.dao”}, sqlSessionFactoryRef = “sqlSessionFactory”)
public class DataSourceConfig {
private static final Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);

@Value("${mybatis.mapper-locations}")
private String mapperLocations;
@Value("${mybatis.config-location}")
private String configLocation;

@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid")
public DruidDataSource druidDataSource() {
    return DruidDataSourceBuilder.create().build();
}

@Bean(name = "ckDataSource")
@ConditionalOnProperty(prefix = "spring.datasource.clickhouse", name = "enable", havingValue = "true")
public DruidDataSource ckDataSource(DataSourceProperties dataSourceProperties) {
    DruidDataSource datasource = new DruidDataSource();
    datasource.setUrl(dataSourceProperties.getUrl());
    datasource.setDriverClassName(dataSourceProperties.getDriverClassName());
    datasource.setUsername(dataSourceProperties.getUsername());
    datasource.setPassword(dataSourceProperties.getPassword());
    datasource.setMaxActive(dataSourceProperties.getMaxActive());
    datasource.setMinIdle(dataSourceProperties.getMinIdle());
    datasource.setInitialSize(dataSourceProperties.getInitialSize());
    datasource.setMaxWait(dataSourceProperties.getMaxWait());
    datasource.setMinEvictableIdleTimeMillis(dataSourceProperties.getMinEvictableIdleTimeMillis());
    datasource.setTimeBetweenEvictionRunsMillis(dataSourceProperties.getTimeBetweenEvictionRunsMillis());
    datasource.setTestOnBorrow(dataSourceProperties.getTestOnBorrow());
    datasource.setTestOnReturn(dataSourceProperties.getTestOnReturn());
    datasource.setTestWhileIdle(dataSourceProperties.getTestWhileIdle());
    datasource.setValidationQuery(dataSourceProperties.getValidationQuery());
    logger.info("ck-DataSource 初始化完成！");
    return datasource;
}

@Bean
@ConditionalOnBean(name = "ckDataSource", value = DruidDataSource.class)
public DynamicRoutingDataSource dynamicRoutingDataSourceCk(
        @Qualifier("dataSource") DataSource mysqlDataSource,
        @Qualifier("ckDataSource") DataSource ckDataSource) {
    Map<Object, Object> targetDataSources = new HashMap<>(8);
    targetDataSources.put(DataSourceType.MYSQL, mysqlDataSource);
    targetDataSources.put(DataSourceType.CK, ckDataSource);
    DynamicRoutingDataSource myRoutingDataSource = new DynamicRoutingDataSource();
    myRoutingDataSource.setDefaultTargetDataSource(mysqlDataSource);
    myRoutingDataSource.setTargetDataSources(targetDataSources);
    return myRoutingDataSource;
}

@Bean
@ConditionalOnMissingBean
public DynamicRoutingDataSource dynamicRoutingDataSource(
        @Qualifier("dataSource") DataSource mysqlDataSource) {
    Map<Object, Object> targetDataSources = new HashMap<>(8);
    targetDataSources.put(DataSourceType.MYSQL, mysqlDataSource);
    DynamicRoutingDataSource myRoutingDataSource = new DynamicRoutingDataSource();
    myRoutingDataSource.setDefaultTargetDataSource(mysqlDataSource);
    myRoutingDataSource.setTargetDataSources(targetDataSources);
    return myRoutingDataSource;
}

@Bean
public SqlSessionFactory sqlSessionFactory(DynamicRoutingDataSource dynamicRoutingDataSource) throws Exception {
    //使用MybatisSqlSessionFactory替换原生SqlSessionFactory
    MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dynamicRoutingDataSource);
    sqlSessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource(configLocation));
    sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));

    return sqlSessionFactoryBean.getObject();
}

@Bean
public SqlSessionTemplate sqlSessionTemplateOne(SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
}

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

}

8.mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>