Springboot mybatis-plus同时连接两个数据库

mybatis-plus同时连接两个数据库

以同时连sqlserver和mysql为例

1. 配置文件

#    driver-class-name: com.mysql.jdbc.Driver
#    url: jdbc:mysql://localhost:3306/cycling?serverTimezone=GMT%2B8
#    username: root
#    password: 123456
  datasource:
    db1:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://localhost:1433;DatabaseName=xfmdata
      username: sa
      password: 123456
    db2:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/packing?serverTimezone=GMT%2B8
      username: root
      password: 123456

**坑1：**连接多个DB的时候url要变成jdbc-url，否则会报错。

2. 写config文件

DB1对应的config文件Db1DataSourceConfig.java

package com.fan.packing.config;

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.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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.fan.packing.mapper.db1",sqlSessionFactoryRef = "SqlSessionFactory1")
//@MapperScan(basePackages = "com.fan.packing.mapper.db1",sqlSessionTemplateRef = "SqlSessionTemplate1")
public class Db1DataSourceConfig {
    @Primary
    @Bean(name = "DataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "SqlSessionFactory1")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("DataSource1") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/fan/packing/mapper/db1/xml/*.xml"));
        //bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:db1/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean(name = "TransactionManager1")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "SqlSessionTemplate1")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

DB2对应的config文件Db2DataSourceConfig.java

package com.fan.packing.config;

import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.toolkit.GlobalConfigUtils;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.fan.packing.handler.MyMetaObjectHandler;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.fan.packing.mapper.db2",sqlSessionFactoryRef = "SqlSessionFactory2")
//@MapperScan(basePackages = "com.fan.packing.mapper.db1",sqlSessionTemplateRef = "SqlSessionTemplate1")
public class Db2DataSourceConfig {
//    @Primary
    @Bean(name = "DataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

//    @Primary
    @Bean(name = "SqlSessionFactory2")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("DataSource2") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //获取mybatis-plus全局配置
        GlobalConfig globalConfig = GlobalConfigUtils.defaults();
        //mybatis-plus全局配置设置元数据对象处理器为自己实现的那个
        globalConfig.setMetaObjectHandler(new MyMetaObjectHandler()); // 手动注入MetaObjectHandler，否则双数据库的时候无法自动填充create_time和update_time
        bean.setGlobalConfig(globalConfig);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/fan/packing/mapper/db2/xml/*.xml"));
        return bean.getObject();
    }

//    @Primary
    @Bean(name = "TransactionManager2")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

//    @Primary
    @Bean(name = "SqlSessionTemplate2")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

**坑2：**加载对应的xml文件的方式，classpath*:com/fan/packing/mapper/db1/xml/*.xml。
**坑3：**这种方式会让mybatis-plus的FieldFill.INSERT和FieldFill.INSERT_UPDATE无效，所以需要在配置类中手动注入MetaObjectHandler。
**坑4：**只能选一个DB最为主DB，添加@Primary注解，否则会写数据失败。