springboot mybatis-plus tdengine 创建超级表，动态创建子表，动态查询子表

tdengine 创建超级表， 子表用不同名字区分，查询单个子表

CustomTableNameHandler

import com.baomidou.mybatisplus.extension.parsers.ITableNameHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.reflection.MetaObject;

/**
 * CustomTableNameHandler
 *
 * @author chentudong
 * @date 2022/12/22 18:32
 * @since 1.0
 */
public class CustomTableNameHandler implements ITableNameHandler {
  private static ThreadLocal<String> TABLE_NAME = new ThreadLocal<>();


  /**
   * 生成动态表名，无改变返回 NULL
   *
   * @param metaObject 元对象
   * @param sql        当前执行 SQL
   * @param tableName  表名
   * @return String
   */
  @Override
  public String dynamicTableName(MetaObject metaObject, String sql, String tableName) {
    try {
      String customTableName = TABLE_NAME.get();
      if (StringUtils.isNotBlank(customTableName)) {
        return customTableName;
      }
    } finally {
      TABLE_NAME.remove();
    }
    return tableName;
  }

  public static ThreadLocal<String> getTableName() {
    return TABLE_NAME;
  }

  public static void setTableName(ThreadLocal<String> tableName) {
    TABLE_NAME = tableName;
  }
}

mybatis-plus 配置， 重点，官方介绍： mybatis-plus do not support TDengine, use postgresql Dialect

/**
 * 实体类
 * 超级表 super_advertiser
 */
@TableName("super_advertiser")
public class Advertiser extends BaseModel<Advertiser> {}

@Bean
  public DynamicTableNameParser dynamicTableNameParser() {
    DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser();
    Map<String, ITableNameHandler> tableNameHandlerMap = new HashMap<>();
    CustomTableNameHandler customTableNameHandler = new CustomTableNameHandler();
    // 超级表
    tableNameHandlerMap.put("super_advertiser", customTableNameHandler);
    dynamicTableNameParser.setTableNameHandlerMap(tableNameHandlerMap);
    return dynamicTableNameParser;
  }

  @Bean
  public PaginationInterceptor paginationInterceptor(DynamicTableNameParser dynamicTableNameParser) {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    List<ISqlParser> sqlParses = new ArrayList<>();
    sqlParses.add(dynamicTableNameParser);
    paginationInterceptor.setSqlParserList(sqlParses);
	//这个是重点，官方介绍： mybatis-plus do not support TDengine, use postgresql Dialect
    paginationInterceptor.setDialectType("postgresql");
    return paginationInterceptor;
  }

BaseTdMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bcwl.springframework.boot.tdengine.domain.base.BaseModel;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;

/**
 * BaseTdMapper
 *
 * @author chentudong
 * @date 2022/12/22 15:12
 * @since 1.0
 */
public interface BaseTdMapper<T> extends BaseMapper<T> {

  /**
   * createTable
   *
   * @param superName superName
   * @param subName   subName
   * @param groupId   groupId
   * @param type      type
   * @return Integer
   */
  @Update("create table if not exists ${subName} using ${superName} tags ( #{groupId}, #{type} )")
  Integer createTable(@Param("superName") String superName, @Param("subName") String subName,
      @Param("groupId") int groupId, @Param("type") int type);

  /**
   * createTable
   *
   * @param baseModel baseModel
   * @return Integer
   */
  @Update("create table if not exists ${subName} using ${superName} tags ( #{groupId}, #{type} )")
  Integer createTable(BaseModel baseModel);

BaseModel

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import java.sql.Timestamp;

/**
 * BaseModel
 *
 * @author chentudong
 * @date 2022/12/22 15:06
 * @since 1.0
 */
public class BaseModel<T extends Model<T>> extends Model<T> {

  /**
   * datetime
   */
  @TableField(value = "datetime", fill = FieldFill.INSERT)
  protected Timestamp datetime;

  /**
   * updateTime
   */
  @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
  protected Timestamp updateTime;

  /**
   * superName
   */
  @TableField(exist = false)
  protected String superName;

  /**subName
   *
   */
  @TableField(exist = false)
  protected String subName;

  /**
   * groupId
   */
  @TableField(exist = false)
  protected int groupId;

  /**
   * type
   */
  @TableField(exist = false)
  protected int type;

BaseServiceImpl

/**
 * BaseServiceImpl
 *
 * @author chentudong
 * @date 2022/12/22 15:17
 * @since 1.0
 */
public class BaseServiceImpl<M extends BaseTdMapper<T>, T extends BaseModel<T>> extends
    ServiceImpl<M, T> implements BaseService<T> {

  @Autowired
  protected M mapper;

  /**
   * save
   *
   * @param entity       entity
   * @param countWrapper countWrapper
   * @return T
   */
  @Transactional(rollbackFor = Exception.class)
  @Override
  public T save(T entity, Wrapper<T> countWrapper) {
    mapper.createTable(entity);
    int count = 0;
    if (Objects.nonNull(countWrapper)) {
      count = super.count(countWrapper);
    }
    if (count == 0) {
      CustomTableNameHandler.getTableName().set(entity.getSubName());
      super.save(entity);
    }
    return entity;
  }

AdvertiserManager 动态保存子表， 关键代码在 mapper.createTable(entity); CustomTableNameHandler.getTableName().set(entity.getSubName());

/**
 * AdvertiserManager
 *
 * @author chentudong
 * @date 2022/12/22 16:49
 * @since 1.0
 */
@Component
public class AdvertiserManager extends BaseServiceImpl<AdvertiserMapper, Advertiser> {
 public void test() {
    Advertiser advertiser = new Advertiser();
    advertiser.setSuperName("super_advertiser");
    advertiser.setSubName("advertiser_172236594346485");
    advertiser.setTenantId("th_ctcc");
    advertiser.setGroupId(1);
    advertiser.setType(1);
    advertiser.setAdvertiserId(172236594346485L);
    advertiser.setAdvertiserName("*黄**");
    save(advertiser, null);
  }
}

最终效果， 分页查询，直接查询超级表， 也可以动态设置表名，查询子表， CustomTableNameHandler.getTableName().set(entity.getSubName());

查询

谢谢观看