springboot 配置多数据源 只有主数据源能查询数据 从数据源查询数据报错

数据库： postgres+ oracle +clickhouse

postgres  作为主数据源

yml 配置 

# 配置多数据源
spring: 
  datasource:
    db-oracle:
      jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
      username: mima
      password: mima
      driver-class-name: oracle.jdbc.OracleDriver
      type: com.zaxxer.hikari.HikariDataSource
    db-postgresql:
      jdbc-url: jdbc:postgresql://127.0.0.1:54321/test
      username: mima
      password: mima
      driver-class-name: org.postgresql.Driver
      type: com.zaxxer.hikari.HikariDataSource
    db-clickhouse:
      jdbc-url: jdbc:clickhouse://127.0.0.1:8123/test
      username: mima
      password: mima
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      type: com.zaxxer.hikari.HikariDataSource

oracle配置

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.cloud.context.config.annotation.RefreshScope;
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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.***.mapper.oracle",sqlSessionTemplateRef = "oracleDbSqlSessionTemplate")
public class OracleDbConfig {

    @Autowired
    private MyMetaObjectHandler myMetaObjectHandler;

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

    @Bean("oracleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db-oracle")
    @RefreshScope
    public DataSource oracleDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "oracleJdbcTemplate")
    public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public SqlSessionFactory oracleDbSqlSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception{
        // 解决多数据源baseMapper 用问题  sqlSessionMapper==>MybatisSqlSessionMapper
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/oracle/*.xml");
        sqlSessionFactoryBean.setMapperLocations(resources);

        //构造方法，解决动态数据源循环依赖问题。
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());
        sqlSessionFactoryBean.setConfiguration(configuration);


        //多数据源 myMetaObjectHandler不起作用问题解决
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(myMetaObjectHandler);
        globalConfig.setDbConfig(new GlobalConfig.DbConfig());
        sqlSessionFactoryBean.setGlobalConfig(globalConfig);

        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public DataSourceTransactionManager oracleDbTranscationManager(@Qualifier("oracleDataSource") DataSource dataSource)throws Exception{
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate oracleDbSqlSessionTemplate(@Qualifier("oracleDbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

postgres 配置

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.cloud.context.config.annotation.RefreshScope;
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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.***.mapper.postgres",
        sqlSessionTemplateRef = "postgresDbSqlSessionTemplate")
public class PostgresDbConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

    @Bean("postgresDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db-postgresql")
    @RefreshScope
    @Primary  -- 此注解代表主数据源
    public DataSource postgresDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "postgresJdbcTemplate")
    @Primary
    public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionFactory postgresDbSqlSessionFactory(@Qualifier("postgresDataSource") DataSource dataSource)
            throws Exception{
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/postgres/*.xml");
        sqlSessionFactoryBean.setMapperLocations(resources);

        //构造方法，解决动态数据源循环依赖问题。
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager postgresDbTransactionManager(@Qualifier("postgresDataSource")
                                                                                 DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate postgresDbSqlSessionTemplate(@Qualifier("postgresDbSqlSessionFactory")
                                                                   SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

clickhouse 配置

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.cloud.context.config.annotation.RefreshScope;
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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.***.mapper.clickhouse",
        sqlSessionTemplateRef = "clickhouseDbSqlSessionTemplate")
public class ClickhouseDbConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db-clickhouse")
    @RefreshScope
    public DataSource clickhouseDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "clickhouseJdbcTemplate")
    public JdbcTemplate clickhouseJdbcTemplate(@Qualifier("clickhouseDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "clickhouseDbSqlSessionFactory")
    public SqlSessionFactory clickhouseDbSqlSessionFactory(@Qualifier("clickhouseDataSource") DataSource dataSource)
            throws Exception{
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/clickhouse/*.xml");
        sqlSessionFactoryBean.setMapperLocations(resources);

        //构造方法，解决动态数据源循环依赖问题。
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setCallSettersOnNulls(true);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public DataSourceTransactionManager clickhouseDbTransactionManager(@Qualifier("clickhouseDataSource")
                                                                                 DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate clickhouseDbSqlSessionTemplate(@Qualifier("clickhouseDbSqlSessionFactory")
                                                                   SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

mapper区分

 

 

多数据源查询报错

1：Relation "xxx" 表 not exist

2：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.study.server.mapper.UserMapper.insert
    at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:227)
    at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:49)
    at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:65)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
    at com.sun.proxy.$Proxy88.insert(Unknown Source)

主数据源对应的mapper查询不会报错，但是从数据源查询报错，一直感觉是config文件配置的@MapperScan包没有扫描到，导致一直用主数据源查询数据。

　原因：由于此处没有把单数据源里面MybatisConfig.java文件上面的全局扫描注解@MapperScan("com.demo.*.mapper")给删掉，导致操作从数据源的表时，

一直报错，表不存在：Relation "xxx" not exist