目录
环境
框架:RuoYi-Vue v3.8.7
系统:Windows 11
java:1.8.351
maven:3.9.2
至少在RuoYi-Vue v3.8.7中该问题还是存在的,其他环境应该不影响
解决方案
废话不多说,直接上方案
1、引入多数据源框架
确认若依是否引入了 dynamic-datasource-spring-boot-starter,且确保版本在3.5.2和以上(3.8.7中,若依默认引入了该框架(ruoyi-common模块中),具体可使用版本忘了,但若依中的3.5.2是支持的,所以就标个3.5.2吧)
<!-- 动态数据源 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
2、注释掉若依原本多数据源配置类
模块:ruoyi-framework,配置类:com.ruoyi.framework.config.DruidConfig
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.ruoyi.common.enums.DataSourceType;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.framework.config.properties.DruidProperties;
import com.ruoyi.framework.datasource.DynamicDataSource;
/**
* druid 配置多数据源
*
* @author ruoyi
*/
@Configuration
public class DruidConfig
{
/** 注释原有配置 */
// @Bean
// @ConfigurationProperties("spring.datasource.druid.master")
// public DataSource masterDataSource(DruidProperties druidProperties)
// {
// DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
// return druidProperties.dataSource(dataSource);
// }
//
// @Bean
// @ConfigurationProperties("spring.datasource.druid.slave")
// @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
// public DataSource slaveDataSource(DruidProperties druidProperties)
// {
// DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
// return druidProperties.dataSource(dataSource);
// }
//
// @Bean(name = "dynamicDataSource")
// @Primary
// public DynamicDataSource dataSource1(DataSource masterDataSource)
// {
// Map<Object, Object> targetDataSources = new HashMap<>();
// targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
// setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
// return new DynamicDataSource(masterDataSource, targetDataSources);
// }
//
// /**
// * 设置数据源
// *
// * @param targetDataSources 备选数据源集合
// * @param sourceName 数据源名称
// * @param beanName bean名称
// */
// public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
// {
// try
// {
// DataSource dataSource = SpringUtils.getBean(beanName);
// targetDataSources.put(sourceName, dataSource);
// }
// catch (Exception e)
// {
// }
// }
/**
* 去除监控页面底部的广告
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
@Bean
@ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
{
// 获取web监控页面的参数
DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
// 提取common.js的配置路径
String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
final String filePath = "support/http/resources/js/common.js";
// 创建filter进行过滤
Filter filter = new Filter()
{
@Override
public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
{
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException
{
chain.doFilter(request, response);
// 重置缓冲区,响应头不会被重置
response.resetBuffer();
// 获取common.js
String text = Utils.readFromResource(filePath);
// 正则替换banner, 除去底部的广告信息
text = text.replaceAll("<a.*?banner\"></a><br/>", "");
text = text.replaceAll("powered.*?shrek.wang</a>", "");
response.getWriter().write(text);
}
@Override
public void destroy()
{
}
};
FilterRegistrationBean registrationBean = new FilterRegistrationBean();
registrationBean.setFilter(filter);
registrationBean.addUrlPatterns(commonJsPattern);
return registrationBean;
}
}
3、修改配置文件
模块:ruoyi-admin,配置文件:application-druid.yml
# 数据源配置
spring:
# autoconfigure:
# # 排除 Druid 自动配置
# exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
datasource:
# 指定使用 Druid 数据源
type: com.alibaba.druid.pool.DruidDataSource
# driverClassName: com.mysql.cj.jdbc.Driver # 注释掉默认驱动
dynamic:
primary: master # 主数据源
strict: false # 严格模式,是否在启动时检查数据源是否可用
datasource:
master: # 数据源名称,@DS注解中进行使用,支持下划线分组
url: jdbc:mysql://localhost:3306/rouyi-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: 123456
slave_1: # 支持@DS("slave")分组和@DS("slave_1")精准
url: jdbc:sqlserver://localhost:1433;DatabaseName=ruoyi-vue # sqlserver链接,需引入sqlserver驱动
username: sa
password: 123456
slave_2: # 支持@DS("slave")分组和@DS("slave_2")精准
url: jdbc:sqlserver://localhost:1433;DatabaseName=ruoyi-vue # sqlserver链接,需引入sqlserver驱动
username: sa
password: 123456
druid:
# 注释掉若依的多数据源配置
# # 主库数据源
# master:
# url: jdbc:mysql://localhost:3306/rouyi-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
# username: root
# password: 123456
# # 从库数据源
# slave:
# # 从数据源开关/默认关闭
# enabled: true
# url: jdbc:sqlserver://localhost:1433;DatabaseName=ruoyi-bue
# username: sa
# password: 123456
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置连接超时时间
connectTimeout: 30000
# 配置网络超时时间
socketTimeout: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
# validationQuery: SELECT 1 FROM DUAL # DUAL 为 MySQL 语法,对于个别数据源来说不可用
validationQuery: SELECT 1 # 该方式支持 MySQL、SQL Server、其余未知
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
webStatFilter:
enabled: true
statViewServlet:
enabled: true
# 设置白名单,不填则允许所有访问
allow:
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: ruoyi
login-password: 123456
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
4、测试
设t1、t2表,并补充mapper和service
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import org.springframework.stereotype.Service;
@Service
public class T1ServiceImpl implements T1Service {
@Resource
private T1Mapper t1Mapper;
@Resource
private T2Service t2Service;
@Override
@DSTransactional // 多数据源事务管理
public boolean save(T1 t1, T2 t2) {
boolean save = t1Mapper.save(t1);
if (true) {
throw new RuntimeException("主动异常");
}
return save && t2Service.save(t2);
}
}
import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.stereotype.Service;
@DS("slave") // 切换其他数据源
@Service
public class T2ServiceImpl implements T2Service {
@Resource
private T2Mapper t2Mapper;
@Override
public boolean save(T2 t2) {
return t2Mapper.save(t2);
}
}
总结:多数据源下请勿使用原@Transactional注解,该注解会导致多数据源失效;使用@DSTransactional注解后可以使用多数据源,但不能支持事务;将若依配置替换为dynamic框架原有配置后,即可支持事务
实验步骤
1、准备两个数据源和两个表
t1表使用mysql
CREATE TABLE t1 (
`id` bigint primary key,
`name` varchar(255)
);
t2表使用sqlserver
CREATE TABLE t2(
id bigint primary key,
pass varchar(255)
);
2、配置若依多数据源
spring:
datasource:
druid:
# 注释掉若依的多数据源配置
# 主库数据源
master:
url: jdbc:mysql://localhost:3306/rouyi-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: 123456
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: true
url: jdbc:sqlserver://localhost:1433;DatabaseName=ruoyi-bue
username: sa
password: 123456
3、构建ORM
框架:mybatis(若依中默认mybatis,就不额外引入plus了)
实体类
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class T1 {
private Long id;
private String name;
}
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class T2 {
private Long id;
private String pass;
}
Mapper
import org.apache.ibatis.annotations.Insert;
public interface T1Mapper {
@Insert("insert into t1 values(#{id}, #{name})")
boolean save(T1 t1);
}
import org.apache.ibatis.annotations.Insert;
public interface T2Mapper {
@Insert("insert into t2 values (#{id},#{pass})")
boolean save(T2 t2);
}
Service
public interface T1Service {
boolean save(T1 t1);
}
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class T1ServiceImpl implements T1Service {
@Resource
private T1Mapper t1Mapper;
@Override
public boolean save(T1 t1) {
return t1Mapper.save(t1);
}
}
public interface T2Service {
boolean save(T2 t2);
}
import com.ruoyi.common.annotation.DataSource;
import com.ruoyi.common.enums.DataSourceType;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@DataSource(DataSourceType.SLAVE) // 使用若依的注解标明这里使用的是从库
@Service
public class T2ServiceImpl implements T2Service {
@Resource
private T2Mapper t2Mapper;
@Override
public boolean save(T2 t2) {
return t2Mapper.save(t2);
}
}
4、实验
注:
1、运行的sql操作都会正常打印sql日志,需要到数据库查看实际数据,以确定效果
2、@DSTransactional注解在Test方法上面无效,请在Service方法上面使用
新建实验接口
public interface TestService {
/**
* 实验方法
*/
void test();
}
@Transactional测试
若依@DataSrouce注解+@Transactional注解
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class TestServiceImpl implements TestService {
@Resource
private T1Service t1Service;
@Resource
private T2Service t2Service;
/**
* 实验方法
*/
@Override
@Transactional
public void test() {
t1Service.save(new T1(1L, "admin")); // 正常回滚
t2Service.save(new T2(1L, "123456")); // 报错
}
}
结果:保存t2时失败,因为加上@Transactional后t1Service和t2Service使用的是同一个数据源(默认数据源),而t2表在默认数据源中不存在,因此存储失败。而因为@Transactional的缘故,保存t2异常,导致t1回滚。dynamic框架使用@Transactional注解也会有此问题,需要额外处理
@DSTransactional测试
测试一:查看数据源是否正常切换
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import org.springframework.stereotype.Service;
@Service
public class TestServiceImpl implements TestService {
@Resource
private T1Service t1Service;
@Resource
private T2Service t2Service;
/**
* 实验方法
*/
@Override
@DSTransactional
public void test() {
t1Service.save(new T1(1L, "admin")); // 正常存储
t2Service.save(new T2(1L, "123456")); // 正常存储
}
}
结果:使用了@DSTransactional注解后,t1和t2都正常保存了,这就证明数据源正常切换了
测试二:查看事务是否正常管理
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import org.springframework.stereotype.Service;
@Service
public class TestServiceImpl implements TestService {
@Resource
private T1Service t1Service;
@Resource
private T2Service t2Service;
/**
* 实验方法
*/
@Override
@DSTransactional
public void test() {
t1Service.save(new T1(1L, "admin")); // 存储成功,回滚失败
// 抛出异常触发t1的回滚
if (true) {
throw new RuntimeException("主动异常");
}
t2Service.save(new T2(1L, "123456")); // 不会执行
}
}
结果:使用@DSTransactional后,数据源可以正常切换,但每个数据源之间互相独立,互相处在自己的事务中,方法调用完毕后便已提交,此时抛出异常将不会触发回滚
dynamic配置替换若依配置测试
按照上面的解决方案替换掉若依配置
替换T2ServiceImpl中的@DataSrouce注解为@DS注解
import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@DS("slave") // dynamic注解
@Service
public class T2ServiceImpl implements T2Service {
@Resource
private T2Mapper t2Mapper;
@Override
public boolean save(T2 t2) {
return t2Mapper.save(t2);
}
}
事务测试
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import org.springframework.stereotype.Service;
@Service
public class TestServiceImpl implements TestService {
@Resource
private T1Service t1Service;
@Resource
private T2Service t2Service;
/**
* 实验方法
*/
@Override
@DSTransactional
public void test() {
t1Service.save(new T1(1L, "admin")); // 无报错,正常回滚
// 抛出异常触发t1的回滚
if (true) {
throw new RuntimeException("主动异常");
}
t2Service.save(new T2(1L, "123456")); // 不会执行
}
}
import com.baomidou.dynamic.datasource.annotation.DSTransactional;
import org.springframework.stereotype.Service;
@Service
public class TestServiceImpl implements TestService {
@Resource
private T1Service t1Service;
@Resource
private T2Service t2Service;
/**
* 实验方法
*/
@Override
@DSTransactional
public void test() {
t1Service.save(new T1(1L, "admin")); // 无报错,正常回滚
t2Service.save(new T2(1L, "123456")); // 无报错,正常回滚
// 抛出异常触发t1和t2的回滚
if (true) {
throw new RuntimeException("主动异常");
}
}
}
结果:使用dynamic框架的原生配置和注解即可实现多数据源事务控制