动态表名
描述： Sql执行时，动态的修改表名
简单业务场景： 日志或者其他数据量大的表，通过日期进行了水平分表，需要通过日期参数，动态的查询数据。
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings({
"rawtypes"})
public class DynamicTableNameInnerInterceptor implements InnerInterceptor {
private Map<String, TableNameHandler> tableNameHandlerMap;
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
mpBs.sql(this.changeTable(mpBs.sql()));
}
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(this.changeTable(mpBs.sql()));
}
}
protected String changeTable(String sql) {
TableNameParser parser = new TableNameParser(sql);
List<TableNameParser.SqlToken> names = new ArrayList<>();
parser.accept(names::add);
StringBuilder builder = new StringBuilder();
int last = 0;
for (TableNameParser.SqlToken name : names) {
int start = name.getStart();
if (start != last) {
builder.append(sql, last, start);
String value = name.getValue();
TableNameHandler handler = tableNameHandlerMap.get(value);
if (handler != null) {
builder.append(handler.dynamicTableName(sql, value));
} else {
builder.append(value);
}
}
last = name.getEnd();
}
if (last != sql.length()) {
builder.append(sql.substring(last));
}
return builder.toString();
}
}
测试案例
案例目标：根据传入的月份参数，动态的查询xx_月份的表
1、 复制几张表，并插入一些测试数据；
2、 实现TableNameHandler接口；
public class MyTableNameHandler implements TableNameHandler {
/**
* @param sql 原始SQL
* @param tableName 表名
* @return 动态表名
*/
@Override
public String dynamicTableName(String sql, String tableName) {
// 模拟获取月份参数，实际应该从参数中获取
String[] month = {
"", "_03", "_04"};
// 随机获取
int nextInt = new Random().nextInt(2);
String dynamicTableName = "order_tbl" + month[nextInt];
System.err.println("动态查询表：" + dynamicTableName);
return dynamicTableName;
}
}
1、 配置类添加插件；
// 添加动态表名插件
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor=new DynamicTableNameInnerInterceptor();
TableNameHandler tableNameHandler=new MyTableNameHandler();
Map<String, TableNameHandler> tableNameHandlerMap=new HashMap<>();
tableNameHandlerMap.put("order_tbl",tableNameHandler); // order_tbl表配置动态表名插件
dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
1、 测试：后台SQL已经实现，不同的参数查询不同表的功能；
