Springboot2实现多数据源

原创已于 2022-10-29 15:43:28 修改 · 1.9k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Springboot

于 2020-09-23 14:51:12 首次发布

本文介绍如何在Spring Boot项目中配置多个数据源，并通过示例代码展示如何使用Druid连接池、MyBatis及Spring事务管理来实现跨数据库的操作。

http://jacksonlhj.cn/articles/2022/03/02/1646232095530.html

序言

由于公司有个需求需要从多个数据库源获取数据然后整合处理，所以就记录下来，这里如何你的方法需要用到事务的话，其实是属于分布式事务，普通的Spring声明式事务已经不能实现，一个项目操作多个数据源的情况下，如果对事务进行管理可以使用jta + atomikos。

废话不多说，我们直接上代码

项目准备
springboot2、mybatis、sql server、druid。

一、pom.xml引入需要的依赖

        <!--sql server-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <!-- 数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--SpringBoot MyBatis启动器-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.2</version>
        </dependency>

二、配置application.yml 多数据源

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    # 配置Druid的其他参数，以下配置必须增加一个配置文件才能有效
    # 初始化大小，最小，最大
    druid:
      # 主库数据源
      master:
        url: jdbc:sqlserver://你的ip:端口;DatabaseName=test;
        username: test
        password: test
      #从数据库
      slave:
        url: jdbc:sqlserver://你的ip:端口;DatabaseName=test2;
        username: test
        password: test
      # 连接池配置
      initial-size: 5
      max-active: 20
      min-idle: 1
      max-wait: 60000
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      #validation-query: SELECT 1 FROM DUAL
      #validation-query-timeout: 5000
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 30000
      max-evictable-idle-time-millis: 60000
      removeAbandoned: true
      removeAbandonedTimeout: 1800
      connectionProperties: druid.stat.mergeSql=false;druid.stat.slowSqlMillis=5000
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat #配置多个英文逗号分隔(统计，sql注入，log4j过滤)//加上wall不支持批量更新,log4j

三、添加主数据源配置

注意：在配置中是主数据库,则需要配置注解@Primary。

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.test.mapper"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourcesConfig {
    //主数据源mapper xml文件夹的路径
    private static final String MAPPER_LOCAL = "classpath:/mapper/*.xml";

    @ConfigurationProperties("spring.datasource.druid.master")
    @Primary
    @Bean(name = "masterDataSource")
    public DruidDataSource druidDataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }
}

四、添加从数据源配置

如需配置多个数据源则把对应的mapper路径改为对应的数据源mapper路径即可。

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = {"com.slave"}, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourcesConfig {

    private static final String MAPPER_LOCAL = "classpath:/slave/*.xml";

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties("spring.datasource.druid.slave")
    public DruidDataSource druidDataSource() {
        return new DruidDataSource();
    }

    //其他数据源的事务管理器
    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DruidDataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }
}