SpringBoot + Mybatis 实现多数据源配置

SpringBoot + Mybatis 实现多数据源配置

第一步添加依赖

oracle 的版本不同所使用的依赖版本也不同，根据自身情况选择不同版本

<!--mybatis-->
 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>2.0.1</version>
 </dependency>
 <dependency>
     <groupId>tk.mybatis</groupId>
     <artifactId>mapper</artifactId>
     <version>3.4.2</version>
 </dependency>
 <dependency>
     <groupId>org.mybatis.generator</groupId>
     <artifactId>mybatis-generator-core</artifactId>
     <version>1.3.5</version>
     <scope>test</scope>
 </dependency>
 
 <!--mysql-->
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.48</version>
 </dependency>
 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 
<!--oracle-->
<dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ojdbc6</artifactId>
     <version>11.2.0.3</version>
 </dependency>

第二步在SpringBoot的配置文件 application.yml 或者 application.properties 中配置

这里以MySQL 和Oracle 作为示例

application.properties 配置

# mysql 数据库
spring.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mysql.jdbc-url=jdbc:mysql://ip:port/database?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
spring.datasource.mysql.username=username
spring.datasource.mysql.password=password

# oracle 数据库
spring.datasource.oracle.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.oracle.jdbc-url=jdbc:oracle:thin:@ip:port:SID
spring.datasource.oracle.username=username
spring.datasource.oracle.password=password

spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.hikari.connection-timeout=960000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.timeBetweenEvictionRunsMillis=600000getDiffDetail
spring.datasource.minEvictableIdleTimeMillis=300000

application.yml 配置

spring: 
  datasource:
    initialSize: 5
    minIdle: 5
    maxActive: 20
    timeBetweenEvictionRunsMillis: 600000getDiffDetail
    minEvictableIdleTimeMillis: 300000
    hikari:
      connection-timeout: 960000
      max-lifetime: 600000
    mysql: 
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://ip:port/database?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
      username: username
      password: password
    oracle:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@ip:port:SID
      username: username
      password: password

第三步添加配置类

mysql 配置

package com.report.config;

import com.github.pagehelper.PageHelper;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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 tk.mybatis.spring.mapper.MapperScannerConfigurer;

import javax.sql.DataSource;
import java.util.Properties;

import static com.report.core.ProjectConstant.MAPPER_INTERFACE_REFERENCE;
import static com.report.core.ProjectConstant.MAPPER_PACKAGE;

@Configuration
public class MysqlConnectionConfigurer {
    @Bean(name = "mysqlDataSource")
    @Qualifier("mysqlDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.mysql")        //这里的前缀就是配置文件中配置的
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mySqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);

        //配置分页插件，详情请查阅官方文档
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("pageSizeZero", "true");//分页尺寸为0时查询所有纪录不再执行分页
        properties.setProperty("reasonable", "true");//页码<=0 查询第一页，页码>=总页数查询最后一页
        properties.setProperty("supportMethodsArguments", "true");//支持通过 Mapper 接口参数来传递分页参数
        pageHelper.setProperties(properties);

        //添加插件
        factory.setPlugins(new Interceptor[]{pageHelper});
        return factory.getObject();
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("mySqlSqlSessionFactory");
        //配置mapper所在的包
        mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE); //com.report.mysql.mapper

        //配置通用Mapper，详情请查阅官方文档
        Properties properties = new Properties();
        properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);  //Mapper插件基础接口的完全限定名
        properties.setProperty("notEmpty", "false");//insert、update是否判断字符串类型!='' 即 test="str != null"表达式内是否追加 and str != ''
        properties.setProperty("IDENTITY", "MYSQL");
//        properties.setProperty("IDENTITY", "ORACLE");
//        properties.setProperty("ORDER", "BEFORE");
        mapperScannerConfigurer.setProperties(properties);

        return mapperScannerConfigurer;
    }
}

oracel 配置

package com.report.config;

import com.github.pagehelper.PageHelper;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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 tk.mybatis.spring.mapper.MapperScannerConfigurer;

import javax.sql.DataSource;
import java.util.Properties;

import static com.report.core.ProjectConstant.*;

@Configuration
public class OracleConnectionConfigurer {

    @Bean(name = "oracleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.oracle")   //这里的前缀就是配置文件中配置的
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name="orcSqlSessionFactoryBean")
    public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);

        //配置分页插件，详情请查阅官方文档
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("pageSizeZero", "true");//分页尺寸为0时查询所有纪录不再执行分页
        properties.setProperty("reasonable", "true");//页码<=0 查询第一页，页码>=总页数查询最后一页
        properties.setProperty("supportMethodsArguments", "true");//支持通过 Mapper 接口参数来传递分页参数
        pageHelper.setProperties(properties);

        //添加插件
        factory.setPlugins(new Interceptor[]{pageHelper});

        return factory.getObject();
    }

    @Bean(name = "orcMapperScanner")
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("orcSqlSessionFactoryBean");
        //配置mapper所在的包
        mapperScannerConfigurer.setBasePackage(ORCMAPPER_PACKAGE);  //com.report.oracle.mapper

        //配置通用Mapper，详情请查阅官方文档
        Properties properties = new Properties();
        properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);  //Mapper插件基础接口的完全限定名
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "ORACLE");
        properties.setProperty("ORDER", "BEFORE");
        mapperScannerConfigurer.setProperties(properties);

        return mapperScannerConfigurer;
    }
}