如标题，这次的分表规则比较？？，部分用户相关表按产品维度划分，例如:user_1,user_2（1，2 是产品 id，新加一个产品就要新增一整套表 …）研究了一波 sharing-jdbc(后面改成了 sharding-sphere) 不太合适，也有种杀鸡牛刀的感觉。
不想手写 SQL 太麻烦，后面说不好表要改动，虽然有生成工具 (不灵活)，所以选择了 Mybatis-plus 这个兄弟，借鉴他的分页等各种插件决定自己实现一个分表插件，把需要分表的表在配置中维护，利用 jsqlparser 解析 sql 重写 sql 语句，废话不多说上代码
/**
分表插件
@author chonglou
@date 2019/2/2117:04
*/@Intercepts({@Signature(type = StatementHandler.class, method = “prepare”, args = {Connection.class, Integer.class})})public class ShardInterceptor implements Interceptor, ShardAgent {
private final ShardProperties shardProperties;
public ShardInterceptor(ShardProperties shardProperties) {
this.shardProperties = shardProperties;
}
public static final CCJSqlParserManager parser = new CCJSqlParserManager();
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue(“delegate.mappedStatement”);
if (!shardProperties.isException(mappedStatement.getId())) {
if (SqlCommandType.INSERT.equals(mappedStatement.getSqlCommandType())
|| SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())
|| SqlCommandType.UPDATE.equals(mappedStatement.getSqlCommandType())
|| SqlCommandType.DELETE.equals(mappedStatement.getSqlCommandType())) {
String sql = statementHandler.getBoundSql().getSql();
Statement statement = parser.parse(new StringReader(sql));
if (statement instanceof Select) {
Select select = (Select) statement;
TableNameModifier modifier = new TableNameModifier(this);
select.getSelectBody().accept(modifier);
} else if (statement instanceof Update) {
Update update = (Update) statement;
List<Table> list = update.getTables();
for (Table t : list) {
parserTable(t, true);
}
} else if (statement instanceof Delete) {
Delete delete = (Delete) statement;
parserTable(delete.getTable(), true);
List<Table> list = delete.getTables();
for (Table t : list) {
parserTable(t, true);
}
} else if (statement instanceof Insert) {
Insert insert = (Insert) statement;
parserTable(insert.getTable(), false);
}
StatementDeParser deParser = new StatementDeParser(new StringBuilder());
statement.accept(deParser);
sql = deParser.getBuffer().toString();
ReflectionUtils.setFieldValue(statementHandler.getBoundSql(), “sql”, sql);
}
}
return invocation.proceed();
}
private Object realTarget(Object target) {
if (Proxy.isProxyClass(target.getClass())) {
MetaObject metaObject = SystemMetaObject.forObject(target);
return realTarget(metaObject.getValue(“h.target”));
} else {
return target;
}
}
/**
* 覆盖表名设置别名
*
* @param table
* @return
*/
private Table parserTable(Table table, boolean alias) {
if (null != table) {
if (alias) {
table.setAlias(new Alias(table.getName()));
}
table.setName(getTargetTableName(table.getName()));
}
return table;
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public void setProperties(Properties properties) {
}
@Override
public String getTargetTableName(String tableName) {
if (shardProperties.isAgentTable(tableName)) {
return ShardUtil.getTargetTableName(tableName);
}
return tableName;
}
}
/**
@author chonglou
@date 2019/2/2218:24
*/public interface ShardAgent {
String getTargetTableName(String name);
}
/*** 工具
@author chonglou
@date 2019/2/2514:11
*/public class ShardUtil {
private final static String KEY_GENERATOR = “keyGenerator”;
public static void setKeyGenerator(Object keyGenerator) {
HttpServletRequest request = SpringContextHolder.getRequest();
request.setAttribute(KEY_GENERATOR, keyGenerator);
}
public static String getTargetTableName(String tableName) {
HttpServletRequest request = SpringContextHolder.getRequest();
Object productId = request.getAttribute(KEY_GENERATOR);
if (null == productId) {
throw new RuntimeException(“keyGenerator is null.”);
}
return tableName.concat(“_”).concat(productId.toString());
}
}
/**
Spring 的 ApplicationContext 的持有者, 可以用静态方法的方式获取 spring 容器中的 bean,
Request 以及 Session
*
@author chonglou
*/@Componentpublic class SpringContextHolder implements ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringContextHolder.applicationContext = applicationContext;
}
public static ApplicationContext getApplicationContext() {
assertApplicationContext();
return applicationContext;
}
public static <T> T getBean(String beanName) {
assertApplicationContext();
return (T) applicationContext.getBean(beanName);
}
public static <T> T getBean(Class<T> requiredType) {
assertApplicationContext();
return applicationContext.getBean(requiredType);
}
private static void assertApplicationContext() {
if (null == SpringContextHolder.applicationContext) {
throw new RuntimeException(“applicationContext 属性为 null, 请检查是否注入了 SpringContextHolder!”);
}
}
/**
* 获取当前请求的 Request 对象
*
* @return HttpServletRequest
*/
public static HttpServletRequest getRequest() {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
return requestAttributes.getRequest();
}
/**
* 获取当前请求的 session 对象
*
* @return HttpSession
*/
public static HttpSession getSession() {
return getRequest().getSession();
}
}
/**
查询语句修改
@author chonglou
@date 2019/2/2211:31
*/public class TableNameModifier extends SelectDeParser {
private ShardAgent shardAgent;
TableNameModifier(ShardAgent shardAgent) {
super();
this.shardAgent = shardAgent;
}
@Override
public void visit(Table tableName) {
StringBuilder buffer = new StringBuilder();
tableName.setName(shardAgent.getTargetTableName(tableName.getName()));
buffer.append(tableName.getFullyQualifiedName());
Alias alias = tableName.getAlias();
if (alias == null) {
alias = new Alias(tableName.getName());
}
buffer.append(alias);
Pivot pivot = tableName.getPivot();
if (pivot != null) {
pivot.accept(this);
}
MySQLIndexHint indexHint = tableName.getIndexHint();
if (indexHint != null) {
buffer.append(indexHint);
}
}
}/**
@author chonglou
@date 2019/2/2215:34
*/@ConfigurationProperties(prefix = “shard.config”)public class ShardProperties {
private List<String> exceptionMapperId;
private List<String> agentTables;
public boolean isException(String mapperId) {
return null != exceptionMapperId && exceptionMapperId.contains(mapperId);
}
public boolean isAgentTable(String tableName) {
return null != agentTables && agentTables.contains(tableName);
}
public List<String> getExceptionMapperId() {
return exceptionMapperId;
}
public void setExceptionMapperId(List<String> exceptionMapperId) {
this.exceptionMapperId = exceptionMapperId;
}
public List<String> getAgentTables() {
return agentTables;
}
public void setAgentTables(List<String> agentTables) {
this.agentTables = agentTables;
}
}