后端设置双数据源mysql+clickhouse

本文将介绍如何设置双数据源mysql+clickhouse，一般正常的只有mysql数据库，现在新增clickhouse数据源之后需要让项目识别出哪些时mysql数据源，哪些是clickhouse数据源。以下是具体的介绍

一、核心原理

1.动态路由

通过Spring的AbstractRoutingDataSource实现多数据源动态切换，根据业务逻辑或注解自动选择MySQL或ClickHouse数据源。

2.连接池隔离

每个数据源独立维护自己的连接池（如HikariCP），避免资源竞争。

3.差异化配置

MySQL：适合事务性操作（OLTP）

ClickHouse：适合分析查询（OLAP），需关闭事务支持（因ClickHouse本身不支持事务）

需注意两种数据库的SQL语法差异（如ClickHouse不支持JOIN优化、自增ID等）。

二、具体实现步骤

文件目录结构如图所示，mysql和clickhouse尽量放在不同的文件夹，方便区分。

1.pom.xml文件添加依赖

<!-- MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>


        <!-- ClickHouse -->
 <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.3.2</version>
 </dependency>

<!-- 数据源管理 -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

2.配置多数据源application.yml文件

spring:
  datasource:
    primary:
      jdbc-url: jdbc:mysql://192.168.245.142:3306/mysql.db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8&allowPublicKeyRetrieval=true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: '123456'
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        minimum-idle: 5
        maximum-pool-size: 15
        idle-timeout: 30000
        pool-name: PrimaryHikariCP
        
    clickhouse:
      jdbc-url: jdbc:clickhouse://192.168.245.142:8123/ch.db
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      username: admin
      password: admin
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        minimum-idle: 5
        maximum-pool-size: 15
        idle-timeout: 30000
        pool-name: ClickHouseHikariCP

3.数据源配置类

DataSourceConfig,PrimaryMybatisConfig,ClickHouseMybatisConfig 这样进行配置的好处是在mapper层和对应的xml文件中可以方便的设置其数据来源

package com.example.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name = "clickHouseDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse")
    public DataSource clickHouseDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
}

package com.example.config;

import jakarta.annotation.Resource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;

@Configuration
@MapperScan(
        basePackages = "com.example.mapper",
        sqlSessionTemplateRef = "primarySqlSessionTemplate"
)
public class PrimaryMybatisConfig {
    @Resource(name = "primaryDataSource")
    private DataSource primaryDataSource;

    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();  // 改用 SqlSessionFactoryBean
        bean.setDataSource(primaryDataSource);

        // 设置MyBatis配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(configuration);

        // 设置mapper位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));

        return bean.getObject();
    }

    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.example.config;

import jakarta.annotation.Resource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(
    basePackages = "com.example.mapperclickhouse",
    sqlSessionTemplateRef = "clickHouseSqlSessionTemplate"
)
public class ClickHouseMybatisConfig {
    
    @Resource(name = "clickHouseDataSource")
    private DataSource clickHouseDataSource;

    @Bean(name = "clickHouseSqlSessionFactory")
    public SqlSessionFactory clickHouseSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(clickHouseDataSource);
        
        // 设置MyBatis配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(configuration);
        
        // 设置mapper位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
            .getResources("classpath:mapperclickhouse/*.xml"));
            
        return bean.getObject();
    }

    @Bean(name = "clickHouseSqlSessionTemplate")
    public SqlSessionTemplate clickHouseSqlSessionTemplate(
            @Qualifier("clickHouseSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4.SpringbootApplication启动类添加注解MapperScan

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan({"com.example.mapper", "com.example.mapperclickhouse"})
public class SpringbootApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootApplication.class, args);
    }

}

以上就是双数据源的全部过程了，需要注意相关的配置文件是否正确哦，entity和 service层对应的方法实现与mysql的基本一致