方案说明
随着业务发展,数据量越来越大,迫切需要做分库分表。 每个库里都有所有的完整的表。
1.首先确认分库的方案是1个站点1个数据库实例 dynamic-n,n是站点编码,站点编码从1开始递增。
2.每创建1个站点执行脚本动态创建1个库,以及dynamic库下的所有的表。
3.每次请求时,需要携带token,token中保存了当前用户对应的站点编码。
4.数据源使用自定义的AbstractRoutingDataSource。
5.每次执行sql语句时需要通过数据源获取连接时。
6.首先通过站点编码替换jdbcUrl中的库名,动态获取jdbcUrl。
7.然后根据driverName、jdbcUrl、userName、password获取数据源。
8.从数据源中获取连接并返回。
代码
配置项
注意
1.配置是1个json
2.数据库是dynamic_*[database]
center.dataSourceInfo = {"url": "jdbc:mysql://localhost:3306/dynamic_*[database]?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true","password": "******","username": "******","idleTimeOut": 30000,"minimumIdle": 1,"driverClassName": "com.mysql.jdbc.Driver","maximumPoolSize": 100,"connectionTimeout": 30000}
配置类
package dynamic.config;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import dynamic.exception.BusinessRuntimeException;
import dynamic.support.MultiTenantAwareRoutingSource;
import dynamic.util.EmptyUtil;
import dynamic.model.DatasourceConfiguration;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.HttpStatus;
import javax.sql.DataSource;
@Slf4j
@Configuration
public class DatasourceConfig {
Logger logger = LoggerFactory.getLogger(DatasourceConfig.class);
@Value("${center.dataSourceInfo}")
private String dataSourceInfo;
//默认的数据源配置 这里是什么场景使用暂时没想到
@Bean
@ConfigurationProperties(prefix = "datasource.default")
public DatasourceConfiguration defaultDatasourceConfiguration() {
return new DatasourceConfiguration();
}
@Bean
public DataSource dataSource() {
//MultiTenantAwareRoutingSource的构造方法的参数是1个数据源工厂
MultiTenantAwareRoutingSource dataSource = new MultiTenantAwareRoutingSource(lookupKey -> {
DataSource ds;
//默认数据源
if (MultiTenantAwareRoutingSource.DS_DEFAULT.equals(lookupKey.toString())) {
ds = createDefaultDatasource();
} else {
//动态数据源
if (EmptyUtil.isEmpty(dataSourceInfo)) {
throw new BusinessRuntimeException("无数据源配置信息", HttpStatus.NOT_FOUND);
}
//租户编码即站点编码
String tenantCode = StrUtil.removePrefix(lookupKey.toString(), MultiTenantAwareRoutingSource.DS_PREFIX);
log.info("dataSourceInfo:{}", dataSourceInfo);
//解析json为配置类
DatasourceConfiguration datasourceConfiguration
= JSONObject.parseObject(dataSourceInfo, DatasourceConfiguration.class);
//配置类判空
if (datasourceConfiguration == null) {
logger.error(StrUtil.format("无法获取正确数据源，请检查配置，租户编码：{}", lookupKey.toString()));
throw new IllegalStateException("无法获取正确数据源，请检查配置");
}
//具体的数据库名称
String database = new StringBuilder().append(tenantCode).toString();
//使用具体的数据库名称替换*[database]
//mysql://uat-other-mysql.yl.com:3306/white_cloud_dynamic_*[database]
datasourceConfiguration.setUrl(datasourceConfiguration.getUrl().replace("*[database]", database));
//创建数据源
ds = createDatasource(datasourceConfiguration, lookupKey.toString());
}
return ds;
});
return dataSource;
}
/***
* 使用默认数据源配置创建默认数据源
*/
private DataSource createDefaultDatasource() {
HikariDataSource dataSource = createDatasource(defaultDatasourceConfiguration(), "ds-default");
return dataSource;
}
/***
* 创建数据源
*/
private HikariDataSource createDatasource(DatasourceConfiguration datasourceConfiguration, String datasourcePoolName) {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setMaximumPoolSize(datasourceConfiguration.getMaximumPoolSize());
dataSource.setIdleTimeout(datasourceConfiguration.getIdleTimeOut());
dataSource.setDriverClassName(datasourceConfiguration.getDriverClassName());
dataSource.setJdbcUrl(datasourceConfiguration.getUrl());
dataSource.setUsername(datasourceConfiguration.getUsername());
dataSource.setPoolName("HikariPool-" + datasourcePoolName);
dataSource.setPassword(datasourceConfiguration.getPassword());
dataSource.setMinimumIdle(datasourceConfiguration.getMinimumIdle());
dataSource.setConnectionTestQuery("SELECT 1");
return dataSource;
}
}
路由数据源
package dynamic.support;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import dynamic.context.AccessContextHolder;
import dynamic.exception.BusinessRuntimeException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class MultiTenantAwareRoutingSource extends AbstractRoutingDataSource {
private static final Logger log = LoggerFactory.getLogger(MultiTenantAwareRoutingSource.class);
Logger logger = LoggerFactory.getLogger(AbstractRoutingDataSource.class);
/***
* 为保证线程安全 并发创建数据源的锁
*/
private final Lock lock = new ReentrantLock();
public static final String DS_PREFIX = "ds-";
public static final String DS_DEFAULT = "ds-default";
/***
* 数据源缓存
*/
Map<Object, DataSource> dataSources;
/***
* 创建数据源工厂类
*/
private MultiTenantAwareRoutingSource.IDatasourceFactory datasourceFactory;
/***
* 路由数据源构造方法
*/
public MultiTenantAwareRoutingSource(MultiTenantAwareRoutingSource.IDatasourceFactory datasourceFactory) {
this.datasourceFactory = datasourceFactory;
this.dataSources = new ConcurrentHashMap();
}
/***
* 数据源缓存
*/
public Map<Object, DataSource> getDataSources() {
return this.dataSources;
}
/***
* 添加数据源到数据源缓存
*/
public void addDataSources(Object lookupKey, DataSource datasource) {
this.dataSources.put(lookupKey, datasource);
}
/***
*重写父类的determineCurrentLookupKey方法
*用于设置查找数据源时 设置数据源的路由key
*/
protected Object determineCurrentLookupKey() {
//注意这个AccessContextHolder.getAccessContext()
//是1个ThreadLocal
//这个ThreadLocal是在哪设置的值？
//AuthRestInterceptor 下面贴代码
String tenantCode = AccessContextHolder.getAccessContext() == null ?
null : AccessContextHolder.getAccessContext().getTenantCode();
if (StrUtil.isEmpty(tenantCode)) {
log.error("无法确定租户：{}", JSONObject.toJSONString(AccessContextHolder.getAccessContext()));
throw new BusinessRuntimeException("无法确定租户", HttpStatus.NOT_FOUND);
} else {
return "ds-" + tenantCode;
}
}
/***
* 重写父类的determineCurrentLookupKey方法
* 根据路由key创建数据源
*/
protected DataSource determineTargetDataSource() {
Object lookupKey = this.determineCurrentLookupKey();
//根据路由key从缓存中获取数据源
DataSource dataSource = (DataSource)this.dataSources.get(lookupKey);
if (dataSource == null) {
//加锁防止重复创建 重复创建可能导致的问题
//线程A创建了dynamic-1 正在使用
//线程B也创建了dynamic-1 覆盖了A正在使用的dynamic-1数据源
//A有2个方法要执行 删除和增加 且需要保证事务
//那么如果第一次 用的是A创建的数据源 第二次 用的是B创建的数据源会不会有问题？
//个人觉得会有问题 因为2个connection连接不是1个连接了
this.lock.lock();
try {
if (dataSource == null) {
//使用数据源工厂 创建数据源
dataSource = this.datasourceFactory.create(lookupKey);
//添加到缓存
this.addDataSources(lookupKey, dataSource);
}
} finally {
this.lock.unlock();
}
} else {
this.logger.debug("==>使用已有数据源【{}】，当前数据源数量:【{}】<==", lookupKey.toString(), this.getDataSources().size());
}
return dataSource;
}
@Override
public void afterPropertiesSet() {
//重写父类的afterPropertiesSet 跳过一些校验!
}
public interface IDatasourceFactory {
DataSource create(Object lookUpKey);
}
}
AuthRestInterceptor
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package dynamic.security;
import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import dynamic.base.event.BaseInterceptor;
import dynamic.context.AbstractAccessUserService;
import dynamic.context.AccessContext;
import dynamic.context.AccessContextHolder;
import dynamic.security.annotation.RequiresPermissions;
import dynamic.util.HttpUtils;
import dynamic.util.JwtUtils;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpMethod;
import org.springframework.http.HttpStatus;
import org.springframework.util.StringUtils;
import org.springframework.web.method.HandlerMethod;
public class AuthRestInterceptor extends BaseInterceptor {
private static final Logger log = LoggerFactory.getLogger(AuthRestInterceptor.class);
public static ThreadLocal<Long> threadLocal = new ThreadLocal();
private String tokenHeader;
private String secret;
private AbstractAccessUserService accessUserService;
public AuthRestInterceptor(String tokenHeader, String secret, AbstractAccessUserService accessUserService) {
this.tokenHeader = tokenHeader;
this.secret = secret;
this.accessUserService = accessUserService;
}
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
if (request.getMethod().equals(HttpMethod.OPTIONS.name())) {
return true;
} else {
threadLocal.set(System.currentTimeMillis());
String authToken = request.getHeader(this.tokenHeader);
if (StringUtils.isEmpty(authToken)) {
log.info("[IP:{}]未授权的访问，token不能为空", HttpUtils.getIP(request));
this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, "未授权的访问，token不能为空");
return false;
} else {
AccessContext accessContext;
try {
accessContext = JwtUtils.getAccessUserFromToken(authToken, this.secret);
accessContext.setToken(authToken);
} catch (Exception var8) {
var8.printStackTrace();
log.info("[IP:{}]未授权的访问，请重新登录获取授权,异常原因:{}", HttpUtils.getIP(request), var8.getMessage());
this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, StrUtil.format("未授权的访问，请重新登录获取授权，异常原因:{}", new Object[]{var8.getMessage()}));
return false;
}
Assert.notNull(accessContext);
if (this.accessUserService.isLogoutToken(accessContext.getToken())) {
log.info("[用户名:{}-{}][IP:{}]会话已注销，请重新登录", new Object[]{accessContext.getName(), accessContext.getLoginName(), HttpUtils.getIP(request)});
this.buildFailResponse(response, HttpStatus.UNAUTHORIZED, "会话已注销，请重新登录");
return false;
} else {
AccessContextHolder.setAccessContext(accessContext);
HandlerMethod handlerMethod = (HandlerMethod)handler;
RequiresPermissions requiresPermissions = (RequiresPermissions)AnnotationUtil.getAnnotation(handlerMethod.getMethod(), RequiresPermissions.class);
if (requiresPermissions != null && !this.isPermitted(requiresPermissions)) {
log.info("[用户名:{}-{}][IP:{}]权限不足，无此权限:{}", new Object[]{accessContext.getName(), accessContext.getLoginName(), HttpUtils.getIP(request), this.getFormattedPermissions(requiresPermissions.value())});
this.buildFailResponse(response, HttpStatus.FORBIDDEN, "权限不足，无此权限:" + this.getFormattedPermissions(requiresPermissions.value()));
return false;
} else {
return super.preHandle(request, response, handler);
}
}
}
}
}
protected boolean isPermitted(RequiresPermissions requiresPermissions) {
return this.accessUserService.isPermitted(requiresPermissions);
}
protected String getFormattedPermissions(String[] permissions) {
StringBuilder sb = new StringBuilder();
if (permissions.length == 1) {
return permissions[0];
} else {
String[] var3 = permissions;
int var4 = permissions.length;
for(int var5 = 0; var5 < var4; ++var5) {
String permission = var3[var5];
sb.append(permission).append(",");
}
return sb.length() > 0 ? sb.substring(0, sb.length() - 1) : "系统数据异常";
}
}
public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
if (!request.getMethod().equals(HttpMethod.OPTIONS.name())) {
if (ex != null) {
log.error("[用户:{}-{}][IP:{}][uri:{}][耗时:{}ms]访问出错，异常原因:[{}]", new Object[]{AccessContextHolder.getAccessContext() == null ? "游客" : AccessContextHolder.getAccessContext().getName(), AccessContextHolder.getAccessContext() == null ? "guest" : AccessContextHolder.getAccessContext().getLoginName(), HttpUtils.getIP(request), request.getRequestURI(), System.currentTimeMillis() - (Long)threadLocal.get(), ex.getMessage()});
} else {
log.info("[用户:{}-{}][IP:{}][uri:{}][耗时:{}ms]访问成功", new Object[]{AccessContextHolder.getAccessContext() == null ? "游客" : AccessContextHolder.getAccessContext().getName(), AccessContextHolder.getAccessContext() == null ? "guest" : AccessContextHolder.getAccessContext().getLoginName(), HttpUtils.getIP(request), request.getRequestURI(), System.currentTimeMillis() - (Long)threadLocal.get()});
}
AccessContextHolder.remove();
threadLocal.remove();
}
super.afterCompletion(request, response, handler, ex);
}
}
