Mybtais plus动态表名实践

　　物联网项目不知你们是否会遇到这样的需求：设备端上报的部分数据要求平台侧进行存储，有时还需要对这些数据进行分析/历史数据查询（清洗），但是这些数据有非常的庞大，清洗也是每天进行的，清洗后的数据还需要按天进行删除，仅保留近一个月数据

　　最初拿到这样的需求，第一反应就是将设备上报的数据按天进行分表，数据库选择的是PGSQL，pgsql对于json操作支持还是很友好的。当然你也可以使用mongo，看项目要求吧。

　　确定方案：程序初始化时，创建当日数据库（需判断当日数据表是否存在），然后写一个定时任务，每天凌晨创建数据库，为了防止日常定时任务已创建表但当日重启项目，创建表时不能使用create or replace,只能去查是否存在当天的表。

　　方案确定，接下来就是代码实现，首先数据表肯定是动态生成的，创建方法也很简单，这里一并给出。

　　动态建表

CREATE TABLE "public".${tableName}
        (
            "id"         SERIAL8 PRIMARY KEY NOT NULL,
            "station_sn" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
            "sensor_code" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
            "data_info" jsonb NOT NULL,
            "data_type" int2,
            "timestamp" timestamp(6) NOT NULL,
            "dept_id" varchar(32) COLLATE "pg_catalog"."default"
        )
        ;

        ALTER TABLE "public".${tableName} OWNER TO "postgres";

        CREATE INDEX ${tableName}_idx_dept_id ON "public".${tableName} USING btree (
            "dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
            );

        CREATE INDEX ${tableName}_idx_sensor_code ON "public".${tableName} USING btree (
            "sensor_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
            );

        CREATE INDEX ${tableName}_idx_station_sn ON "public".${tableName} USING btree (
            "station_sn" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
            );

        CREATE INDEX ${tableName}_data_info ON "public".${tableName} USING gin (
            "data_info"
            );


        COMMENT ON COLUMN "public".${tableName}."station_sn" IS '测站编号';
        COMMENT ON COLUMN "public".${tableName}."sensor_code" IS '传感器编码';
        COMMENT ON COLUMN "public".${tableName}."data_info" IS '数据';
        COMMENT ON COLUMN "public".${tableName}."data_type" IS '数据类型';
        COMMENT ON COLUMN "public".${tableName}."timestamp" IS '入库时间';
        COMMENT ON COLUMN "public".${tableName}."dept_id" IS '部门id';

查询表是否存在

select tablename
        from pg_tables
        where schemaname = 'public'
          and tablename = #{tableName}

若需要查询某类形的某日数据，我提供的方案是：前端告知数据类型、日期，由后端组装后查询对应的数据。第一想法就是像上面动态创建表一样写xml来实现，这样比较简单，但是过于繁琐，对于我这种编码过程喜欢“偷懒”的人，肯定是不愿使用这种方案的--程序使用的是mybatis plus，目的就是要干掉非必要的xml,研究了一下开源项目，发现还真有一种方法可以实现我的想法。

下面整理一下按类按日查询的代码实现

Property配置

@Getter
@Setter
@ConfigurationProperties(prefix = "dynamic.table")
public class DynamicTableProperty {

    private String source;

    private String result;

    private String history;

　  private String bus;
}

yml文件（哪几类数据可以在这里配置）

dynamic:
  table:
    source: t_stat_source
    result: t_stat_result
    history: t_device_history
    bus: t_bus_data

接下来就是mybatis配置

@Configuration
@Slf4j
@EnableTransactionManagement
@MapperScan("com.hd.uws.mapper")
@EnableConfigurationProperties(DynamicTableProperty.class)
public class MybatisPlusConfig {

　　
    final DynamicTableProperty tableProperty;
    List<String> tables;

    public MybatisPlusConfig(DynamicTableProperty tableProperty) {
        this.tableProperty = tableProperty;
        tables = Lists.newArrayList(tableProperty.getSource(),
                tableProperty.getHistory(), tableProperty.getResult());

    }

　  public static ThreadLocal<Map<String, String>> DYNAMIC_TABLE_NAME = new ThreadLocal<>();
    

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     * <p>
     * Mybatis Plus 3.4.0之后useDeprecatedExecutor已经@Deprecated,且后续的版本均不需要设置该属性
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        // 动态表名插件
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandler(this::dynamicTableName);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        //分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }

    private String dynamicTableName(String sql, String tableName) {
        String newTable = null;
        try{
            for (String table : tables) {
                Map<String, String> dynamicTableName = DYNAMIC_TABLE_NAME.get();
                if(!CollectionUtils.isEmpty(dynamicTableName)) {
                    newTable = dynamicTableName.get(table);
                }
                if (table.equals(tableName) && newTable != null) {
                    tableName = newTable;
                    break;
                }
            }
        }catch(Exception e){
            log.error("动态表名配置失败:{}",e.getLocalizedMessage());
        } finally{
            ThreadLocalConstants.DYNAMIC_TABLE_NAME.remove();
        }
        return tableName;
    }
}

配合mybatis plus使用也非常简单，tableName就是你根据前端请求类型和日期动态拼接的，如配置的初始表为t_bus_data，需要查询flow类型2022-08-22日数据，则tableName应为

t_bus_data_flow_2022-08-22

 

 建议：mybatis plus 使用时最好用LambdaQueryWrapper/LambdaUpdataWrapper,只要数据库字段变更，代码里面有问题的地方就很一目了然