Such as the title, the comparison of sub table rules this time?? Some user related tables are divided by product dimensions, for example: user 1, user 2 (1, 2 is the product id, a new product will add a whole set of tables...). It is not appropriate to study a wave of sharing JDBC (later changed to sharing sphere), and it also has the feeling of killing chickens and cows.
Don't want to write SQL too much trouble, later said bad table to change, although there is a generation tool (inflexible), so choose mybatis plus brother, learn from his paging and other plug-ins to decide to implement a sub table plug-in, maintain the table that needs to be sub table in the configuration, use jsqlparser to parse SQL and rewrite SQL statements, no more nonsense about the code

/**

• Sub table plug-in
• @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;
    }
}

/**
 * Override table name set alias
 *
 * @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);

}

/**
* tools

• @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());
}

}

/**

• The holder of spring's ApplicationContext can obtain the bean s in the spring container in a static way,
• Request and 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> 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 Attribute is null,Please check whether it is injected SpringContextHolder!");
    }
}

/**
 * Get the Request object of the current Request
 *
 * @return HttpServletRequest
 */
public static HttpServletRequest getRequest() {
    ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    return requestAttributes.getRequest();
}

/**
 * Get the session object of the current request
 *
 * @return HttpSession
 */
public static HttpSession getSession() {
    return getRequest().getSession();
}

}

/**

• Query statement modification
• @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;
}

}