记录一次springboot单数据源mysql到多数据源mysql+clickhouse的接入过程,项目使用seata做事务管理

1. 单数据源配置:

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://192.168.5.1:3306/aaa?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initial-size: 10
    min-idle: 3
    max-active: 100
    max-wait: 60000
    time-between-eviction-runs-millis: 60000
    min-evictable-idle-time-millis: 300000
    validation-query: SELECT 1 FROM DUAL
    test-while-idle: true
    test-on-borrow: false
    test-on-return: false
    pool-prepared-statements: true
    max-pool-prepared-statement-per-connection-size: 20
    filters: stat,wall
    use-global-data-source-stat: true
    connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

mybatis:
  type-aliases-package: com.aa.bb.*.dao;com.aa.*.base;com.aa.bb.*.entity;
  mapper-locations: classpath:mapper/*Mapper.xml,classpath:mapper/**/*Mapper.xml

seata:
  enabled: true
  application-id: ${spring.application.name} #服务名
  tx-service-group: xxxxxxxxxxxxx
  #启用自动数据源代理
  enable-auto-data-source-proxy: true
  ..........

2. 改成多数据源配置
   2.1 pom加入依赖

<!--clickhouse-->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.2.4</version>
        </dependency>
        <!--多数据源-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

2.2 配置文件

spring:
  autoconfigure:
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    dynamic:
      seata: true
      primary: mysql
      strict: false
      datasource:
        mysql:
          username: root
          password: zprd
          url: jdbc:mysql://192.168.5.1:3306/aaa?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
          driver-class-name: com.mysql.cj.jdbc.Driver
        clickhouse:
          url: jdbc:clickhouse://192.168.5.1:8123
          username: default
          password: admin
          driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      druid:
        initial-size: 10
        min-idle: 3
        max-active: 100
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat
        use-global-data-source-stat: true
        connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
mybatis:
  type-aliases-package: com.aa.bb.*.dao;com.aa.*.base;com.aa.bb.*.entity;
  mapper-locations: classpath:mapper/*Mapper.xml,classpath:mapper/**/*Mapper.xml

seata:
  enabled: true
  application-id: ${spring.application.name} #服务名
  tx-service-group: xxxxxxxxxxxxx
  #启用自动数据源代理
  enable-auto-data-source-proxy: false
  ........

-----如果这里没有用到seata,那么到这里就结束了,配置文件去掉seata相关配置------

注: 这里seata关闭了自动代理 enable-auto-data-source-proxy: false
切换数据源: 使用@DS注解

@DS("clickhouse")
public interface EventTrackingDao{

    Integer findCount();
}

然后启动项目,报错:

这个异常大致情况就是项目使用了seata, 但是clickhouse不支持事务,所以初始化失败,

3. 继续干, 我先关闭seata,看项目能不能跑起来: seata: false
   结果不行,还是报这个错,按理说不应该,
   然后继续关: enabled: false
   这次成了,项目正常启动,也能正常访问数据库
   其实这里的原因就是: enable-auto-data-source-proxy: false, 这个配置不对,
   更改为: enableAutoDataSourceProxy: false就对了

4. 集成clickhouse不能影响原有项目运行,seata也不能关闭,还得继续搞
   大概思路就是: 关闭seata自动数据源代理,然后改为手动代理方式,指定mysql使用seata代理,clickhouse不使用代理.
   4.1 配置文件

spring.datasource.master.url=jdbc:mysql://192.168.5.1:3306/aaa?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&allowMultiQueries=true
spring.datasource.master.username=root
spring.datasource.master.password=root
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.master.initialSize=10
spring.datasource.master.minIdle=3
spring.datasource.master.maxActive=100
spring.datasource.master.maxWait=60000
spring.datasource.master.removeAbandoned=true
spring.datasource.master.removeAbandonedTimeout=180
spring.datasource.clickhouse.url=jdbc:clickhouse://192.168.5.1:8123
spring.datasource.clickhouse.username=default
spring.datasource.clickhouse.password=admin
spring.datasource.clickhouse.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.clickhouse.type=com.alibaba.druid.pool.DruidDataSource

seata:
  enabled: true
  application-id: ${spring.application.name} #服务名
  tx-service-group: xxxxxxxxx
  # 启用自动数据源代理
  enableAutoDataSourceProxy: false
  ........

4.2 创建文件:config/mybatis.properties

mybatis.typeAliasesPackage=com.aa.bb.*.dao;com.aa.*.base;com.aa.bb.*.entity;
mybatis.mapperLocations=classpath:mapper/*Mapper.xml,classpath:mapper/**/*Mapper.xml

4.3 创建文件:DataSourceConfigurer

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.github.pagehelper.PageInterceptor;
import io.seata.rm.datasource.DataSourceProxy;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.core.type.classreading.CachingMetadataReaderFactory;
import org.springframework.core.type.classreading.MetadataReader;
import org.springframework.core.type.classreading.MetadataReaderFactory;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.util.ClassUtils;

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

@Slf4j
@Configuration
@PropertySource("classpath:config/mybatis.properties")
public class DataSourceConfigurer  implements EnvironmentAware {

    private Environment env;

    @Override
    public void setEnvironment(Environment environment) {
        // spring自动注入 会报空指针
        this.env = environment;
    }


    @Bean("clickhouse")
    @ConfigurationProperties("spring.datasource.clickhouse")
    public DataSource clickhouseDataSource() {
        return new DruidDataSource();
    }


    @Bean("originalMysql")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource mysqlDataSource() {
        return new DruidDataSource();
    }

    @Primary
    @Bean("mysql")
    public DataSource dataSource1(@Qualifier("originalMysql")DataSource dataSource) {
        return new DataSourceProxy(dataSource);
    }


    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource(@Qualifier("mysql") DataSource mysql,
                                        @Qualifier("clickhouse") DataSource clickhouse) {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        dynamicRoutingDataSource.addDataSource("mysql", mysql);
        dynamicRoutingDataSource.addDataSource("clickhouse", clickhouse);
        dynamicRoutingDataSource.setPrimary("mysql");
        dynamicRoutingDataSource.setSeata(true);
        return dynamicRoutingDataSource;
    }

    /**
     * 解决引入seata后@Transactional注解失效的问题
     */
    @Bean("txManager")
    public DataSourceTransactionManager txManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("dynamicDataSource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        //解决DefaultVFS在获取jar上存在的问题
        factory.setVfs(SpringBootVFS.class);

        //别名
        String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
        String mapperLocations = env.getProperty("mybatis.mapperLocations");
        typeAliasesPackage=setTypeAliasesPackage(typeAliasesPackage);

        factory.setTypeAliasesPackage(typeAliasesPackage);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        //驼峰
        configuration.setMapUnderscoreToCamelCase(true);
        factory.setConfiguration(configuration);
        //添加XML目录
        factory.setMapperLocations(resolveMapperLocations(mapperLocations));
        return factory.getObject();
    }

    public Resource[] resolveMapperLocations(String mapperLocations){
        ResourcePatternResolver resourceResolver =new PathMatchingResourcePatternResolver();
        List<String> strings = Arrays.asList(mapperLocations.split(","));
        List<Resource> resources =new ArrayList();
        if(mapperLocations != null){
            for(String mapperLocation : strings){
                try{
                    Resource[] mappers = resourceResolver.getResources(mapperLocation);
                    resources.addAll(Arrays.asList(mappers));
                }catch(IOException e){
                }
            }
        }
        return resources.toArray(new Resource[resources.size()]);
    }

    private static final String DEFAULT_RESOURCE_PATTERN = "*.class";

    private static String setTypeAliasesPackage(String typeAliasesPackage) {
        ResourcePatternResolver resolver = (ResourcePatternResolver) new PathMatchingResourcePatternResolver();
        MetadataReaderFactory metadataReaderFactory = new CachingMetadataReaderFactory(
                resolver);
        String[] packages = typeAliasesPackage.split(";");
        try {
            List<String> result = new ArrayList<>();

            for (String p:packages){
                typeAliasesPackage = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX
                        + ClassUtils.convertClassNameToResourcePath(p)
                        + "/" + DEFAULT_RESOURCE_PATTERN;
                Resource[] resources  = resolver.getResources(typeAliasesPackage);
                if (resources.length > 0) {
                    MetadataReader metadataReader ;
                    for (Resource resource : resources) {
                        if (resource.isReadable()) {
                            metadataReader = metadataReaderFactory.getMetadataReader(resource);
                            try {
                                log.info(Class.forName(metadataReader.getClassMetadata().getClassName()).getPackage().getName());
                                result.add(Class
                                        .forName(
                                                metadataReader.getClassMetadata()
                                                        .getClassName())
                                        .getPackage().getName());
                            } catch (ClassNotFoundException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }

            if (result.size() > 0) {
                HashSet<String> h = new HashSet<>(result);
                result.clear();
                result.addAll(h);
                typeAliasesPackage=String.join(",",(String[]) result.toArray(new String[0]));
                log.info("mybatis扫描通配符:"+typeAliasesPackage+"=========================================");
            } else {
                throw new RuntimeException(
                        "mybatis typeAliasesPackage 路径扫描错误,参数typeAliasesPackage:"
                                + typeAliasesPackage + "未找到任何包");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return typeAliasesPackage;
    }
}

配置完成OK
启动项目,然后报错:

嗯,这个啥错咱也不知道,只能看到dynamic这个多数据源的jar包里报错了,OK,可能是版本问题
调整jar版本:

<!--多数据源-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

启动项目,正常,搞定收工.

交流Q: 466129139