SpringBoot实现mysql与clickhouse多数据源

一、我们来实现一个mysql与clickhouse多数据源配置

二、数据源配置

# 指定服务名称
spring:application:name: demobigdatadatasource:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useAffectedRows=true&allowPublicKeyRetrieval=trueusername: rootpassword: 123456type: com.alibaba.druid.pool.DruidDataSourcedruid:# 数据源  clickhouseclickhouse:driverClassName: com.clickhouse.jdbc.ClickHouseDriverurl: jdbc:clickhouse://192.168.42.142:8123/bigdatausername: defaultpassword: 123456initialSize: 10maxActive: 100minIdle: 10maxWait: 6000

三、MysqlDuridConfig

import javax.sql.DataSource;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import com.alibaba.druid.pool.DruidDataSource;@Configuration
@MapperScan(basePackages = "org.demoflowable.mysql.dao")
public class MysqlDuridConfig {@javax.annotation.ResourceMysqlJdbcParamConfig mysqlJdbcParamConfig;@Bean("mysqlDataSource")@Primarypublic DataSource mysqlDataSource() {DruidDataSource datasource = new DruidDataSource();datasource.setUrl(mysqlJdbcParamConfig.getUrl());datasource.setDriverClassName(mysqlJdbcParamConfig.getDriverClassName());datasource.setUsername(mysqlJdbcParamConfig.getUsername());datasource.setPassword(mysqlJdbcParamConfig.getPassword());return datasource;}@Bean("mysqlTransactionManager")@Primarypublic DataSourceTransactionManager getDataSourceTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean("sqlSessionFactory")@Primarypublic SqlSessionFactory getSqlSessionManager(@Qualifier("mysqlDataSource") DataSource dataSource)throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);Resource[] resource = new PathMatchingResourcePatternResolver().getResources("classpath:META-INF/mapper/mysql/*.xml");bean.setMapperLocations(resource);Resource configLocation=new PathMatchingResourcePatternResolver().getResource("classpath:META-INF/spring/mybatis-config.xml");bean.setConfigLocation(configLocation);return bean.getObject();}
}

四、MysqlJdbcParamConfig

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;@Component
@ConfigurationProperties(prefix = "spring.datasource")
public class MysqlJdbcParamConfig {private String driverClassName;private String url;private String username;private String password;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;}
}

五、ClickHouseJdbcParamConfig

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;@Component
@ConfigurationProperties(prefix = "spring.datasource.druid.clickhouse")
public class ClickHouseJdbcParamConfig {private String driverClassName;private String url;private Integer initialSize;private Integer maxActive;private Integer minIdle;private Integer maxWait;private String username;private String password;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 Integer getInitialSize() {return initialSize;}public void setInitialSize(Integer initialSize) {this.initialSize = initialSize;}public Integer getMaxActive() {return maxActive;}public void setMaxActive(Integer maxActive) {this.maxActive = maxActive;}public Integer getMinIdle() {return minIdle;}public void setMinIdle(Integer minIdle) {this.minIdle = minIdle;}public Integer getMaxWait() {return maxWait;}public void setMaxWait(Integer maxWait) {this.maxWait = maxWait;}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;}
}

六、ClickHouseConfig 

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import com.alibaba.druid.pool.DruidDataSource;@Configuration
@ConfigurationProperties
@MapperScan(basePackages = "org.demoflowable.clickhouse.dao", sqlSessionFactoryRef = "clickhouseSqlSessionFactory")
public class ClickHouseConfig {@javax.annotation.Resourceprivate ClickHouseJdbcParamConfig jdbcParamConfig;@Bean("clickDataSource")public DataSource dataSource() {DruidDataSource datasource = new DruidDataSource();datasource.setUrl(jdbcParamConfig.getUrl());datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());datasource.setInitialSize(jdbcParamConfig.getInitialSize());datasource.setMinIdle(jdbcParamConfig.getMinIdle());datasource.setMaxActive(jdbcParamConfig.getMaxActive());datasource.setMaxWait(jdbcParamConfig.getMaxWait());datasource.setUsername(jdbcParamConfig.getUsername());datasource.setPassword(jdbcParamConfig.getPassword());return datasource;}@Bean("clickTransactionManager")public DataSourceTransactionManager getDataSourceTransactionManager(@Qualifier("clickDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean("clickhouseSqlSessionFactory")public SqlSessionFactory getSqlSessionManager(@Qualifier("clickDataSource") DataSource dataSource)throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);Resource[] resource = new PathMatchingResourcePatternResolver().getResources("classpath:META-INF/mapper/clickhouse/*.xml");bean.setMapperLocations(resource);return bean.getObject();}
}

七、启动类Application

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;/*** @Description: 启动类*/
@SpringBootApplication
@ComponentScan({ "org.demoflowable" })
public class Application {/*** @Title: main* @Description: 启动类* @param args* @date 2023-11-08 01:49:23*/public static void main(String[] args) {SpringApplication.run(Application.class, args);}}