Tdengine实现动态表名

概要

时序数据库中，通过库名分割超级表，所以会出现许多不同库名相同结构的表，因此通过动态修改表名，结合MybatisPlus实现一个实体映射多个结构

自定义Mybatis拦截器

1.实现自定义拦截器
官方的DynamicTableNameInnerInterceptor拦截器只能根据固定的表名实现规则替换，在时序数据库中有许多不同库名表名但是结构相同的表，因此重写DynamicTableNameInnerInterceptor实现自定义拦截
代码如下：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class TaosDynamicTableNameInnerInterceptor implements InnerInterceptor {

    private TableNameHandler tableNameHandler;

    @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();
                if (value.contains(".")) {
                    TableNameHandler handler = tableNameHandler;
                    if (handler != null) {
                        builder.append(handler.dynamicTableName(sql, value));
                    } else {
                        builder.append(value);
                    }
                } else {
                    builder.append(value);
                }
            }
            last = name.getEnd();
        }
        if (last != sql.length()) {
            builder.append(sql.substring(last));
        }
        return builder.toString();
    }
}

这个拦截器与DynamicTableNameInnerInterceptor相比，tableNameHandlerMap改为了tableNameHandler，需要放入一个唯一的taos拦截TableNameHandler，并且规定替换规则

2.实现的TableNameHandler
代码如下：

public class TaosTableNameHandler implements TableNameHandler {
    //ThreadLocal存储不同线程的动态表名
    private static final ThreadLocal<String> TABLE_NAME = new ThreadLocal<>();
    //对外提供设置表名的方法
    public static void setTableName(String name) {
        TABLE_NAME.set(name);
    }
    //删除当前请求线程的day数据
    public static void removeTableName() {
        TABLE_NAME.remove();
    }
    //动态表名接口实现方法
    @Override
    public String dynamicTableName(String sql, String tableName) {
        // 检查表名是否包含"."
        if (tableName.contains(".")) {
            String table = TABLE_NAME.get();
            if (table != null) {
                // 确保移除操作成功，避免残留数据
                TABLE_NAME.remove();
                return table;
            }else {
                return tableName;
            }
        }
        // 如果表名不包含"."，则不进行替换，直接返回原表名
        return tableName;
    }
}

通过ThreadLocal设置动态表名

3.设置MybatisPlusConfig配置
再Config中引入拦截器，加入MybatisPlusInterceptor

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 动态表名插件
        TaosDynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new TaosDynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandler(new TaosTableNameHandler());
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);

        return interceptor;
    }

在interceptor中加入自定义的TaosDynamicTableNameInnerInterceptor

使用效果

1.创建一个测试类

@EqualsAndHashCode(callSuper = true)
@Data
@TableName("test1.test")
public class Test extends BaseTaosModel<Test> {

    @ApiModelProperty(value = "名称")
    private String name;

    @ApiModelProperty(value = "数值")
    private String pointValue;

    @Tags()
    private String eqpId;
}

2.测试

    @Bean
    public void test(){
        Test test = new Test();
        List<Test> list = testService.lambdaQuery().list();
        TaosTableNameHandler.setTableName("test2.test");
        List<Test> list1 = testService.lambdaQuery().list();
        System.out.println(list);
        System.out.println(list1);
    }

通过TaosTableNameHandler动态设置表名，调用后发现两个代码查询了不同库的相同结构表

小结

拦截器和查询语句都用到了MybatisPlus，具体配置在上篇文章Tdengine整合MybatisPlus