Spring Boot+mybatis create multi data source connection

Keywords: Mybatis Druid Spring SQL

Spring Boot+mybatis create multi data source connection

1, Background: the company system needs to do heterogeneous database data migration, need to configure multiple data sources, there are also some small problems in the configuration process, so make a record here;

2, Code address:

3, Code explanation

1. Project structure:

                              

2. Main configuration file: first configuration file 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  #Initialize size
    minIdle: 1      #Minimum
    maxActive: 300  #Maximum
    maxWait: 60000  #Maximum waiting time in milliseconds
    #timeBetweenEvictionRunsMillis: 300000L #How often is the interval detected? Detect the idle connections that need to be closed (unit: ms)
    minEvictableIdleTimeMillis: 300000 #Minimum lifetime of a connection in the connection pool in milliseconds
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    #Open PSCache and specify the size of PSCache on each connection
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    #The filters intercepted by monitoring statistics cannot be counted by the sql of monitoring interface after removal. wall is used for firewall
    filters: log4j
    #Open the mergeSQL function through the connectionProperties property, slow SQL record
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    #Merge monitoring data from multiple druiddatasources
    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

Then there is the reference 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

Parameters can be modified according to their own systems and businesses

4. Then the configuration class: DruidDataSourceNewConfig.java (new data source configuration class)

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 {

    /**
     * Data source configuration
     * @return
     */
    @Bean("newDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.new")
    public DataSource dataSource() {
        return new DruidDataSource();
    }

    /**
     * transaction management
     * @return
     */
    @Bean("newTransactionManager")
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(this.dataSource());
        return dataSourceTransactionManager;
    }

}

Old data source configuration class: DruidDataSourceOldConfig.java is similar to the new data source configuration

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 configuration class: MybatisNewDataSourceConfig.java the mybatis configuration class corresponding to the new data source

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;

    //Type alias
    private String typeAliasesPackage;
    //Scan Mapper address
    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("------Initialization 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 configuration class of old data source: 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;
    //Type alias
    private String typeAliasesPackage;
    //Scan Mapper address
    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("------Initialization 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. In addition to the above configuration, the annotation on the startup class needs to be modified to @ SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

6. Other configurations are the same as those of ordinary Spring Boot projects, and then different data sources are used by scanning mapper files in different directories.

38 original articles published, 21 praised, 10000 visitors+
Private letter follow

Posted by rookie on Sat, 11 Jan 2020 08:10:12 -0800