mybatis/mybatis-plus 配置多数据源

一、修改yml配置

#tomacat相关配置
server:
  port: 9111
spring:
  datasource:
    db-oracle:
      jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
      username: test
      password: test
      driver-class-name: oracle.jdbc.driver.OracleDriver
      type: com.zaxxer.hikari.HikariDataSource
    db-clickhouse:
      jdbc-url: jdbc:clickhouse://127.0.0.1:8123/test
      username: test
      password: test
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      type: com.zaxxer.hikari.HikariDataSource
#mybatis-plus相关配置
mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  #实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.test.model.entity
  global-config:
    db-config:
      #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
      id-type: AUTO
      #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
      field-strategy: NOT_EMPTY
      #驼峰下划线转换
      table-underline: true
      #mp2.3+ 全局表前缀 mp_
      #table-prefix: mp_
      #刷新mapper 调试神器
      #refresh-mapper: true
      #数据库大写下划线转换
      #alipay-mode: true
      # Sequence序列接口实现类配置
      #key-generator: com.baomidou.mybatisplus.incrementer.OracleKeyGenerator
      #逻辑删除配置（下面3个配置）
      logic-delete-value: 1
      logic-not-delete-value: 0
      #sql-injector: com.baomidou.mybatisplus.mapper.LogicSqlInjector
      #自定义填充策略接口实现
    #meta-object-handler: com.baomidou.mybatisplus.core.handlers.MetaObjectHandler
  configuration:
    #配置返回数据库(column下划线命名&&返回java实体是驼峰命名)，自动匹配无需as（没开启这个，SQL需要写as： select user_id as userId）
    map-underscore-to-camel-case: true
    cache-enabled: false
    #配置JdbcTypeForNull, oracle数据库必须配置
    jdbc-type-for-null: 'null'

 

二、项目添加配置

MyMetaObjectHandler 　　

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.sgis.common.handler.BusinessException;
import org.apache.ibatis.reflection.MetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(MyMetaObjectHandler.class);

    @Autowired
    @Qualifier("oracleJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public void insertFill(MetaObject metaObject) {
        LOGGER.info("start insert fill ....");
        this.setFieldValByName("version", 0, metaObject);//version 默认为0
        //this.setInsertFieldValByName("operator", "Jerry", metaObject);//@since 快照：3.0.7.2-SNAPSHOT， @since 正式版暂未发布3.0.7
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        LOGGER.info("start update fill ....");
        boolean status = versionCheck(metaObject);
        if(!status){
            throw new BusinessException(500,"数据版本不一致，数据更新失败");
        }
        //this.setFieldValByName("operator", "Tom", metaObject);
        //this.setUpdateFieldValByName("operator", "Tom", metaObject);//@since 快照：3.0.7.2-SNAPSHOT， @since 正式版暂未发布3.0.7
    }


    /**
     * 如果数据实体存在version则更新数据时需要验证versio是否一致
     * @param metaObject
     * @return
     */
    public boolean versionCheck(MetaObject metaObject){
        //如果不存在version 默认返回true
        boolean isVersionEqual = true;
        String fieldName = "version";
        if(metaObject.hasGetter("param1." + fieldName)){
            //获取更新前数据版本（版本未加1之前）
            String oVersionStr  = metaObject.getValue("param1." + fieldName)+"";
            Object id = metaObject.getValue("param1." + "id");
            /**
             * 获取当前数据库中的版本
             */
            Object tableName = metaObject.getValue("param1." + "sysTableName");
            if(tableName == null){
                return false;
            }
            String sql = "select count(1) from " + tableName + " where id = '" + id + "' and version = '" + oVersionStr + "'" ;
            int count = this.jdbcTemplate.queryForObject(sql,Integer.class);
            System.out.println(count);
            if(count < 1 ){
                return false;
            }
        }
        return isVersionEqual;
    }

}

 

OracleDbConfig 　

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.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
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.test.mapper.oracle",sqlSessionTemplateRef = "oracleDbSqlSessionTemplate")
public class OracleDbConfig {

    @Autowired
    private MyMetaObjectHandler myMetaObjectHandler;


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

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

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

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

        //构造方法，解决多数据源导致mybatis-config配置失效的问题
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());
        // mybatis添加实体类字段为空报错问题
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        sqlSessionFactoryBean.setConfiguration(configuration);


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

        return sqlSessionFactoryBean.getObject();
    }

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

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

}

 

ClickhouseDbConfig 　　　

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.sgis.one.map.micro.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
    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);
        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);
    }
}