SpringBoot 项目集成多种数据源

SpringBoot 项目集成多种数据源

环境：SpringBoot 2.3.5.RELEASE+ JDK1.8 +MybatisPlus3.4.0

1、引入依赖

<!--数据库连接驱动和连接池依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.4</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>


<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

2、配置数据源

properties

#去除druid配置
spring.autoconfigure.exclude=com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
## 初始化连接池的连接数量 大小，最小，最大
spring.datasource.dynamic.druid.initial-size=10
spring.datasource.dynamic.druid.max-active=30
spring.datasource.dynamic.druid.min-idle=10
# 获取连接时最大等待时间，单位毫秒
spring.datasource.dynamic.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.dynamic.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.dynamic.druid.minEvictableIdleTimeMillis=300000
# 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
spring.datasource.dynamic.druid.pool-prepared-statements=false
spring.datasource.dynamic.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.dynamic.druid.validation-query=SELECT 1
spring.datasource.dynamic.druid.validation-query-timeout=30000
spring.datasource.dynamic.druid.test-on-borrow=false
spring.datasource.dynamic.druid.test-on-return=false
spring.datasource.dynamic.druid.test-while-idle=true
spring.datasource.dynamic.druid.keep-alive=true

#Mysql数据源配置
spring.datasource.dynamic.primary=mysql
spring.datasource.dynamic.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.mysql.url=jdbc:mysql://xx.xx.xx.xx:3306/dynamic
spring.datasource.dynamic.datasource.mysql.username=XXXXXXX
spring.datasource.dynamic.datasource.mysql.password=XXXXXXX
#sqlServer数据源配置
spring.datasource.dynamic.datasource.sqlServer.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.dynamic.datasource.sqlServer.url=jdbc:sqlserver://xx.xx.xx.xx:1433;DatabaseName=dynamic
spring.datasource.dynamic.datasource.sqlServer.username=XXXXXXX
spring.datasource.dynamic.datasource.sqlServer.password=XXXXXXX

#sql防注入配置
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
#设置数据库不可以执行删除、删表操作
spring.datasource.druid.filter.wall.config.delete-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false
#是否启用StatFilter默认值true
spring.datasource.druid.web-stat-filter.enabled=true
##spring.datasource.dynamic.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.filters=stat,wall,log4j
#开启监控
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
#监控的账号密码
spring.datasource.druid.stat-view-servlet.login-username=XXXXXXX
spring.datasource.druid.stat-view-servlet.login-password=XXXXXXX
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

yaml

spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        reset-enable: false
          # 登录名
        login-username: XXXX
          # 登录密码
        login-password: XXXX
    dynamic:
      druid:
          initial-size: 5
          min-idle: 5
          max-active: 20
          max-wait: 60000
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 30000
          validation-query: SELECT 1
          test-while-idle: true
          test-on-borrow: true
          test-on-return: false
      primary: mysql #设置默认的数据源或者数据源组,默认值即为mysql
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        mysql:
          url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic
          username: XXXXXXX
          password: XXXXXXX
          driver-class-name: com.mysql.jdbc.Driver
        sqlServer:
          url: jdbc:sqlserver://xx.xx.xx.xx:1433;DatabaseName=dynamic
          username: XXXXXXX
          password: XXXXXXX
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
       #......省略

3、使用@DS切换数据源

@DS(“XXXX”)所放置的位置：

建议放于Service实现类中，@DS是以粒度最小的为准，当Service实现类和Mapper接口一同被加上@DS注解时，会以Mapper上加的@DS去选择数据源。@DS的起作用位置：Service实现< Service实现方法 <Mapper<Mapper方法。

需避免以最小粒度去设置，因为以最小粒度设置会导致当多个相同数据库进行切换数据源时，Mapper只能使用对应一个，只能建立多个Mapper方法去对应。

添加在Service实现类与实现方法上相结合，更有利于数据源的切换和使用。

@Service
@DS("sqlServer")
public class GradeResultStudentAnswerPaperServiceImpl extends ServiceImpl<GradeResultStudentAnswerPaperMapper, GradeResultStudentAnswerPaper> implements GradeResultStudentAnswerPaperService {

    @Autowired
    private GradeResultStudentAnswerPaperMapper gradeResultStudentAnswerPaperMapper;
    
    @Override
    @DS("mysql")
    public Object test(String id) {
        //以mysql数据源执行
        return gradeResultStudentAnswerPaperMapper.selectById(id);
    }
    
    @Override
    public Object test(String id) {
        //以sqlServer数据源执行
        return gradeResultStudentAnswerPaperMapper.selectById(id);
    }
}