一般都是使用第三方 mybatis 扩展库dynamic-datasource-spring-boot-starter来切换多数据源.但是使用@DS
切换数据源在有@Transactional
事务注解的时候会有一个坑!
原因: 开启事务的同时,会从数据库连接池获取数据库连接;
如果内层的service使用@DS切换数据源,只是又做了一层拦截,但是并没有改变整个事务的连接;
在这个事务内的所有数据库操作,都是在事务连接建立之后,所以会产生数据源没有切换的问题;
为了使
@D
S起作用,必须替换数据库连接,也就是改变事务的传播机智,产生新的事务,获取新的数据库连接;所以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'
}
使用Maven构建时的配置: pom.xml
文件:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 单元测试 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- Mybatis-Plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
<scope>runtime</scope>
</dependency>
编辑属性文件,例如: 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)
💡提示: 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
相关的表语句
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');
实体类:
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();
}
}
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> {
}
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);
}
}
关键点在:
@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);
}
}
关键点在:
@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);
}
}
运行结果:
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]
至此Spring Boot + Mybatis-Plus已经完成了多数据源的实现。
事物注解@Transactional
要注意的事项:
要在@Transactional
注解里配置上使用哪个transactionManager()
,例如:
@Transactional(transactionManager="slaveTransactionManager")