springBoot + mybatisPlus 多数据源 实现 saas系统
最近在做的his系统是saas模式,使用的医院id作为所有数据的区分条件,这个应该是目前比较好的实现办法了,毕竟好维护,开发也比较方便。
之前在开放的时候突然想到用多数据源实现saas模式试一下。
文章底部有整个demo的代码
saas主库
多数据源实现saas的大概思路是,在一个主数据库里维护一下saas的信息,比如数据库,数据库地址,账号密码什么的
create table saas_main (
id bigint primary key comment '雪花id',
name varchar(16) comment '租户名称',
type int comment '数据库类型',
driver_class_name varchar(256) comment '驱动',
jdbc_url varchar(1024) comment '数据库链接地址',
username varchar(256) comment '数据库账户',
password varchar(256) comment '数据库密码',
data_base varchar(256) comment '数据库'
);
具体业务逻辑数据库
使用多数据源实现不同的数据库对应不同的用户
-- 数据库1
create database saas_1;
use saas_1;
CREATE TABLE `user`
(
`id` int(11) primary key auto_increment,
`name` varchar(255) comment '姓名'
)
-- 数据库2
create database saas_2;
use saas_2;
CREATE TABLE `user`
(
`id` int(11) primary key auto_increment,
`name` varchar(255) comment '姓名'
)
-- 数据库3
create database saas_3;
use saas_3;
CREATE TABLE `user`
(
`id` int(11) primary key auto_increment,
`name` varchar(255) comment '姓名'
)
pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>demo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.7.0</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.dromara.stream-query</groupId>
<artifactId>stream-plugin-mybatis-plus</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.4</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
</dependencies>
</project>
yaml配置文件
这里只设置了saas_main 主saas库的地址,注意这里是 **jdbc-url ** 不是 url
参考:为什么是jdbc-url : springboot多数据源配置和使用-腾讯云开发者社区-腾讯云 (tencent.com)
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/saas_main?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&verifyServerCertificate=false&useSSL=false
username: root
password: 123456
ThreadLocal 多数据源切换类
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;
/**
* 动态数据源管理
*/
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>() {
/**
* 设置默认数据源的key
*/
@Override
protected String initialValue() {
return "default";
}
};
/**
* 数据源key的集合
*/
public static Set<Object> dataSourceKeys = new HashSet<>();
/**
* 切换数据源
*/
public static void setDataSourceKey(String key) {
contextHolder.set(key);
}
/**
* 获取数据源
*/
public static String getDataSourceKey() {
return contextHolder.get();
}
/**
* 重置数据源
*/
public static void clearDataSourceKey() {
contextHolder.remove();
}
/**
* 判断数据源是否存在
*
* @param key 数据源key
*/
public static boolean containDataSourceKey(String key) {
return dataSourceKeys.contains(key);
}
/**
* 添加数据源key
*/
public static boolean addDataSourceKey(Object key) {
return dataSourceKeys.add(key);
}
/**
* 添加多个数据源keys
*/
public static boolean addDataSourceKeys(Collection<? extends Object> keys) {
return dataSourceKeys.addAll(keys);
}
}
重写 AbstractRoutingDataSource
spring在准备执行sql时都会通过这个抽象的实现类来获取数据源
如果在多线程的情况下去切换数据源可能会出现异常,可以使用ConcurrentHashMap进行替换
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 自定义动态数据源
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private Map<Object, Object> dataSources = new HashMap<>();
/**
* 获取当前数据源的键、
* 每次准备执行sql都会调用此方法获取连接的key
*/
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceKey();
}
/**
* 获取当前数据源
* 每次准备执行sql都会调用此方法获取连接
*/
@Override
protected DataSource determineTargetDataSource() {
return super.determineTargetDataSource();
}
/**
* 设置默认数据源
*
* @param defaultDataSource
*/
public void setDefaultDataSource(Object defaultDataSource) {
super.setDefaultTargetDataSource(defaultDataSource);
}
/**
* 设置数据源
*
* @param dataSources
*/
public void setDataSources(Map<Object, Object> dataSources) {
this.dataSources = dataSources;
super.setTargetDataSources(dataSources);
// 保存数据源的key
DataSourceContextHolder.addDataSourceKeys(dataSources.keySet());
}
/**
* 追加数据源
*
* @param key
* @param dataSource
*/
public void addDataSource(String key, DataSource dataSource) {
dataSources.put(key, dataSource);
super.setTargetDataSources(dataSources);
// 保存数据源的key
DataSourceContextHolder.addDataSourceKey(key);
// 加载新的数据源
//但是多线程切换下可能会出现异常,可以把内部的 hashmap换成 ConcurrentHashMap
super.afterPropertiesSet();
}
}
DataSourceConfig 配置类,加载自定义的bean
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
/**
* 自定义动态数据源配置类
*/
@Configuration
public class DataSourceConfig {
/**
* 默认基础数据源
*
* @return
*/
@Bean("defaultSource")
@ConfigurationProperties("spring.datasource")
@Primary
public DataSource defaultSource() {
return DataSourceBuilder.create().build();
}
/**
* 自定义动态数据源
*
* @return
*/
@Bean("dynamicDataSource")
public DynamicDataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put("default", defaultSource());
// 默认数据源
dynamicDataSource.setDefaultDataSource(defaultSource());
// 动态数据源
dynamicDataSource.setDataSources(dataSourceMap);
return dynamicDataSource;
}
/**
* 修改MybatisPlus数据源配置
*
*/
@Bean
public MybatisSqlSessionFactoryBean sqlSessionFactoryBean() throws IOException {
MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
// 配置自定义动态数据源
sessionFactory.setDataSource(dynamicDataSource());
// 开启驼峰转下划线设置
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
sessionFactory.setConfiguration(configuration);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
return sessionFactory;
}
//如果是mybatis,替换一下session工厂就可以了
/* @Bean
public SqlSessionFactoryBean sqlSessionFactoryBean() throws IOException {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
// 配置自定义动态数据源
sessionFactory.setDataSource(dynamicDataSource());
// 开启驼峰转下划线设置
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
sessionFactory.setConfiguration(configuration);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
return sessionFactory;
}*/
/**
* 开启动态数据源@Transactional注解事务管理的支持
* @return
*/
@Bean
public PlatformTransactionManager transactionManager(
@Autowired DataSource source
) {
return new DataSourceTransactionManager(source);
}
}
具体业务使用
主要配置就是上面三个用来切换数据源的,剩下的就是业务逻辑
user 模拟saas业务
import com.app.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @author supermarketss@163.com
* @date 2024-01-19 - 14:50
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
user 实体类
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* @author supermarketss@163.com
* @date 2024-01-19 - 14:49
*/
@TableName
@Data
public class User {
@TableId
private Integer id;
private String name;
}
SaasMainMapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @author supermarketss@163.com
* @date 2024-01-19 - 11:46
*/
@Mapper
public interface SaasMainMapper extends BaseMapper<SaasMain> {
}
saasMain
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.io.Serializable;
/**
* 租户类
*/
@Data
@TableName
public class SaasMain implements Serializable {
@TableId
private Long id;
/**
* 租户名
*/
private String name;
/**
* 数据库类型
*/
private Integer type;
/**
* 驱动类
*/
private String driverClassName;
/**
* 数据库连接
*/
private String jdbcUrl;
/**
* 数据库用户名
*/
private String username;
/**
* 数据库密码
*/
private String password;
/**
* 数据库名
*/
private String dataBase;
}
SaasMainService
import cn.hutool.core.util.RandomUtil;
import cn.hutool.extra.spring.SpringUtil;
import com.app.config.DataSourceContextHolder;
import com.app.config.DynamicDataSource;
import com.app.entity.SaasMain;
import com.app.entity.User;
import com.app.mapper.SaasMainMapper;
import com.app.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.util.List;
/**
* @author supermarketss@163.com
* @date 2024-01-19 - 14:08
*/
@Service
public class SaasMainService {
@Autowired
private DynamicDataSource dynamicDataSource;
@Autowired
private UserMapper userMapper;
@Autowired
private SaasMainMapper saasMainMapper;
@Autowired
private TransactionTemplate transactionTemplate;
@PostConstruct
public void a() {
List<SaasMain> saasMains1 = SpringUtil.getBean(SaasMainMapper.class).selectList(null);
System.out.println(saasMains1);
for (SaasMain saasMain : saasMains1) {
//动态构建数据源
DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.driverClassName(saasMain.getDriverClassName());
dataSourceBuilder.url(saasMain.getJdbcUrl());
dataSourceBuilder.username(saasMain.getUsername());
dataSourceBuilder.password(saasMain.getPassword());
DataSource source = dataSourceBuilder.build();
//id作为这个数据源的key
dynamicDataSource.addDataSource(saasMain.getId().toString(), source);
//设置当前数据源
DataSourceContextHolder.setDataSourceKey(saasMain.getId().toString());
//获取当前数据源
System.out.println(DataSourceContextHolder.getDataSourceKey());
//查询数据库
System.out.println(userMapper.selectList(null));
/* try {
//也可以使用事务,每个数据源事务互相隔离
transactionTemplate.execute(status -> {
User user = new User();
user.setId(RandomUtil.randomInt());
user.setName("张三");
this.userMapper.insert(user);
int a = 1 / 0;
return null;
});
} catch (Exception e) {
e.printStackTrace();
}*/
/* //因为使用的threadlocal 多线程的情况下这个数据源就会失效
//解决办法需要提前把变量传递到线程内部
String dbkey = DataSourceContextHolder.getDataSourceKey();
new Thread(() ->{
//重新设置数据源
DataSourceContextHolder.setDataSourceKey(dbkey);
System.out.println(userMapper.selectList(null));
}).start();*/
}
System.out.println();
}
}
参考
【正确姿势】完全理解 Spring AbstractRoutingDataSource 实现动态(多)数据源切换(避免踩坑)-CSDN博客