Spring Boot+mybatis 建立多資料源連接配接

一、背景：公司系統需要做異構資料庫資料遷移，需要配置多資料源，配置過程中也出現過些小問題，是以在此做下記錄；

二、代碼位址：https://github.com/tiedungao/multipleDataSource.git

三、代碼講解

1、項目結構：

2、主要配置檔案：首先配置檔案 application.yml

server:
  port: 8086
  servlet:
    context-path: /
#http
http:
  encoding:
    charset: UTF-8
jackson:
  date-format: yyyy-MM-dd HH:mm:ss
  time-zone: GMT+8
  default-property-inclusion: non_null

spring:
  profiles:
    active: local
  http:
    encoding:
      charset: UTF-8
  datasource:
    #druid
    initialSize: 1  #初始化大小
    minIdle: 1      #最小
    maxActive: 300  #最大
    maxWait: 60000  #最大等待時長，機關（毫秒）
    #timeBetweenEvictionRunsMillis: 300000L #間隔多久進行一次檢測，檢測需要關閉的空閑連接配接（機關：毫秒）
    minEvictableIdleTimeMillis: 300000 #一個連接配接在連接配接池中的最小生存時間（機關：毫秒）
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    #打開PSCache,并且指定每個連接配接上的PSCache的大小
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    #監控統計攔截的filters，去掉後監控界面sql無法統計，wall用于防火牆
    filters: log4j
    #通過connectionProperties屬性打開mergeSQL功能，慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    #合并多個DruidDataSource的監控資料
    useGlobalDataSourceStat: true

mybatis:
  old:
    mapper-locations: classpath:/mapper/old/*.xml
    type-aliases-package: com.shallbuy.transfer.entity
    configuration:
      cache-enabled: true
      lazy-loading-enabled: true
      use-column-label: true
      use-generated-keys: true
      default-statement-timeout: 25000
      log-prefix: mybatis
  new:
    mapper-locations: classpath:/mapper/newer/*.xml
    type-aliases-package: com.shallbuy.transfer.entity
    configuration:
      cache-enabled: true
      lazy-loading-enabled: true
      use-column-label: true
      use-generated-keys: true
      default-statement-timeout: 25000
      log-prefix: mybatis
           

然後是引用的application-local.yml

spring:
  datasource:
    druid:
      old:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: *************************
        username: ****************
        password: ****************** 
        initial-size: 8
        min-idle: 3
        max-active: 300
        max-wait: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-open-prepared-statements: -1
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        use-global-data-source-stat: false
      new:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: *****************
        username: ************
        password: **************
        initial-size: 8
        min-idle: 5
        max-active: 300
        max-wait: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-open-prepared-statements: -1
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        use-global-data-source-stat: false
           

參數可以根據自己的系統和業務進行修改

4、然後是配置類：DruidDataSourceNewConfig.java（新資料源配置類）

package com.shallbuy.transfer.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**
 * @author gtd
 * description:
 * date: Create in 9:31 2019/4/13
 */
@Configuration
@EnableTransactionManagement
public class DruidDataSourceNewConfig {

    /**
     * 資料源配置
     * @return
     */
    @Bean("newDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.new")
    public DataSource dataSource() {
        return new DruidDataSource();
    }

    /**
     * 事務管理
     * @return
     */
    @Bean("newTransactionManager")
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(this.dataSource());
        return dataSourceTransactionManager;
    }

}
           

老資料源配置類：DruidDataSourceOldConfig.java 和新資料源配置類似

package com.shallbuy.transfer.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @author gtd
 * description:
 * date: Create in 9:31 2019/4/13
 */
@Configuration
@EnableTransactionManagement
public class DruidDataSourceOldConfig {

    @Bean("oldDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.old")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

    @Bean("oldTransactionManager")
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(this.dataSource());
        return dataSourceTransactionManager;
    }

}
           

mybatis配置類：MybatisNewDataSourceConfig.java 新資料源對應的mybatis配置類

package com.shallbuy.transfer.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.IOException;

/**
 * @author gtd
 * description:
 * date: Create in 21:36 2019/4/15
 */
@Configuration
@ConfigurationProperties(prefix = "mybatis.new")
@MapperScan(basePackages = {"com.shallbuy.transfer.dao.newer"}, sqlSessionFactoryRef = "newSqlSessionFactory")
public class MybatisNewDataSourceConfig {
    private static final Logger logger = LoggerFactory.getLogger(MybatisNewDataSourceConfig.class);

    @Resource
    @Qualifier("newDataSource")
    private DataSource newDataSource;

    //類型别名
    private String typeAliasesPackage;
    //掃描Mapper位址
    private String mapperLocations;

    public String getTypeAliasesPackage() {
        return typeAliasesPackage;
    }

    public void setTypeAliasesPackage(String typeAliasesPackage) {
        this.typeAliasesPackage = typeAliasesPackage;
    }

    public String getMapperLocations() {
        return mapperLocations;
    }

    public void setMapperLocations(String mapperLocations) {
        this.mapperLocations = mapperLocations;
    }

    @Bean(name="newSqlSessionFactory")
    public SqlSessionFactory newSqlSessionFactoryBean(){
        logger.info("------初始化newSqlSessionFactory------");
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        SqlSessionFactory sqlSessionFactory = null;
        bean.setDataSource(newDataSource);
        try {
            org.springframework.core.io.Resource[] resources = new PathMatchingResourcePatternResolver()
                    .getResources(mapperLocations);
            bean.setMapperLocations(resources);
            bean.setTypeAliasesPackage(typeAliasesPackage);
            sqlSessionFactory = bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlSessionFactory;
    }

}
           

老資料源的mybatis配置類：MybatisOldDataSourceConfig.java

package com.shallbuy.transfer.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.support.PathMatchingResourcePatternResolver;

import javax.annotation.Resource;
import javax.sql.DataSource;

/**
 * @author gtd
 * description:
 * date: Create in 21:36 2019/4/15
 */
@Configuration
@ConfigurationProperties(prefix = "mybatis.old")
@MapperScan(basePackages = {"com.shallbuy.transfer.dao.old"}, sqlSessionFactoryRef = "oldSqlSessionFactory")
public class MybatisOldDataSourceConfig {
    private static final Logger logger = LoggerFactory.getLogger(MybatisOldDataSourceConfig.class);

    @Resource
    @Qualifier("oldDataSource")
    private DataSource oldDataSource;
    //類型别名
    private String typeAliasesPackage;
    //掃描Mapper位址
    private String mapperLocations;

    public String getTypeAliasesPackage() {
        return typeAliasesPackage;
    }

    public void setTypeAliasesPackage(String typeAliasesPackage) {
        this.typeAliasesPackage = typeAliasesPackage;
    }

    public String getMapperLocations() {
        return mapperLocations;
    }

    public void setMapperLocations(String mapperLocations) {
        this.mapperLocations = mapperLocations;
    }

    @Bean(name="oldSqlSessionFactory")
    public SqlSessionFactory oldSqlSessionFactoryBean(){
        logger.info("------初始化oldSqlSessionFactory------");
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        SqlSessionFactory sqlSessionFactory = null;
        bean.setDataSource(oldDataSource);
        try {
            org.springframework.core.io.Resource[] resources = new PathMatchingResourcePatternResolver()
                    .getResources(mapperLocations);
            bean.setMapperLocations(resources);
            bean.setTypeAliasesPackage(typeAliasesPackage);
            sqlSessionFactory = bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlSessionFactory;
    }

}
           

5、除了上面配置，在啟動類上注解上需要修改為@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

6、其他配置和普通Spring Boot項目相同，然後通過掃描不同目錄下的mapper檔案，使用不同的資料源。