基于dynamic-datasource-spring-boot-starter的多数据源实现方案。

1.引入依赖

 <dependency>
    <groupId>com.xy</groupId>
    <artifactId>xy-core-framework-dal-dynamic-monitor</artifactId>
    <version>${xy-core-framework.version}</version>
</dependency>

2.配置数据源

########################### dynamic datasource ############################################
# 全局hikariCP参数，所有值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
#spring.datasource.dynamic.hikari.catalog=
spring.datasource.dynamic.hikari.connection-timeout=30000
spring.datasource.dynamic.hikari.validation-timeout=30000
spring.datasource.dynamic.hikari.idle-timeout=600000
#spring.datasource.dynamic.hikari.leak-detection-threshold=
spring.datasource.dynamic.hikari.max-lifetime=28740000
spring.datasource.dynamic.hikari.max-pool-size=10
spring.datasource.dynamic.hikari.min-idle=5
#spring.datasource.dynamic.hikari.initialization-fail-timeout=
spring.datasource.dynamic.hikari.connection-init-sql=SET NAMES utf8mb4
spring.datasource.dynamic.hikari.connection-test-query=SELECT 1
#spring.datasource.dynamic.hikari.dataSource-class-name=
#spring.datasource.dynamic.hikari.schema=
#spring.datasource.dynamic.hikari.transaction-isolation-name=
spring.datasource.dynamic.hikari.is-auto-commit=true
spring.datasource.dynamic.hikari.is-read-only=false
#spring.datasource.dynamic.hikari.is-isolate-internal-queries=
#spring.datasource.dynamic.hikari.is-register-mbeans=
#spring.datasource.dynamic.hikari.is-allow-pool-suspension=
#spring.datasource.dynamic.hikari.data-source-class-name=
#spring.datasource.dynamic.hikari.data-source-properties.name=
#spring.datasource.dynamic.hikari.health-check-properties.name=
spring.datasource.dynamic.primary=demo
spring.datasource.dynamic.strict=true
spring.datasource.dynamic.strategy=com.baomidou.dynamic.datasource.strategy.LoadBalanceDynamicDataSourceStrategy
##一主多从时，从库的负载均衡策略
#demo-master
spring.datasource.dynamic.datasource.demo.url=jdbc:mysql://localhost:3306/company-demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.demo.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.demo.username=root
spring.datasource.dynamic.datasource.demo.password=1q2w3e4r@
spring.datasource.dynamic.datasource.demo.type=com.zaxxer.hikari.HikariDataSource
#hikari的全局配置项在此处均可以覆盖
spring.datasource.dynamic.datasource.demo.hikari.is-read-only=false
#demo-salve1
spring.datasource.dynamic.datasource.demo-salve_1.url=jdbc:mysql://localhost:3306/company-demo-salve1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.demo-salve_1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.demo-salve_1.username=root
spring.datasource.dynamic.datasource.demo-salve_1.password=1q2w3e4r@
spring.datasource.dynamic.datasource.salve_1.type=com.zaxxer.hikari.HikariDataSource
#hikari的全局配置项在此处均可以覆盖
spring.datasource.dynamic.datasource.demo-salve_1.hikari.is-read-only=false
#demo-salve2
spring.datasource.dynamic.datasource.demo-salve_2.url=jdbc:mysql://localhost:3306/company-demo-salve2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.demo-salve_2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.demo-salve_2.username=root
spring.datasource.dynamic.datasource.demo-salve_2.password=1q2w3e4r@
spring.datasource.dynamic.datasource.demo-salve_2.type=com.zaxxer.hikari.HikariDataSource
#hikari的全局配置项在此处均可以覆盖
spring.datasource.dynamic.datasource.demo-salve_2.hikari.is-read-only=false
#order-master
spring.datasource.dynamic.datasource.order.url=jdbc:mysql://localhost:3306/xy_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.order.username=root
spring.datasource.dynamic.datasource.order.password=1q2w3e4r@
spring.datasource.dynamic.datasource.order.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.dynamic.datasource.order.hikari.is-read-only=false
spring.datasource.dynamic.datasource.order.hikari.max-pool-size=3
spring.datasource.dynamic.datasource.order.hikari.min-idle=8
#order-salve2
spring.datasource.dynamic.datasource.order-slave_1.url=jdbc:mysql://localhost:3306/xy_order_slave1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.order-slave_1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.order-slave_1.username=root
spring.datasource.dynamic.datasource.order-slave_1.password=1q2w3e4r@
spring.datasource.dynamic.datasource.order-slave_1.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.dynamic.datasource.order-slave_1.hikari.is-read-only=false
spring.datasource.dynamic.datasource.order-slave_1.hikari.max-pool-size=50
spring.datasource.dynamic.datasource.order-slave_1.hikari.min-idle=50
#order-salve2
spring.datasource.dynamic.datasource.order-slave_2.url=jdbc:mysql://localhost:3306/xy_order_slave2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.order-slave_2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.order-slave_2.username=root
spring.datasource.dynamic.datasource.order-slave_2.password=1q2w3e4r@
spring.datasource.dynamic.datasource.order-slave_2.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.dynamic.datasource.order-slave_2.hikari.is-read-only=false
spring.datasource.dynamic.datasource.order-slave_2.hikari.max-pool-size=5
spring.datasource.dynamic.datasource.order-slave_2.hikari.min-idle=2
#master
spring.datasource.dynamic.datasource.product.url=jdbc:mysql://localhost:3306/xy_product?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.dynamic.datasource.product.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.product.username=root
spring.datasource.dynamic.datasource.product.password=1q2w3e4r@
spring.datasource.dynamic.datasource.product.type=com.zaxxer.hikari.HikariDataSource
#hikari的全局配置项在此处均可以覆盖
spring.datasource.dynamic.datasource.product.hikari.is-read-only=false

支持数据源分组，分组数据源需要已 “_”区分。例如:order-slave_1,order-slave_2,则分组名为 order-slave

3.数据源使用

推荐在Repository类上添加@DS注解。注解的优先级：方法>类

public class BService {
    @DS("order") //指定具体数据源
    public void dosomething(){
        //dosomething
    }
}

public class CService {   
    @DS("order-slave") //指定数据源分组，框架会更具负载均衡策略进行负载
    public void dosomething(){
        //dosomething
    }
}

4.自定义数据源注解

对于项目中固定的数据源，可以基于DS自定义注解。

import com.baomidou.dynamic.datasource.annotation.DS;
import java.lang.annotation.*;

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS("product")
public @interface Product {
}

5.推荐用法

在多数据源情况下，如果多数据源仅仅是一主一从，且主写从读的情况下，将主命名master，从命名slave，

那么只需开启以下配置，即可实现

多数据源情况下，是否只是一主一从，若是:无需注解即可实现主写从读
mybatis-plus.only-master-salve-mode=false

6.本地事务

使用方法

直接复制作者原文：

在最外层的方法添加 @DSTransactional，底下调用的各个类该切数据源就正常使用DS切换数据源即可。就是这么简单。

//如AService调用BService和CService的方法，A,B,C分别对应不同数据源。
public class AService {

    @DS("a")//如果a是默认数据源则不需要DS注解。
    @DSTransactional
    public void dosomething(){
        BService.dosomething();
        CService.dosomething();
    }
}

public class BService {
    @DS("b")
    public void dosomething(){
        //dosomething
    }
}

public class CService {   
    @DS("c")
    public void dosomething(){
        //dosomething
    }
}

只要@DSTransactional注解下任一环节发生异常，则全局多数据源事务回滚。
如果BC上也有@DSTransactional会有影响吗？答：没有影响的。

注意事项

不支持spring原生事务，不支持spring事务，不支持spring事务，可分别使用，千万不能混用。
再次强调不支持spring事务注解，可理解成独立写了一套事务方案。
只适合简单本地多数据源场景，如果涉及异步和微服务等完整事务场景，请使用seata方案。
暂时不支持更多配置，如只读，如spring的传播特性。
在多数据源情况下，若事物方法内使用的数据源都是同一个数据源的，推荐使用@DS+@Transactional原生事务。

7.Seata分布式事务

一般需要分布式事务的场景大多数都是微服务化，个人并不建议在单体项目引入多数据源+分布式事务，有能力尽早拆开，可为过度方案。 seata 使用步骤

1.工程中引入依赖

<dependency>
    <groupId>io.seata</groupId>
    <artifactId>seata-spring-boot-starter</artifactId>
    <version>xxx</version>
</dependency>

2.添加配置

spring:
  application:
    name: dynamic
  datasource:
    dynamic:
      primary: order
      strict: true
      seata: true    #开启seata代理，开启后默认每个数据源都代理，如果某个不需要代理可单独关闭
      seata-mode: AT #支持XA及AT模式,默认AT
      datasource:
        order:
          username: root
          password: 123456
          url: jdbc:mysql://39.108.158.138:3306/seata_order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
          driver-class-name: com.mysql.cj.jdbc.Driver
          schema: classpath:db/schema-order.sql
        account:
          username: root
          password: 123456
          url: jdbc:mysql://39.108.158.138:3306/seata_account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
          driver-class-name: com.mysql.cj.jdbc.Driver
          schema: classpath:db/schema-account.sql
        product:
          username: root
          password: 123456
          url: jdbc:mysql://39.108.158.138:3306/seata_product?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
          driver-class-name: com.mysql.cj.jdbc.Driver
          schema: classpath:db/schema-product.sql
        test:
          username: sa
          password: ""
          url: jdbc:h2:mem:test
          driver-class-name: org.h2.Driver
          seata: false #这个数据源不需要seata
seata:
  enabled: true
  application-id: applicationName
  tx-service-group: my_test_tx_group
  enable-auto-data-source-proxy: false   #一定要是false
  service:
    vgroup-mapping:
      my_test_tx_group: default  #key与上面的tx-service-group的值对应
    grouplist:
      default: 39.108.158.138:8091 #seata-server地址仅file注册中心需要
  config:
    type: file
  registry:
    type: file

3.业务代码编写

@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class OrderServiceImpl implements OrderService {

  private final OrderDao orderDao;

  private final AccountService accountService;

  private final ProductService productService;

  @DS("order")//每一层都需要使用多数据源注解切换所选择的数据库
  @Override
  @Transactional
  @GlobalTransactional //重点 第一个开启事务的需要添加seata全局事务注解
  public void placeOrder(PlaceOrderRequest request) {
    log.info("=============ORDER START=================");
    Long userId = request.getUserId();
    Long productId = request.getProductId();
    Integer amount = request.getAmount();
    log.info("收到下单请求,用户:{}, 商品:{},数量:{}", userId, productId, amount);

    log.info("当前 XID: {}", RootContext.getXID());

    Order order = Order.builder()
        .userId(userId)
        .productId(productId)
        .status(OrderStatus.INIT)
        .amount(amount)
        .build();

    orderDao.insert(order);
    log.info("订单一阶段生成，等待扣库存付款中");
    // 扣减库存并计算总价
    Double totalPrice = productService.reduceStock(productId, amount);
    // 扣减余额
    accountService.reduceBalance(userId, totalPrice);

    order.setStatus(OrderStatus.SUCCESS);
    order.setTotalPrice(totalPrice);
    orderDao.updateById(order);
    log.info("订单已成功下单");
    log.info("=============ORDER END=================");
  }
}

@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ProductServiceImpl implements ProductService {

  private final ProductDao productDao;

  /**
   * 事务传播特性设置为 REQUIRES_NEW 开启新的事务  重要！！！！一定要使用REQUIRES_NEW
   */
  @DS("product")
  @Transactional(propagation = Propagation.REQUIRES_NEW)
  @Override
  public Double reduceStock(Long productId, Integer amount) {
    log.info("=============PRODUCT START=================");
    log.info("当前 XID: {}", RootContext.getXID());

    // 检查库存
    Product product = productDao.selectById(productId);
    Integer stock = product.getStock();
    log.info("商品编号为 {} 的库存为{},订单商品数量为{}", productId, stock, amount);

    if (stock < amount) {
      log.warn("商品编号为{} 库存不足，当前库存:{}", productId, stock);
      throw new RuntimeException("库存不足");
    }
    log.info("开始扣减商品编号为 {} 库存,单价商品价格为{}", productId, product.getPrice());
    // 扣减库存
    int currentStock = stock - amount;
    product.setStock(currentStock);
    productDao.updateById(product);
    double totalPrice = product.getPrice() * amount;
    log.info("扣减商品编号为 {} 库存成功,扣减后库存为{}, {} 件商品总价为 {} ", productId, currentStock, amount, totalPrice);
    log.info("=============PRODUCT END=================");
    return totalPrice;
  }
}

@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class AccountServiceImpl implements AccountService {

  private final AccountDao accountDao;

  /**
   * 事务传播特性设置为 REQUIRES_NEW 开启新的事务    重要！！！！一定要使用REQUIRES_NEW
   */
  @DS("account")
  @Override
  @Transactional(propagation = Propagation.REQUIRES_NEW)
  public void reduceBalance(Long userId, Double price) {
    log.info("=============ACCOUNT START=================");
    log.info("当前 XID: {}", RootContext.getXID());

    Account account = accountDao.selectById(userId);
    Double balance = account.getBalance();
    log.info("下单用户{}余额为 {},商品总价为{}", userId, balance, price);

    if (balance < price) {
      log.warn("用户 {} 余额不足，当前余额:{}", userId, balance);
      throw new RuntimeException("余额不足");
    }
    log.info("开始扣减用户 {} 余额", userId);
    double currentBalance = account.getBalance() - price;
    account.setBalance(currentBalance);
    accountDao.updateById(account);
    log.info("扣减用户 {} 余额成功,扣减后用户账户余额为{}", userId, currentBalance);
    log.info("=============ACCOUNT END=================");
  }

}

4.注意事项

xy-core-framework-dal-dynamic 组件内部开启seata后会自动使用DataSourceProxy来包装DataSource,所以需要以下方式来保持兼容。

1.如果你引入的是seata-all,请不要使用@EnableAutoDataSourceProxy注解。 2.如果你引入的是seata-spring-boot-starter 请关闭自动代理。

seata:   enable-auto-data-source-proxy: false