java项目如何配置多数据源(基于SpringBoot+Mybatis框架)

前言

近期公司接到一个需求,第三方公司和我们有一款同类型的APP,需要保证两边公司的数据一致性(两边的同一条数据有相同的唯一标识字段)，所以需要他们提供的数据库帐号查询数据进行数据同步，此时则会在项目中涉及到操作多个数据源。

正文

1. maven构建的项目利用pom文件引入数据源需要的jar文件

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.0</version>
        </dependency>
        <!--mysql 连接-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--oracle 连接    如果不需要连接oracle类型的数据库 则可以注释掉如下依赖-->
        <!--oracle 连接    如果需要连接oracle类型的数据库 则在项目根目录(src同级)创建一个lib文件夹放一个oracle连接的jar包-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>1.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/ojdbc6.jar</systemPath>
        </dependency>

ojdbc6.jar快速下载
3. 在springboot的配置文件中配置数据源需要的配置项（application.yml）

datasource-mysql:
  validationQuery: SELECT 1
  driverClassName: com.mysql.jdbc.Driver
  type: com.alibaba.druid.pool.DruidDataSource
  url: jdbc:mysql://ip:port/数据库?useUnicode=true&useSSL=false&characterEncoding=utf8&characterSetResults=utf8
  username: rdis
  password: rdis

datasource-oracle:
  driverClassName: oracle.jdbc.driver.OracleDriver
  type: com.alibaba.druid.pool.DruidDataSource
  url: jdbc:oracle:thin:@ip:port/数据库
  username: GXCX_EK
  password: GXCX_EK
  validationQuery: SELECT 1

dbPool:
  # 初始化大小，最小，最大
  initialSize: 10
  minIdle: 30
  maxActive: 100
  # 配置获取连接等待超时的时间
  maxWait: 60000
  # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  timeBetweenEvictionRunsMillis: 60000
  # 配置一个连接在池中最小生存的时间，单位是毫秒
  minEvictableIdleTimeMillis: 300000
  testWhileIdle: true
  testOnBorrow: false
  exceptionSorter: true
  testOnReturn: false
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: 20

4. 配置类（数据源1 @Primary表示主数据源）

package com.easyfly.main.config;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
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.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;


/**
 * @Description mysql数据源
 * @Author  ouyangli
 * @Param
 * @Return
 * @Date 2019/4/16 0016 13:41
 */

@Configuration
//这里写你的这个数据源需要使用的mapper接口的包路径
@MapperScan(basePackages="com.easyfly.main.dao.mysql", sqlSessionTemplateRef = "mysqlSqlSessionTemplate") //扫描到具体的包
public class MysqlDataSourceConfig {
    private Logger log = LoggerFactory.getLogger(this.getClass());
    //精确到 具体 目录，以便跟其他数据源隔离
    //这里写你的mapper接口对应的mapper.xml的路径,我这里是配置在项目的resources资源文件下
    private static final String MAPPER_LOCATION = "classpath:mapping/*.xml";
    @Value("${datasource-mysql.url}")
    private String dbUrl;
    @Value("${datasource-mysql.type}")
    private String dbtype;
    @Value("${datasource-mysql.username}")
    private String username;
    @Value("${datasource-mysql.password}")
    private String password;
    @Value("${datasource-mysql.driverClassName}")
    private String driverClassName;
    @Value("${datasource-mysql.validationQuery}")
    private String validationQuery;
    //以下的配置从配置文件中读取
    @Value("${dbPool.initialSize}")
    private int initialSize;
    @Value("${dbPool.minIdle}")
    private int minIdle;
    @Value("${dbPool.maxActive}")
    private int maxActive;
    @Value("${dbPool.maxWait}")
    private int maxWait;
    @Value("${dbPool.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${dbPool.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${dbPool.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${dbPool.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${dbPool.testOnReturn}")
    private boolean testOnReturn;
    @Value("${dbPool.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${dbPool.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    /**
     * 设置主数据源的参数
     */
    @Bean
    @Primary
    public DruidDataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setDbType(dbtype);
        datasource.setUsername(this.username);
        datasource.setPassword(this.password);
        datasource.setDriverClassName(this.driverClassName);
        datasource.setInitialSize(this.initialSize);
        datasource.setMinIdle(this.minIdle);
        datasource.setMaxActive(this.maxActive);
        datasource.setMaxWait((long) this.maxWait);
        datasource.setTimeBetweenEvictionRunsMillis((long) this.timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis((long) this.minEvictableIdleTimeMillis);
        datasource.setValidationQuery(this.validationQuery);
        datasource.setTestWhileIdle(this.testWhileIdle);
        datasource.setTestOnBorrow(this.testOnBorrow);
        datasource.setTestOnReturn(this.testOnReturn);
        datasource.setPoolPreparedStatements(this.poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize);
        return datasource;
    }

    /**
     * 设置数据源的事务
     */
    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager() {
        log.info("---------mysqlTransactionManager-------" + "加载完成");
        return new DataSourceTransactionManager(dataSource());
    }

    /**
     * 连接池管道
     */
    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("dataSource") DataSource idmDataSource)
            throws Exception {
        log.info("--------mysqlSqlSessionFactory-------" + "加载完成");
        log.info("driverClassName:{}",this.driverClassName);
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(idmDataSource);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(MysqlDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    /**
     * 数据sql模板
     */
    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate kdysSqlSessionTemplate(
            @Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5. 配置类（数据源2）

package com.easyfly.main.config;

import com.alibaba.druid.pool.DruidDataSource;
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.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * @Description oracle数据源
 * @Author  ouyangli
 * @Param
 * @Return
 * @Date 2019/4/16 0016 13:41
 */
@Configuration
//这里写你的这个数据源需要使用的mapper接口的包路径
@MapperScan(basePackages="com.easyfly.main.dao.oracle", sqlSessionTemplateRef = "oracleSqlSessionTemplate") //扫描到具体的包
public class OracleDataSorceConfig {
    private Logger log = LoggerFactory.getLogger(this.getClass());
    //精确到 具体 目录，以便跟其他数据源隔离
    //这里写你的mapper接口对应的mapper.xml的路径,我这里是配置在项目的resources资源文件下
    private static final String MAPPER_LOCATION = "classpath*:mapping/oracle/*.xml";

    @Value("${datasource-oracle.url}")
    private String dbUrl;
    @Value("${datasource-oracle.type}")
    private String dbtype;
    @Value("${datasource-oracle.username}")
    private String username;
    @Value("${datasource-oracle.password}")
    private String password;
    @Value("${datasource-oracle.driverClassName}")
    private String driverClassName;
    @Value("${datasource-oracle.validationQuery}")
    private String validationQuery;
    //以下的配置从配置文件中读取
    @Value("${dbPool.initialSize}")
    private int initialSize;
    @Value("${dbPool.minIdle}")
    private int minIdle;
    @Value("${dbPool.maxActive}")
    private int maxActive;
    @Value("${dbPool.maxWait}")
    private int maxWait;
    @Value("${dbPool.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${dbPool.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${dbPool.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${dbPool.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${dbPool.testOnReturn}")
    private boolean testOnReturn;
    @Value("${dbPool.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${dbPool.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;


    /**
     * 设置主数据源的参数
     */
    @Bean(name="oracleDataSource")
    public DruidDataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setDbType(this.dbtype);
        datasource.setUsername(this.username);
        datasource.setPassword(this.password);
        datasource.setDriverClassName(this.driverClassName);
        datasource.setInitialSize(this.initialSize);
        datasource.setMinIdle(this.minIdle);
        datasource.setMaxActive(this.maxActive);
        datasource.setMaxWait((long) this.maxWait);
        datasource.setTimeBetweenEvictionRunsMillis((long) this.timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis((long) this.minEvictableIdleTimeMillis);
        datasource.setValidationQuery(this.validationQuery);
        datasource.setTestWhileIdle(this.testWhileIdle);
        datasource.setTestOnBorrow(this.testOnBorrow);
        datasource.setTestOnReturn(this.testOnReturn);
        datasource.setPoolPreparedStatements(this.poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize);
        return datasource;
    }

    /**
     * 设置数据源的事务
     */
    @Bean(name = "oracleTransactionManager")
    public DataSourceTransactionManager idmTransactionManager() {
        log.info("---------oracleTransactionManager-------" + "加载完成");
        return new DataSourceTransactionManager(dataSource());
    }

    /**
     * 连接池管道
     */
    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource)
            throws Exception {
        log.info("--------oracleSqlSessionFactory-------" + "加载完成");
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(oracleDataSource);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(OracleDataSorceConfig.MAPPER_LOCATION));

        log.info("driverClassName:{}",this.driverClassName);
        return sessionFactory.getObject();
    }

    @Bean(name = "oracleSqlSessionTemplate")
    public SqlSessionTemplate kdysSqlSessionTemplate(
            @Qualifier("oracleSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

总结

以上方案可以配置多个不同类型的数据源，只需要在application.yml增加你需要的数据源的配置项，在项目中增加一个配置类即可，需要注意的是，@Primary 代表主数据源，只能在其中一个配置类中使用。

本文地址：https://blog.csdn.net/ouyangli2011/article/details/107384490