springboot多数据源整合及使用(一个oracle,两个mysql)

　　在开发工作中,会遇到需要使用多个数据源的情况,比如项目一开始只有oracle,后面需要追加两个mysql数据源使用,这时候就需要配置多数据源了.

　　首先,配置文件的编写:版本如下

spring:
  datasource:
    db1: 
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://xxxxxx:3306/cptdata?characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true&useSSL=false&allowMultiQueries=true&serverTimezone=GMT%2B8
      username: xxxxx
      password: xxxx
    db2:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@xxxx:1521:tpsc
      username: xxxx
      password: xxxx
    db3:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://xxxxx:3306/cptetl?characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true&useSSL=false&allowMultiQueries=true&serverTimezone=GMT%2B8
      username: xxxxx
      password:xxxxx
    # hikariCP
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      connection-timeout: 60000
      maximum-pool-size: 20
      minimum-idle: 10
      idle-timeout: 600000
      max-lifetime: 1800000
      pool-name: HikariConnectionPool

然后就是编写各自的配置文件:

mysql1:

@Configuration
@MapperScan(basePackages ="xxxx.xxx.xxx.mysql.mapper",sqlSessionFactoryRef="mysqlSqlSessionFactory")
public class mysqlConfig {

    private static final String mapper_location = "classpath:mybatis/mapper/mysql/**/*.xml";
    private static final String mybatis_config_location = "mybatis/mybatis-config.xml";
    private static final String alias_package = xxx.xxx.xxx.xxx";

    @ConfigurationProperties("spring.datasource.db1")
    @Bean
    @Primary
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource mysqlDataSource() {
        return mysqlDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager() {
        return new DataSourceTransactionManager(mysqlDataSource());
    }

    @Bean
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(mysqlDataSource());
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_location));
        sqlSessionFactory.setConfigLocation(new ClassPathResource(mybatis_config_location));
        sqlSessionFactory.setTypeAliasesPackage(alias_package);

        sqlSessionFactory.setVfs(SpringBootVFS.class);
        return sqlSessionFactory.getObject();
    }
}

mysql2配置:

@Configuration
@MapperScan(basePackages = "xxx.xxx.xxxx.mysql.etlmapper", sqlSessionFactoryRef = "mysqlEtlSqlSessionFactory")
public class CptMysqlEtlConfig {
    private static final String mapper_location = "classpath:mybatis/mapper/mysqletl/**/*.xml";
    private static final String mybatis_config_location = "mybatis/mybatis-config.xml";
    private static final String alias_package = "xxx.xxxx.xxx.xxx";

    @ConfigurationProperties("spring.datasource.db3")
    @Bean
    @Primary
    public DataSourceProperties mysqlEtlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource mysqlEtlDataSource() {
        return mysqlEtlDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager mysqlEtlTransactionManager() {
        return new DataSourceTransactionManager(mysqlEtlDataSource());
    }

    @Bean
    @Primary
    public SqlSessionFactory mysqlEtlSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(mysqlEtlDataSource());
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_location));
        sqlSessionFactory.setConfigLocation(new ClassPathResource(mybatis_config_location));
        sqlSessionFactory.setTypeAliasesPackage(alias_package);
        sqlSessionFactory.setVfs(SpringBootVFS.class);
        return sqlSessionFactory.getObject();
    }
}

oracle配置:

@Configuration
@MapperScan(basePackages ="xxx.xxx.xxx.oracle.mapper",sqlSessionFactoryRef="oracleSqlSessionFactory")
public class CptOracleConfig {

    private static final String mapper_location = "classpath:mybatis/mapper/oracle/**/*.xml";
    private static final String mybatis_config_location = "mybatis/mybatis-config.xml";
    private static final String alias_package = "xxx.xxx.xxx.xxx";

    @ConfigurationProperties("spring.datasource.db2")
    @Bean
    public DataSourceProperties oracleDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public DataSource oracleDataSource() {
        return oracleDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    public DataSourceTransactionManager cptOracleTransactionManager() {
        return new DataSourceTransactionManager(oracleDataSource());
    }


    @Bean
    public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDataSource") DataSource oracleDataSource) {
        return new JdbcTemplate(oracleDataSource);
    }

    @Bean
    public SqlSessionFactory oracleSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(oracleDataSource());
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_location));
        sqlSessionFactory.setConfigLocation(new ClassPathResource(mybatis_config_location));
        sqlSessionFactory.setTypeAliasesPackage(alias_package);

        sqlSessionFactory.setVfs(SpringBootVFS.class);
        return sqlSessionFactory.getObject();
    }
}

mybatis-config.xml如下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="true"/>
        <setting name="lazyLoadingEnabled" value="false"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <setting name="defaultStatementTimeout" value="10"/>
    </settings>
</configuration>