方案说明
随着业务发展,数据量越来越大,迫切需要做分库分表。 每个库里都有所有的完整的表。
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.DSDEFAULT.equals(lookupKey.toString())) { ds = createDefaultDatasource(); } else { //动态数据源 if (EmptyUtil.isEmpty(dataSourceInfo)) { throw new BusinessRuntimeException("无数据源配置信息", HttpStatus.NOTFOUND); } //租户编码即站点编码 String tenantCode = StrUtil.removePrefix(lookupKey.toString(), MultiTenantAwareRoutingSource.DSPREFIX); 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/whiteclouddynamic[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** * 创建数据源工厂类 */ 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); } }