# 多数据源

版本要求:3.1.1

对应demo:springboot-multi-datasource (opens new window)

# 引入依赖

<dependency>
    <groupId>io.gitee.durcframework</groupId>
    <artifactId>fastmybatis-spring-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>

配置application.yml

# 激活开发环境配置
spring:
  profiles:
    active: dev
  multi-datasource:
    # 第一个数据源
    stu:
      # 设置主数据源
      main: true
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/stu?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
      username: root
      password: 12345678
      mybatis:
        # 扫描Mapper接口,多个用英文逗号隔开
        base-package: com.gitee.web.stu
        # [可选]指定配置文件
        # config-location: classpath:mybatis/mybatisConfig.xml
        # [可选]指定mapper xml文件
        mapper-locations:
          - classpath:mybatis/stu/mapper/*.xml
        # [可选]打印SQL,默认false
        print-sql: true
    # 第二个数据源
    goods:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/stu2?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
      username: root
      password: 12345678
      mybatis:
        # 扫描Mapper接口,多个用英文逗号隔开
        base-package: com.gitee.web.goods
        # [可选]指定配置文件
        # config-location: classpath:mybatis/mybatisConfig.xml
        # [可选]指定mapper xml文件
        mapper-locations:
          - classpath:mybatis/goods/mapper/*.xml


# 日志级别配置
logging:
  level:
    com.gitee: debug
    # 关闭mybatis自带sql日志
    com.gitee.web.stu.mapper: error
    com.gitee.web.goods.mapper: error

多数据源格式:

spring:
  multi-datasource:
    <数据源别名1>:
    <数据源别名2>:
    <数据源别名N>:

数据源别名最好用全小写英文,没有-,_。如:shopgoods等。

以下是最精简配置:

# 激活开发环境配置
spring:
  profiles:
    active: dev
  multi-datasource:
    # 第一个数据源
    stu:
      # 设置主数据源
      main: true
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/stu?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
      username: root
      password: 12345678
      mybatis:
        # mapper扫描包路径
        base-package: com.gitee.web.stu
        # [可选]指定插件
        plugins:
          # SQL打印插件
          - com.gitee.fastmybatis.core.support.plugin.SqlFormatterPlugin
        # [可选]打印SQL,默认false
        print-sql: true
    # 第二个数据源
    goods:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/stu2?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
      username: root
      password: 12345678
      mybatis:
        base-package: com.gitee.web.goods

properties对应如下:

spring.profiles.active=dev

# 第一个数据源,格式:spring.multi-datasource.<数据库别名>.driver-class-name
spring.multi-datasource.stu.main=true
spring.multi-datasource.stu.driver-class-name=com.mysql.cj.jdbc.Driver
spring.multi-datasource.stu.url=jdbc:mysql://127.0.0.1:3306/stu?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.multi-datasource.stu.username=root
spring.multi-datasource.stu.password=12345678
# mapper扫描包路径
spring.multi-datasource.stu.mybatis.base-package=com.gitee.web.stu
# 指定配置文件
#spring.multi-datasource.stu.mybatis.config-location=classpath:mybatis/mybatisConfig.xml
# 指定mapper xml文件
spring.multi-datasource.stu.mybatis.mapper-locations=classpath:mybatis/stu/mapper/*.xml
# 打印SQL
spring.multi-datasource.stu.mybatis.print-sql=true

# 第二个数据源
spring.multi-datasource.goods.driver-class-name=com.mysql.cj.jdbc.Driver
spring.multi-datasource.goods.url=jdbc:mysql://127.0.0.1:3306/stu2?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.multi-datasource.goods.username=root
spring.multi-datasource.goods.password=12345678
spring.multi-datasource.goods.mybatis.base-package=com.gitee.web.goods


logging.level.com.gitee=debug
logging.level.com.gitee.web.stu.mapper=error
logging.level.com.gitee.web.goods.mapper=error

# 事务处理

# 方式1:注解处理

多数据源下,事务处理需要特别注意。

主数据源事务,无需指定transactionManager属性

    /**
     * 主数据源事务
     * @param tUser
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public int updateUser(TUser tUser) {
        int update = this.update(tUser);
        int i = 1 / 0;
        return update;
    }

非主数据源必须指定transactionManager,格式:transactionManager+数据源别名

// 非主数据源必须指定transactionManager,格式:transactionManager+数据库别名
// 别名即spring.multi-datasource.goods.xx中的goods
@Transactional(transactionManager = "transactionManagergoods", rollbackFor = Exception.class)
public int updateGoods(Goods goods) {
    int update = this.update(goods);
    int i = 1 / 0; // 报错回滚事务
    return update;
}

# 方式2:手动处理

使用MybatisUtil工具类进行事务处理,如下代码所示:

    /**
     * 手动处理事务
     *
     * @param tUser
     * @return
     */
    public int updateUser2(TUser tUser) {
        return MybatisUtil.doTransaction("stu", transactionStatus -> {
            int update = this.update(tUser);
            int i = 1 / 0;
            return update;
        });
    }

fastmybatis还提供一个DbSupport接口,创建一个枚举类可管理数据源别名。

public enum Dbs implements DbSupport {
    STU("stu"),
    GOODS("goods");

    private final String alias;

    Dbs(String alias) {
        this.alias = alias;
    }

    @Override
    public String getDataSourceAlias() {
        return alias;
    }
}

使用方式:

/**
 * 手动处理事务,方式2
 *
 * @param tUser
 * @return
 */
public int updateUser3(TUser tUser) {
    return Dbs.STU.doTransaction(() -> {
        int update = this.update(tUser);
        int i = 1 / 0; // 报错回滚事务
        return update;
    });
}