springboot + mybatis-plus + clickhouse + mysql 多数据源配置及问题记录

1.引入依赖

        <properties>
            <java.version>1.8</java.version>
            <mybatis-plus.version>3.4.1</mybatis-plus.version>
        </properties>
        <!--  mybatis-plus  -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <!-- 使用MybatisSqlSessionFactoryBean需要添加此依赖，不然会报错：sun.reflect.annotation.TypeNotPresentExceptionProxy -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.2.4</version>
        </dependency>
        <!-- pagehelper版本要与mybatis-plus相对应，版本低会报错：net.sf.jsqlparser.statement.select.PlainSelect.getGroupByColumnReferences()Ljava/util/List; -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

2.配置文件

#mysql
spring.datasource.mysql.url = jdbc:mysql://ip:3306/xxdb?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&zeroDateTimeBehavior=convertToNull
spring.datasource.mysql.driverClassName = com.mysql.jdbc.Driver
spring.datasource.mysql.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.mysql.username = xxxx
spring.datasource.mysql.password = xxxxxx
spring.datasource.mysql.initial-size = 10
spring.datasource.mysql.max-active = 200
spring.datasource.mysql.time-between-eviction-runs-millis = 60000
spring.datasource.mysql.min-evictable-idle-time-millis = 300000
spring.datasource.mysql.validation-query = SELECT 1 FROM DUAL
spring.datasource.mysql.test-while-idle = true
spring.datasource.mysql.test-on-borrow = false
spring.datasource.mysql.test-on-return = false
spring.datasource.mysql.pool-prepared-statements = true
spring.datasource.mysql.max-pool-prepared-statement-per-connection-size = 20
spring.datasource.mysql.filter.stat.log-slow-sql = true
spring.datasource.mysql.filter.stat.slow-sql-millis = 2000
spring.datasource.mysql.filter.stat.enabled = false
spring.datasource.mysql.filter.config.enabled = true
spring.datasource.mysql.filter.wall.enabled = true
spring.datasource.mysql.filter.encoding.enabled = true

#clickhouse
spring.datasource.clickhouse.url = jdbc:clickhouse://ip:8123/xxxdb?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&zeroDateTimeBehavior=convertToNull
spring.datasource.clickhouse.driverClassName = ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.clickhouse.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.clickhouse.username = xxxx
spring.datasource.clickhouse.password = xxxxxx
spring.datasource.clickhouse.initial-size = 10
spring.datasource.clickhouse.max-active = 200
spring.datasource.clickhouse.time-between-eviction-runs-millis = 60000
spring.datasource.clickhouse.min-evictable-idle-time-millis = 300000
spring.datasource.clickhouse.validation-query = SELECT 1
spring.datasource.clickhouse.test-while-idle = true
spring.datasource.clickhouse.test-on-borrow = false
spring.datasource.clickhouse.test-on-return = false
spring.datasource.clickhouse.pool-prepared-statements = true
spring.datasource.clickhouse.max-pool-prepared-statement-per-connection-size = 20
spring.datasource.clickhouse.filter.stat.log-slow-sql = true
spring.datasource.clickhouse.filter.stat.slow-sql-millis = 2000
spring.datasource.clickhouse.filter.stat.enabled = false
spring.datasource.clickhouse.filter.config.enabled = true
spring.datasource.clickhouse.filter.wall.enabled = true
spring.datasource.clickhouse.filter.encoding.enabled = true

#druid 监控页面
spring.datasource.druid.stat-view-servlet.enabled = true
spring.datasource.druid.stat-view-servlet.url-pattern = /druid/*
spring.datasource.druid.stat-view-servlet.login-username = xxxx
spring.datasource.druid.stat-view-servlet.login-password = xxxxxx

3.配置多数据源

配置clickhouse数据源

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "spring.datasource.clickhouse")
public class DataSourceWrapperClickHouse extends DruidDataSource implements InitializingBean {

    private static final long serialVersionUID = -4869699543454182682L;

    @Autowired
    private DataSourceProperties basicProperties;

    @Override
    public void afterPropertiesSet() throws Exception {
        if (StringUtils.isBlank(super.getUsername())) {
            super.setUsername(basicProperties.getDataUsername());
        }
        if (StringUtils.isBlank(super.getPassword())) {
            super.setPassword(basicProperties.getPassword());
        }
        if (StringUtils.isBlank(super.getUrl())) {
            super.setUrl(basicProperties.getUrl());
        }
        if (StringUtils.isBlank(super.getDriverClassName())) {
            super.setDriverClassName(basicProperties.getDriverClassName());
        }
    }
}

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Objects;

@Configuration
@MapperScan(basePackages = {"com.demo.dao.clickhouse"}, sqlSessionFactoryRef = "clickhouseSqlSessionFactory")
public class DataSourceClickHouse {

    @Bean(name = "clickhouseSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("clickhouseDataSource") DataSource masterDataSource) throws Exception {
        //这里使用的是MybatisSqlSessionFactoryBean 而不是SqlSessionFactoryBean 
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(masterDataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/clickhouse/*.xml");
        bean.setMapperLocations(resources);
        Objects.requireNonNull(bean.getObject()).getConfiguration().setMapUnderscoreToCamelCase(false);
        return bean.getObject();
    }
}

配置Mysql数据源 

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public class DataSourceWrapperMysql extends DruidDataSource implements InitializingBean {

    private static final long serialVersionUID = -7976925151028289811L;

    @Autowired
    private DataSourceProperties basicProperties;

    @Override
    public void afterPropertiesSet() throws Exception {
        if (StringUtils.isBlank(super.getUsername())) {
            super.setUsername(basicProperties.getDataUsername());
        }
        if (StringUtils.isBlank(super.getPassword())) {
            super.setPassword(basicProperties.getPassword());
        }
        if (StringUtils.isBlank(super.getUrl())) {
            super.setUrl(basicProperties.getUrl());
        }
        if (StringUtils.isBlank(super.getDriverClassName())) {
            super.setDriverClassName(basicProperties.getDriverClassName());
        }
    }
}

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.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 javax.sql.DataSource;
import java.util.Objects;

@Configuration
@MapperScan(basePackages = "com.demo.dao.member", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class DataSourceMysql {

    @Primary
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqlDataSource") DataSource slaveDataSource) throws Exception {
        //这里使用的是MybatisSqlSessionFactoryBean 而不是SqlSessionFactoryBean
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(slaveDataSource);
        //输出sql日志
//        MybatisConfiguration configuration = new MybatisConfiguration();
//        configuration.setLogImpl(StdOutImpl.class);
//        bean.setConfiguration(configuration);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/member/*.xml");
        bean.setMapperLocations(resources);
        Objects.requireNonNull(bean.getObject()).getConfiguration().setMapUnderscoreToCamelCase(false);
        return bean.getObject();
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sessionFactory) throws Exception {
        return new SqlSessionTemplate(sessionFactory);
    }

    @Bean(name = "mysqlTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("mysqlDataSource") DataSource slaveDataSource) {
        return new DataSourceTransactionManager(slaveDataSource);
    }
}

Druid配置

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * Druid配置
 *
 * @author
 */
@Configuration
@Slf4j
public class DruidConfig {

    @Autowired
    private WallFilter wallFilter;

    public static DruidConfig create() {
        return new DruidConfig();
    }

    @Primary
    @Bean(name = "mysqlDataSource")
    public DataSource masterDataSource() {
        DruidDataSource druidDataSource = DruidConfig.create().buildMemberDataSource();
        // filter
        List<Filter> filters = new ArrayList<>();
        filters.add(wallFilter);
        druidDataSource.setProxyFilters(filters);
        log.info("==>mysqlDataSource,druidDataSource: {}", druidDataSource);
        return druidDataSource;
    }

    @Bean(name = "clickhouseDataSource")
    public DataSource slaveDataSource() {
        DruidDataSource druidDataSource = DruidConfig.create().buildClickHouseDataSource();
        // filter
        List<Filter> filters = new ArrayList<>();
        filters.add(wallFilter);
        druidDataSource.setProxyFilters(filters);
        log.info("==>clickhouseDataSource,druidDataSource: {}", druidDataSource);
        return druidDataSource;
    }

    public DruidDataSource buildMemberDataSource() {
        return new DataSourceWrapperMysql();
    }

    public DruidDataSource buildClickHouseDataSource() {
        return new DataSourceWrapperClickHouse();
    }

    @Bean(name = "wallFilter")
    @DependsOn("wallConfig")
    public WallFilter wallFilter(WallConfig wallConfig) {
        WallFilter wallFilterNew = new WallFilter();
        wallFilterNew.setConfig(wallConfig);
        return wallFilterNew;
    }

    @Bean(name = "wallConfig")
    public WallConfig wallConfig() {
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
        wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句
        wallConfig.setStrictSyntaxCheck(false); //是否进行严格的语法检测, 如果为true,clickhouse 删除会报错
        return wallConfig;
    }
}