多租户方案及对比
1、行级隔离
行隔离,存在数据融合,数据库性能是考研。
2、表级隔离
技术复杂度高,改造难度打。
3、库级隔离
数据隔离,数据安全性得到保证,单个租户数据量少,会造成资源浪费。
最终方案:库级隔离,动态数据源
Spring框架自带多数据源支持,提供AbstractRoutingDataSource抽象类,与我们现有系统整合简单,易于自定义扩展。
配置中心配置是否Saas化部署,则区分与特殊租户,单独部署服务。
Saas部署,需要支持多数据源热加载和删除及动态切换数据源,增加租户管理模块,租户管理相关数据(包含模块租户信息管理、数据源信息管理、定时任务管理等相关功能),租户管理相关数据配置在核心(默认)库,只有登录用户租户ID为1才能到核心数据库查询。
数据库选择,租户独立数据库和租户共享数据库区别:
配置中心,配置默认数据源(租户管理数据源),启动过程中加载数据库中已启用的租户数据源。
用户操作流程:
公共模块
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;
/**
* @description :配置常量
*/
@RefreshScope
@Component
public class SaasConstants implements InitializingBean {
@Value("${saas.enable:false}")
private String saasEnable;
@Value("${saas.default.tenant:1}")
private String defaultTenantId;
/**
* saas开启区分
*/
public static String SAAS_ENABLE;
/**
* 默认租户Id
*/
public static String DEFAULT_TENANT_ID;
@Override
public void afterPropertiesSet() {
SAAS_ENABLE = this.saasEnable;
DEFAULT_TENANT_ID = this.defaultTenantId;
}
}
数据源配置,支持事务
import com.ruoyi.system.datasource.DynamicDataSource;
import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.concurrent.ConcurrentHashMap;
/**
* @description :数据源配置
*/
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {
@Value("${mybatis.typeAliasesPackage:com.ruoyi.**.mapper}")
private String typeAliasesPackage;
@Value("${mybatis.mapperLocations:classpath*:mapper/system-cloud/*.xml}")
private String mapperLocations;
/**
* @return {@link DataSource }
* @description 默认数据源
*/
@Bean("defaultSource")
@ConfigurationProperties("spring.datasource")
public DataSource defaultSource() {
return DataSourceBuilder.create().build();
}
/**
* @return {@link DataSource }
* @description 自定义动态数据源
*/
@Primary
@Bean("dynamicDataSource")
public DataSource dynamicDataSource() {
return new DynamicDataSource(defaultSource(), new ConcurrentHashMap<>(16));
}
/**
* @return {@link SqlSessionFactoryBean }
* @description 修改Mybatis数据源配置
*/
@Bean("sqlSessionFactoryBean")
public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("databaseIdProvider") DatabaseIdProvider databaseIdProvider) throws IOException {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
// 配置自定义动态数据源
sessionFactory.setDataSource(dynamicDataSource());
// 实体、Mapper类映射
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
sessionFactory.setDatabaseIdProvider(databaseIdProvider);
return sessionFactory;
}
/**
* @return {@link PlatformTransactionManager }
* @description 开启动态数据源@Transactional注解事务管理的支持
*/
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
}
ThreadLocal实现线程内数据共享
/**
* @description: 动态数据源管理
* @version: 1.0.0
*/
public class DataSourceContextHolder {
/**
* 数据源key
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* @param key 数据源key
* @description 切换数据源
*/
public static void setDataSourceKey(String key) {
CONTEXT_HOLDER.set(key);
}
/**
* @return {@link String }
* @description 获取数据源key
*/
public static String getDataSourceKey() {
return CONTEXT_HOLDER.get();
}
/**
* @description 重置数据源
*/
public static void clearDataSourceKey() {
CONTEXT_HOLDER.remove();
}
}
spring管理多数据源
import com.ruoyi.common.core.constant.UnimisConstants;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.system.config.SaasConstants;
import com.ruoyi.system.domain.SysTenantDto;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.Date;
import java.util.Map;
/**
* @description: 动态数据源实现类 切换数据源必须在调用service之前进行,也就是开启事务之前
* @version: 1.0.0
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);
/**
* 租户数据源列表
*/
private Map<Object, Object> dynamicTargetDataSources;
/**
* @param defaultTargetDataSource 默认数据源
* @param targetDataSources 目标数据源
* @description 决定使用哪个数据源之前需要把多个数据源的信息以及默认数据源信息配置好
* @date 2023-04-06 15:40:24
*/
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
setDefaultTargetDataSource(defaultTargetDataSource);
targetDataSources.put(String.valueOf(UnimisConstants.DEFAULT_TENANT_ID), defaultTargetDataSource);
setTargetDataSources(targetDataSources);
this.dynamicTargetDataSources = targetDataSources;
super.afterPropertiesSet();
}
/**
* @return {@link DataSource }
* @description 如果不希望数据源在启动配置时就加载好,可以定制这个方法,从任何你希望的地方读取并返回数据源
* 比如从数据库、文件、外部接口等读取数据源信息,并最终返回一个DataSource实现类对象即可
*/
@Override
protected DataSource determineTargetDataSource() {
return super.determineTargetDataSource();
}
/**
* @return {@link Object }
* @description :
* 如果希望所有数据源在启动配置时就加载好,这里通过设置数据源Key值来切换数据,定制这个方法
*
* 实现数据源切换要扩展的方法,该方法的返回值就是项目中所要用的DataSource的key值,
* 拿到该key后就可以在resolvedDataSource中取出对应的DataSource,如果key找不到对应的DataSource就使用默认的数据源。
*/
@Override
protected Object determineCurrentLookupKey() {
String dataSourceName = DataSourceContextHolder.getDataSourceKey();
if (Boolean.TRUE.toString().equals(SaasConstants.SAAS_ENABLE)) {
if (!StringUtils.isEmpty(dataSourceName)) {
if (this.dynamicTargetDataSources.containsKey(dataSourceName)) {
logger.info("当前数据源为:{}", dataSourceName);
} else {
logger.info("不存在的数据源:{}", dataSourceName);
}
} else {
logger.info("当前数据源为:默认数据源");
}
}
return dataSourceName;
}
/**
* @param defaultDataSource Object
* @description 设置默认数据源
*/
@Override
public void setDefaultTargetDataSource(Object defaultDataSource) {
super.setDefaultTargetDataSource(defaultDataSource);
}
/**
* @param dataSources Map<Object, Object>
* @description 设置数据源集合
*/
@Override
public void setTargetDataSources(Map<Object, Object> dataSources) {
super.setTargetDataSources(dataSources);
this.dynamicTargetDataSources = dataSources;
}
/**
* @param tenant 租户
* @description 新版租户热加载
*/
public R<Boolean> setDataSources(SysTenantDto tenant) {
try {
Date expiryDate = tenant.getDowntime();
// 比对是否过期
if (expiryDate != null && !expiryDate.after(new Date())) {
logger.warn("[{}]已经超出有效期,请续签之后再试", tenant.getTenantName());
}
HikariDataSource dataSource = DynamicDataSourceFactory.buildHikariDatasource(tenant);
if (dataSource == null) {
return R.fail("数据源获取失败");
}
// 测试连接
dataSource.getConnection();
this.dynamicTargetDataSources.put(tenant.getTenantId().toString(), dataSource);
setTargetDataSources(this.dynamicTargetDataSources);
super.afterPropertiesSet();
logger.info("数据源初始化成功------>" + tenant.getTenantId());
return R.ok(Boolean.TRUE);
} catch (Exception e) {
logger.error("[{}]:数据源连接不上, 可能是连接参数有误!", tenant.getTenantId(), e);
return R.fail("数据源连接不上");
}
}
/**
* description 删除租户数据源
*
* @param tenantId 租户Id
**/
public void removeDataSources(String tenantId) {
try {
if (this.dynamicTargetDataSources.containsKey(tenantId)) {
this.dynamicTargetDataSources.remove(tenantId);
setTargetDataSources(this.dynamicTargetDataSources);
super.afterPropertiesSet();
logger.info("数据源删除成功------>{}", tenantId);
}
} catch (Exception e) {
logger.error("[{}]:数据源删除失败!", tenantId, e);
}
}
/**
* description 判断租户数据源是否存在
*
* @param tenantId 租户Id
* @return java.lang.Boolean
**/
public Boolean existsDataSource(String tenantId) {
return this.dynamicTargetDataSources.containsKey(tenantId);
}
}
获取HikariDataSource数据源
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.system.domain.DataBasePoolParams;
import com.ruoyi.system.domain.SysDatasource;
import com.ruoyi.system.domain.SysTenantDto;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @description :数据源工厂
*/
public class DynamicDataSourceFactory {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceFactory.class);
/**
* 默认超时 毫秒
*/
private static final Long IDLE_TIMEOUT_MS_DEFAULT = 180000L;
/**
* 连接在连接池中的最大生命周期 默认值
*/
private static final Long MAX_LIFETIME_MS_DEFAULT = 900000L;
/**
* 连接超时时间
*/
private static final Long CONNECTION_TIMEOUT_MS_DEFAULT = 60000L;
/**
* 验证超时时间
*/
private static final Long VALIDATION_TIMEOUT_MS_DEFAULT = 3000L;
/**
* 测试查询sql
*/
private static final String CONNECTION_TEST_QUERY = "select 1 from dual";
/**
* @param tenantDto 租户dto
* @return {@link HikariDataSource }
* @description 构建线程池数据源
**/
public static HikariDataSource buildHikariDatasource(SysTenantDto tenantDto) {
SysDatasource datasource = tenantDto.getDatasource();
if (datasource != null) {
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setDriverClassName(datasource.getDriver());
hikariDataSource.setJdbcUrl(datasource.getUrl());
hikariDataSource.setUsername(datasource.getUsername());
hikariDataSource.setPassword(datasource.getPassword());
String poolParams = datasource.getPoolParams();
DataBasePoolParams params = new DataBasePoolParams();
if (StringUtils.isNotEmpty(poolParams)) {
try {
logger.info("DataBasePoolParams:{}", poolParams);
ObjectMapper objectMapper = new ObjectMapper();
params = objectMapper.readValue(poolParams, DataBasePoolParams.class);
} catch (JsonProcessingException e) {
logger.error("JSON对象转换失败", e);
}
}
setPoolParams(hikariDataSource, params);
return hikariDataSource;
}
return null;
}
/**
* description 数据源设置连接池参数
*
* @param hikariDataSource 数据源
* @param params 连接池参数
**/
private static void setPoolParams(HikariDataSource hikariDataSource, DataBasePoolParams params) {
// 最小线程数
if (params.getMinimumIdle() != null) {
hikariDataSource.setMinimumIdle(params.getMinimumIdle());
} else {
hikariDataSource.setMinimumIdle(2);
}
// 最大线程数
if (params.getMaximumPoolSize() != null) {
hikariDataSource.setMaximumPoolSize(params.getMaximumPoolSize());
} else {
hikariDataSource.setMaximumPoolSize(5);
}
// 超时设置
if (params.getIdleTimeoutMs() != null) {
hikariDataSource.setIdleTimeout(params.getIdleTimeoutMs());
} else {
hikariDataSource.setIdleTimeout(IDLE_TIMEOUT_MS_DEFAULT);
}
// 连接在连接池中的最大生命周期
if (params.getMaxLifetimeMs() != null) {
hikariDataSource.setMaxLifetime(params.getMaxLifetimeMs());
} else {
hikariDataSource.setMaxLifetime(MAX_LIFETIME_MS_DEFAULT);
}
// 自动提交区分
if (params.getAutoCommit() != null) {
hikariDataSource.setAutoCommit(params.getAutoCommit());
} else {
hikariDataSource.setAutoCommit(Boolean.TRUE);
}
// 连接超时时间
if (params.getConnectionTimeoutMs() != null) {
hikariDataSource.setConnectionTimeout(params.getConnectionTimeoutMs());
} else {
hikariDataSource.setConnectionTimeout(CONNECTION_TIMEOUT_MS_DEFAULT);
}
// 连接超时时间
if (params.getConnectionTimeoutMs() != null) {
hikariDataSource.setConnectionTimeout(params.getConnectionTimeoutMs());
} else {
hikariDataSource.setConnectionTimeout(CONNECTION_TIMEOUT_MS_DEFAULT);
}
// 验证超时时间
if (params.getValidationTimeoutMs() != null) {
hikariDataSource.setValidationTimeout(params.getValidationTimeoutMs());
} else {
hikariDataSource.setConnectionTimeout(VALIDATION_TIMEOUT_MS_DEFAULT);
}
// 默认测试查询语句
hikariDataSource.setConnectionTestQuery(CONNECTION_TEST_QUERY);
}
}
服务器启动初始化数据源
import com.ruoyi.common.core.constant.Constants;
import com.ruoyi.common.core.constant.UnimisConstants;
import com.ruoyi.system.domain.SysTenantDto;
import com.ruoyi.system.service.ISysTenantService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.TimeUnit;
/**
* @description: 租户数据源初始化
* @version: 1.0.0
*/
@ConditionalOnProperty(prefix = "saas", name = "enable", havingValue = "true")
@Configuration
public class DynamicDataSourceInit {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceInit.class);
@Resource
private ISysTenantService tenantService;
@Resource
private DynamicDataSource dynamicDataSource;
@Resource(name = Constants.THREAD_POOL_NAME)
private ThreadPoolTaskExecutor poolTaskExecutor;
/**
* 初始化租户数据源
*/
@PostConstruct
public void initDataSource() {
logger.info("=====初始化租户数据源=====");
// 加载默认数据源外的其他数据源
List<SysTenantDto> tenantList = tenantService.selectTenantAll();
if (tenantList.size() <= 0) {
logger.info("初始化租户数据源完成, 没有有效租户");
return;
}
CountDownLatch countDownLatch = new CountDownLatch(tenantList.size());
tenantList.forEach(sysTenantDto -> {
poolTaskExecutor.execute(() -> {
if (sysTenantDto.getTenantId() != UnimisConstants.DEFAULT_TENANT_ID) {
dynamicDataSource.setDataSources(sysTenantDto);
}
countDownLatch.countDown();
});
});
try {
boolean await = countDownLatch.await(2, TimeUnit.MINUTES);
logger.info("初始化租户数据源完成:{}", await);
} catch (InterruptedException e) {
logger.error("初始化租户数据源超时", e);
}
}
}
动态数据源切换,中间及程序需要切换模块
1、页面请求,Controller添加代理实现多数据源切换。
import com.ruoyi.common.security.utils.SecurityUtils;
import com.ruoyi.system.datasource.DataSourceContextHolder;
import com.ruoyi.system.domain.SysOperLog;
import com.ruoyi.system.service.ISysTenantService;
import jodd.util.ArraysUtil;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
/**
* @description: Aop动态切换多数据源
* 请注意:这里order一定要小于tx:annotation-driven的order,即先执行DynamicDataSourceAspect切面,再执行事务切面,才能获取到最终的数据源
* @version: 1.0.0
*/
@ConditionalOnProperty(prefix = "saas", name = "enable", havingValue = "true")
@Aspect
@Order(1)
@Component
public class ControllerDynamicDsAspect {
private static final Logger logger = LoggerFactory.getLogger(ControllerDynamicDsAspect.class);
@Resource
private ISysTenantService tenantService;
/**
* 无需切换数据源请求
*/
private static final String[] FILTER_URI = {"/operlog"};
/**
* 日志存储无需切换数据源title
*/
private static final String[] OPERATE_LOG_TITLE = {"租户管理", "数据源管理"};
/**
* 切换数据源
*/
@Before("execution(public * com.ruoyi.system.controller..*.*(..)) && !execution(* com.ruoyi.system.controller.tenant.*.*(..))")
public void switchDataSource(JoinPoint joinPoint) {
// 判断是否登录
if (SecurityUtils.getLoginUser() == null || SecurityUtils.getLoginUser().getUserDTO() == null
|| SecurityUtils.getLoginUser().getUserDTO().getSysUser() == null) {
ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
if (attributes == null) {
throw new RuntimeException("请重新登录");
}
HttpServletRequest request = attributes.getRequest();
String uri = request.getRequestURI();
if (ArraysUtil.contains(FILTER_URI, uri)) {
for (Object arg : joinPoint.getArgs()) {
if (arg instanceof SysOperLog) {
if (ArraysUtil.contains(OPERATE_LOG_TITLE, ((SysOperLog) arg).getTitle())) {
return;
}
// 日志对象中存在租户Id
tenantService.switchDataSource(((SysOperLog) arg).getTenantId());
return;
}
}
}
logger.error("请重新登录租户Id不存在,uri:{}", uri);
throw new RuntimeException("请重新登录");
}
// 租户Id
Long tenantId = SecurityUtils.getTenantId();
tenantService.switchDataSource(tenantId);
}
/**
* 重置数据源
*/
@After("execution(public * com.ruoyi.system.controller..*.*(..)) && !execution(* com.ruoyi.system.controller.tenant.*.*(..))")
public void restoreDataSource() {
// 将数据源置为默认数据源
logger.info("重置数据源");
DataSourceContextHolder.clearDataSourceKey();
}
}
2、消息消费(mq)需要支持动态切换数据源。
import com.ruoyi.system.datasource.DataSourceContextHolder;
import com.ruoyi.system.domain.SysTenantDto;
import com.ruoyi.system.service.ISysTenantService;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
/**
* @description :动态数据源切面
*/
@ConditionalOnProperty(prefix = "saas", name = "enable", havingValue = "true")
@Component
@Aspect
public class DynamicDataSourceAspect {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
@Resource
private ISysTenantService tenantService;
@Pointcut("@annotation(com.ruoyi.system.config.annotation.DynamicDataSource)")
public void dbPointCut(){
}
@Before("dbPointCut()")
public void beforeSwitchDs(JoinPoint point){
Object[] args = point.getArgs();
for (Object arg : args) {
// 租户对象切换
if (arg instanceof SysTenantDto) {
tenantService.switchDataSource(((SysTenantDto) arg).getTenantId());
return;
}
// 租户Id切换
else if (arg instanceof Long) {
tenantService.switchDataSource((Long) arg);
return;
}
}
}
@After("dbPointCut()")
public void afterSwitchDs(){
// 清空数据源
DataSourceContextHolder.clearDataSourceKey();
}
}
3、异步线程,线程切换需要子线程继承父线程数据源。
4、单点登录需要支持动态切换数据源。
5、任务调度需要动态切换数据源。
6、对外接口需要支持动态切换数据源。
参照:https://blog.csdn.net/u014528861/article/details/116655292