mysql分表插件_mybatis利用插件实现分表

如标题，这次的分表规则比较？？，部分用户相关表按产品维度划分，例如: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

for (Table t : list) {

parserTable(t, true);

}

} else if (statement instanceof Delete) {

Delete delete = (Delete) statement;

parserTable(delete.getTable(), true);

List

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

*/

@Component

public 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 getBean(String beanName) {

assertApplicationContext();

return (T) applicationContext.getBean(beanName);

}

public static T getBean(Class 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 exceptionMapperId;

private List 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 getExceptionMapperId() {

return exceptionMapperId;

}

public void setExceptionMapperId(List exceptionMapperId) {

this.exceptionMapperId = exceptionMapperId;

}

public List getAgentTables() {

return agentTables;

}

public void setAgentTables(List agentTables) {

this.agentTables = agentTables;

}

}