springboot整合oracle,mysql多数据源

背景:boot项目需要使用oracle和mysql等两个关系型数据库的数据
拓展:使用mybatisplus框架的一些坑

思路
从原理上来说,boot加载单数据源无非就是yml中的配置被扫到后,默认实例化了一个sqlsessionfactory装载进容器,那么多数据源无非就是手动配置实例化2个sqlsessionfactory装载到容器中.

依赖与配置
MVN依赖:

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
        <!--<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>-->

注意事项
1.ojbc6的包无法需要手动下载加入本地仓库
2.如果使用了mybatisPlus的包,那么需要就不需要加入mybatis的依赖,防止冲突

首先是yml的配置

spring:
  datasource:
    primary:
      jdbc-url: jdbcurl
      username: username
      password: password
      driver-class-name: oracle.jdbc.driver.OracleDriver
      hikari:
        minimum-idle: 5
        maximum-pool-size: 15
        auto-commit: true
        idle-timeout: 30000
        pool-name: DatebookHikariPR
        max-lifetime: 1800000
        connection-timeout: 10000
        connection-test-query: SELECT * from dual
    secondary:
      jdbc-url: jdbcurl
      username: username
      password: password
      driver-class-name: com.mysql.jdbc.Driver
      hikari:
        minimum-idle: 5
        maximum-pool-size: 15
        auto-commit: true
        idle-timeout: 30000
        pool-name: DatebookHikariSE
        max-lifetime: 1800000
        connection-timeout: 10000
        connection-test-query: SELECT 1

注意事项
默认的配置是url,多数据源必须使用jdbc-url,test查询语句不同,这里使用的是boot2默认的hikari连接池,如果使用Druid需要自行修改配置

接下来是sqlSessionFactory的配置

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;

/**
 * @Copyright: Zhejiang Hangzhou BroB Co., Ltd  2020 <br/>
 * @ClassName OracleDataSourceConfig
 * @Description OracleSqlsessionFactory配置
 * @Author sean
 * @Date 2020/6/3 5:34 下午
 * @Version 1.0
 */
@Configuration
@MapperScan(basePackages = "com.XXX.primary", sqlSessionTemplateRef  = "primarySqlSessionTemplate")
public class OracleDataSourceConfig {
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    @Primary
    public DataSource DataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory SqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
        return sqlSessionFactoryBean.getObject();;
    }
    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager TransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

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;

/**
 * @Copyright: Zhejiang Hangzhou BroB Co., Ltd  2020 <br/>
 * @ClassName OracleDataSourceConfig
 * @Description MysqlSqlSessionFactory配置
 * @Author sean
 * @Date 2020/6/3 5:34 下午
 * @Version 1.0
 */
@Configuration
@MapperScan(basePackages = "com.XXX.secondary", sqlSessionTemplateRef  = "secondarySqlSessionTemplate")
public class MysqlDataSourceConfig {
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource DataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory SqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/secondary/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager TransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name = "secondarySqlSessionTemplate")
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

注意事项
1.有且只有一个@Primary的配置,其余多少数据源都不需要加
2.MapperScan扫的是Mapper interface,resources放的是mapper.xml,需要分开
3.需要特别注意的点,如果使用mybatisPlus,那么这里需要改动如下配置

   @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory SqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        mybatisSqlSessionFactoryBean.setDataSource(dataSource);
        mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
        return mybatisSqlSessionFactoryBean.getObject();
    }

conf.png

mapper interface.png

mapper xml.png

调用
注入对应的Mapper,调用即可自动命中对应数据源

2020-06-10

2020-06-10

springboot整合oracle,mysql多数据源