SpringBoot & Mybatis-Plus实现多数据源的方法

一般都是使用第三方 mybatis 扩展库dynamic-datasource-spring-boot-starter来切换多数据源.但是使用@DS切换数据源在有@Transactional事务注解的时候会有一个坑!

原因： 开启事务的同时，会从数据库连接池获取数据库连接；

如果内层的service使用@DS切换数据源，只是又做了一层拦截，但是并没有改变整个事务的连接；

在这个事务内的所有数据库操作，都是在事务连接建立之后，所以会产生数据源没有切换的问题；

为了使@DS起作用，必须替换数据库连接，也就是改变事务的传播机智，产生新的事务，获取新的数据库连接；

所以service方法上除了加@Transactional外，还需要设置propagation = Propagation.REQUIRES_NEW参数.

我这里主要介绍使用原生SpringBoot方式让Mybatis-Plus轻松实现多数据源，需要的朋友可以参考下.

依赖项:

使用Gradle构建时的配置: build.gradle文件:

dependencies {
  implementation 'org.springframework.boot:spring-boot-starter'
  implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    
  runtimeOnly 'mysql:mysql-connector-java:5.1.49'
    
  //Mybatis-Plus
  implementation "com.baomidou:mybatis-plus-boot-starter:3.5.2"
  
  //单元测试  
  testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
1
2
3
4
5
6
7
8
9
10
11
12

使用Maven构建时的配置: pom.xml文件:

<dependency>
  <groupId>org.springframework.bootgroupId>
  <artifactId>spring-boot-starterartifactId>
dependency>
<dependency>
  <groupId>org.springframework.bootgroupId>
  <artifactId>spring-boot-starter-jdbcartifactId>
dependency>


<dependency>
  <groupId>org.springframework.bootgroupId>
  <artifactId>spring-boot-starter-testartifactId>
  <scope>testscope>
dependency>


<dependency>
  <groupId>com.baomidougroupId>
  <artifactId>mybatis-plus-boot-starterartifactId>
  <version>3.5.2version>
dependency>
<dependency>
  <groupId>mysqlgroupId>
  <artifactId>mysql-connector-javaartifactId>
  <version>5.1.49version>
  <scope>runtimescope>
dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

编辑属性文件,例如: application.yaml

spring:
  datasource:
    #主数据库
    master:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai"
      username: "root"
      password: "111111"
      minimum-idle: 5
      maximum-pool-size: 10
    #副数据库
    slave:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: "jdbc:mysql://127.0.0.1:3306/mt?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai"
      username: "root"
      password: "111111"
      minimum-idle: 5
      maximum-pool-size: 10

---
#mybatis plus 设置
mybatis-plus:
  configuration:
    # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 返回类型为Map,显示null对应的字段
    call-setters-on-nulls: true
  global-config:
    # 关闭MP3.0自带的banner
    banner: false
    db-config:
      #主键类型  0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: 4
      # 默认数据库表下划线命名
      table-underline: true
      # 逻辑删除
      logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

💡提示: datasource下面的属性配置这块,由于SpringBoot2默认使用Hikari,因此属性要跟Hikari的属性一致才行.例如要用jdbc-url而不能使用url .

属性详情请参考SpringBoot Application properties指南里的spring.datasource.hikari.项

Hikari常用配置:

# Hikari pool https://github.com/brettwooldridge/HikariCP
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
# 连接池中允许的最小连接数。缺省值：10
spring.datasource.hikari.minimum-idle=10
# 连接池中允许的最大连接数。缺省值：10
spring.datasource.hikari.maximum-pool-size=100
# 自动提交
spring.datasource.hikari.auto-commit=true
# 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.hikari.idle-timeout=30000
# 连接池名字
spring.datasource.hikari.pool-name=FlyduckHikariCP
# 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒
spring.datasource.hikari.max-lifetime=1800000
# 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
spring.datasource.hikari.connection-timeout=30000
# 数据库连接测试语句
spring.datasource.hikari.connection-test-query=SELECT 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

相关的表语句

CREATE TABLE `mt_city` (
  `ID` int(11) NOT NULL COMMENT '城市ID',
  `NAME` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '城市名称',
  `PINYIN` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '城市拼音',
  `ACRONYM` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '城市首字母缩写',
  `RANK` char(1) CHARACTER SET utf8 NOT NULL COMMENT '城市等级(按照A-Z来排)',
  `FIRSTCHAR` char(1) CHARACTER SET utf8 NOT NULL COMMENT '城市的第一个首字母',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB

INSERT INTO mt.mt_city (ID,NAME,PINYIN,ACRONYM,`RANK`,FIRSTCHAR) VALUES
	 (1,'北京','beijing','bj','S','B'),
	 (10,'上海','shanghai','sh','S','S'),
	 (20,'广州','guangzhou','gz','A','G'),
	 (30,'深圳','shenzhen','sz','A','S'),
	 (40,'天津','tianjin','tj','A','T'),
	 (42,'西安','xian','xa','A','X'),
	 (44,'福州','fuzhou','fz','B','F'),
	 (45,'重庆','chongqing','cq','A','C'),
	 (50,'杭州','hangzhou','hz','A','H'),
	 (51,'宁波','ningbo','nb','B','N');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

实体类:

package wjw.test.mybatisplus.vo.slave;

import java.io.Serializable;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;

public class MtCity implements Serializable {

  private static final long serialVersionUID = 1L;

  @TableId(
      type = IdType.AUTO)
  /**
   * 城市id
   */
  private Integer id;

  /**
   * 城市名称
   */
  private String name;

  /**
   * 城市拼音
   */
  private String pinyin;

  /**
   * 城市首字母缩写
   */
  private String acronym;

  /**
   * 城市等级(按照a-z来排)
   */
  private String rank;

  /**
   * 城市的第一个首字母
   */
  private String firstchar;

  public MtCity() {
  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getPinyin() {
    return pinyin;
  }

  public void setPinyin(String pinyin) {
    this.pinyin = pinyin;
  }

  public String getAcronym() {
    return acronym;
  }

  public void setAcronym(String acronym) {
    this.acronym = acronym;
  }

  public String getRank() {
    return rank;
  }

  public void setRank(String rank) {
    this.rank = rank;
  }

  public String getFirstchar() {
    return firstchar;
  }

  public void setFirstchar(String firstchar) {
    this.firstchar = firstchar;
  }

  @Override
  public String toString() {
    StringBuilder builder = new StringBuilder();
    builder.append("MtCity [id=");
    builder.append(id);
    builder.append(", name=");
    builder.append(name);
    builder.append(", pinyin=");
    builder.append(pinyin);
    builder.append(", acronym=");
    builder.append(acronym);
    builder.append(", rank=");
    builder.append(rank);
    builder.append(", firstchar=");
    builder.append(firstchar);
    builder.append("]");
    return builder.toString();
  }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

Mapper类:

package wjw.test.mybatisplus.mapper.slave;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.transaction.annotation.Transactional;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import wjw.test.mybatisplus.vo.slave.MtCity;

@Mapper
@Transactional(transactionManager="slaveTransactionManager")
public interface MtCityMapper extends BaseMapper<MtCity> {

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

master数据源

package wjw.test.mybatisplus;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;

//多数据源配置类:master
@Configuration
//扫描加载mapper接口
@MapperScan(basePackages="wjw.test.mybatisplus.mapper.master",sqlSessionFactoryRef="masterSqlSessionFactory")
public class MasterDatasourceConfiguration {
  @Bean(name = "masterDataSource")
  @Primary
  @ConfigurationProperties(prefix = "spring.datasource.master")
  public DataSource primaryDataSource() {
    DataSource ds =  DataSourceBuilder.create().build();
    return ds;
  }
  
  @Bean(name = "masterMybatisPlusProperties")
  @Primary
  @ConfigurationProperties(prefix = "mybatis-plus")
  public MybatisPlusProperties mybatisPlusProperties() {
   return new MybatisPlusProperties(); 
  }
  
  @Bean(name = "masterSqlSessionFactory")
  @Primary
  //具体设置MybatisSqlSessionFactoryBean的细节参见: com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration类
  public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource,@Qualifier("masterMybatisPlusProperties") MybatisPlusProperties mybatisPlusProperties) throws Exception {
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    
    bean.setConfiguration(mybatisPlusProperties.getConfiguration());
    bean.setGlobalConfig(mybatisPlusProperties.getGlobalConfig());
    
    return bean.getObject();
  }
 
  @Bean(name = "masterTransactionManager")
  @Primary
  public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
 
  @Bean(name = "masterSqlSessionTemplate")
  @Primary
  public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }  
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

关键点在: @MapperScan(basePackages="wjw.test.mybatisplus.mapper.master",sqlSessionFactoryRef="masterSqlSessionFactory")

salve数据源:

package wjw.test.mybatisplus;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;

//多数据源配置类:slave
@Configuration
//扫描加载mapper接口
@MapperScan(basePackages="wjw.test.mybatisplus.mapper.slave",sqlSessionFactoryRef="slaveSqlSessionFactory")
public class SlaveDatasourceConfiguration {
  @Bean(name = "slaveDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.slave")
  public DataSource primaryDataSource() {
    DataSource ds =  DataSourceBuilder.create().build();
    return ds;
  }

  @Bean(name = "slaveMybatisPlusProperties")
  @ConfigurationProperties(prefix = "mybatis-plus")
  public MybatisPlusProperties mybatisPlusProperties() {
   return new MybatisPlusProperties(); 
  }
  
  @Bean(name = "slaveSqlSessionFactory")
  //具体设置MybatisSqlSessionFactoryBean的细节参见: com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration类
  public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource,@Qualifier("slaveMybatisPlusProperties") MybatisPlusProperties mybatisPlusProperties) throws Exception {
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);

    bean.setConfiguration(mybatisPlusProperties.getConfiguration());
    bean.setGlobalConfig(mybatisPlusProperties.getGlobalConfig());
    
    return bean.getObject();
  }
 
  @Bean(name = "slaveTransactionManager")
  public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
 
  @Bean(name = "slaveSqlSessionTemplate")
  public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }  
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

关键点在: @MapperScan(basePackages="wjw.test.mybatisplus.mapper.slave",sqlSessionFactoryRef="slaveSqlSessionFactory")

测试类:

package wjw.test.mybatisplus;

import java.util.List;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.ActiveProfiles;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import wjw.test.mybatisplus.mapper.slave.MtCityMapper;
import wjw.test.mybatisplus.vo.slave.MtCity;

@SpringBootTest(webEnvironment = WebEnvironment.NONE)
@ActiveProfiles("dev") //标明激活哪个profile
public class TestsMybatisPlus {
  @Autowired
  MtCityMapper mtCityMapper;
  
  public TestsMybatisPlus() {
    //
  }

  @BeforeEach
  public void setup() {
    //
  }

  @AfterEach
  public void stop() {
  }

  @Test
  public void geMtCity(){
      QueryWrapper<MtCity> wrapper=new QueryWrapper();
      wrapper.eq("id",1);
      MtCity mtCity = mtCityMapper.selectOne(wrapper);
      System.out.println(mtCity);
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

运行结果:

2022-09-05 19:23:07.022 [main] DEBUG w.t.m.mapper.slave.MtCityMapper.selectList - [debug,137] - ==>  Preparing: SELECT id,name,pinyin,acronym,rank,firstchar FROM mt_city WHERE (id = ?)
2022-09-05 19:23:07.040 [main] DEBUG w.t.m.mapper.slave.MtCityMapper.selectList - [debug,137] - ==> Parameters: 1(Integer)
2022-09-05 19:23:07.055 [main] TRACE w.t.m.mapper.slave.MtCityMapper.selectList - [trace,143] - <==    Columns: id, name, pinyin, acronym, rank, firstchar
2022-09-05 19:23:07.055 [main] TRACE w.t.m.mapper.slave.MtCityMapper.selectList - [trace,143] - <==        Row: 1, 北京, beijing, bj, S, B
2022-09-05 19:23:07.058 [main] DEBUG w.t.m.mapper.slave.MtCityMapper.selectList - [debug,137] - <==      Total: 1
MtCity [id=1, name=北京, pinyin=beijing, acronym=bj, rank=S, firstchar=B]
1
2
3
4
5
6

至此Spring Boot + Mybatis-Plus已经完成了多数据源的实现。

事物注解@Transactional要注意的事项:

要在@Transactional注解里配置上使用哪个transactionManager(),例如:

@Transactional(transactionManager="slaveTransactionManager")
1