Logo

Menu

梁华东
Published on 2025-08-19 / 14 Visits
1
0

关系型数据库扩展字段方案设计

关系型数据库扩展字段方案设计

一、 概述

设计方案（Spring Boot + MyBatis-Flex + JSON 扩展 + 无代码表单）

二、 数据库设计

todo 删除列、新增同名列

1、 业务表（ext_attrs 存储扩展字段）

//mysql 5.7+
CREATE TABLE customer (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(50),
    ext_attrs JSON COMMENT '扩展字段存储JSON',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

//oracle 19c及以上
CREATE TABLE customer (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(50),
    ext_attrs JSON, -- 21c+ 原生 JSON  
    ext_attrs CLOB CHECK (ext_attrs IS JSON), -- CLOB 存 JSON 并加 JSON 约束  Oracle 12c/19c（没有 JSON 类型）
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

//pg 
CREATE TABLE customer (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(50),
    ext_attrs JSONB, -- 推荐 JSONB 存储
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2、 元数据表（ext_field_meta）

CREATE TABLE ext_field_meta (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  
    -- 绑定到哪个业务表的哪个 JSON 列
    table_name VARCHAR(100) NOT NULL COMMENT '业务表名',
    column_name VARCHAR(100) NOT NULL COMMENT 'JSON列名',
  
    -- JSON key 和显示属性
    field_key VARCHAR(100) NOT NULL COMMENT '业务字段Key（JSON里的Key，唯一标识）',
    field_label VARCHAR(100) NOT NULL COMMENT '显示标签名（中文名/展示名）',
    field_placeholder VARCHAR(200) COMMENT '提示语 / 占位符',
  
    -- 字段属性
    field_type VARCHAR(50) NOT NULL COMMENT '字段类型：STRING, LONG, DOUBLE, BOOLEAN, DATETIME, ENUM, MULTI_ENUM',
    field_options TEXT COMMENT '可选值(JSON数组: ["选项1","选项2"])',
    field_default_value VARCHAR(255) COMMENT '默认值',
  
    -- 校验属性
    required TINYINT(1) DEFAULT 0 COMMENT '是否必填（0否，1是）',
    max_length INT COMMENT '最大长度（文本类型适用）',
    precision INT COMMENT '精度（浮点类型适用）',
  
    -- 界面控制
    sort_order INT DEFAULT 0 COMMENT '字段排序',
    is_visible TINYINT(1) DEFAULT 1 COMMENT '是否显示（0否，1是）',

  
    status       TINYINT(1) DEFAULT 1 COMMENT     '1=启用, 0=禁用',
  
    -- 审计 & 扩展
    remark VARCHAR(255) COMMENT '备注说明',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

    UNIQUE KEY uq_meta (table_name, column_name, field_key)
) COMMENT='JSON 扩展字段元数据定义表';

三、 元数据管理

1、 UI效果

2、 DTO 返回结构

@Data
public class MetaTreeDTO {
    private String name;               // 分组名
    private List<FieldDTO> children;   // 扩展字段
}

@Data
public class FieldDTO {
    private String name;               // 中文名
    private String nameEn;             // 英文key
    private BusinessObject businessObject;
}

@Data
public class BusinessObject {
    private String constraint;         // JSON字符串（存储约束信息）
    private boolean disableFlag;
    private String type;               // STRING, BOOLEAN, NUMBER...
}

@RestController
@RequestMapping("/api/json-field-meta")
@RequiredArgsConstructor
public class MetadataController {

    private final JsonFieldMetaService metadataService;
  
    //增、删、改等接口

  /**
     * 获取所有表的扩展信息树
     */
    @GetMapping("/tree")
    public ResponseEntity<Map<String, List<MetaTreeDTO>>> getAllMetaTree() {
        return ResponseEntity.ok(metaService.buildAllMetaResponse());
    }

}

3、 JsonFieldMetaService 实现

@Service
public class JsonFieldMetaService {

    @Autowired
    private JsonFieldMetaMapper metaMapper; // MyBatis-Flex Mapper

    // 可以加上缓存（如 Caffeine / Redis）
    private final Map<String, JsonFieldMeta> cache = new ConcurrentHashMap<>();

    /**
     * 根据 tableName + fieldKey 获取 QueryColumn
     */
    public JsonExtractColumn buildColumn(String tableName, String fieldKey) {
        JsonFieldMeta meta = findMeta(tableName, fieldKey);
        if (meta == null) {
            throw new IllegalArgumentException("未找到字段定义: " + tableName + "." + fieldKey);
        }
        return new JsonExtractColumn(meta.getColumnName(), meta.getJsonPath()).as(fieldKey);
    }

    /**
     * 获取元数据定义
     */
    private JsonFieldMeta findMeta(String tableName, String fieldKey) {
        String key = tableName + ":" + fieldKey;
        return cache.computeIfAbsent(key, k -> 
            metaMapper.selectOneByCondition(
                QueryWrapper.create()
                    .where(JsonFieldMetaTableDef.JSON_FIELD_META.TABLE_NAME.eq(tableName))
                    .and(JsonFieldMetaTableDef.JSON_FIELD_META.FIELD_KEY.eq(fieldKey))
            )
        );
    }

    /**
     * 刷新缓存（可在后台管理配置变更时调用）
     */
    public void clearCache(String tableName, String fieldKey) {
        cache.remove(tableName + ":" + fieldKey);
    }
  
  
  
    //--------------------------------------
  
     /**
     * 按表名构建单表的扩展属性树
     */
    public Map<String, List<MetaTreeDTO>> buildMetaResponse(String tableName) {
        List<JsonFieldMeta> metas = metaMapper.selectListByTable(tableName);
        return Collections.singletonMap(tableName, buildTree(tableName, metas));
    }

    /**
     * 获取所有表的扩展属性树
     */
    public Map<String, List<MetaTreeDTO>> buildAllMetaResponse() {
        List<JsonFieldMeta> metas = metaMapper.selectAll();

        // 按表名分组
        Map<String, List<JsonFieldMeta>> grouped = metas.stream()
                .collect(Collectors.groupingBy(JsonFieldMeta::getTableName));

        // 构建返回结果
        Map<String, List<MetaTreeDTO>> result = new HashMap<>();
        grouped.forEach((tableName, metaList) -> {
            result.put(tableName, buildTree(tableName, metaList));
        });

        return result;
    }

    private List<MetaTreeDTO> buildTree(String tableName, List<JsonFieldMeta> metas) {
        List<FieldDTO> children = metas.stream().map(this::toFieldDTO).toList();

        MetaTreeDTO tree = new MetaTreeDTO();
        tree.setName(tableName + "_扩展属性");
        tree.setChildren(children);

        return Collections.singletonList(tree);
    }

    private FieldDTO toFieldDTO(JsonFieldMeta meta) {
        FieldDTO field = new FieldDTO();
        field.setName(meta.getFieldLabel());
        field.setNameEn(meta.getFieldKey());

        BusinessObject bo = new BusinessObject();
        bo.setType(meta.getFieldType().toUpperCase());
        bo.setDisableFlag(false);

        // 构造 constraint JSON
        Map<String, Object> constraint = new LinkedHashMap<>();
        constraint.put("associationType", "STRONG");
        constraint.put("caseMode", "DEFAULT");
        constraint.put("compose", false);
        constraint.put("encryption", false);
        constraint.put("graphIndex", false);
        constraint.put("index", false);
        constraint.put("legalValueType", "");
        constraint.put("length", meta.getUiType().equals("input") ? 100 : 0);
        constraint.put("multiValue", "checkbox".equals(meta.getUiType()));
        constraint.put("notnull", meta.getRequired() == 1);
        constraint.put("optionalValue", meta.getDictCode() != null ? meta.getDictCode() : "LEGAL_VALUE_TYPE");
        constraint.put("precision", 0);
        constraint.put("secretLevel", "internal");
        constraint.put("stockInDB", true);
        constraint.put("variable", true);

        try {
            bo.setConstraint(new ObjectMapper().writeValueAsString(constraint));
        } catch (JsonProcessingException e) {
            bo.setConstraint("{}");
        }

        field.setBusinessObject(bo);
        return field;
    }
  
}

4、 返回（示例）

{
  "SieMesPackageRule": [
    {
      "name": "SieMesPackageRule_扩展属性",
      "children": [
        {
          "name": "aa",
          "nameEn": "a",
          "businessObject": {
            "constraint": "{...}",
            "disableFlag": false,
            "type": "STRING"
          }
        },
        {
          "name": "是否混BIN",
          "nameEn": "isMixedBIN",
          "businessObject": {
            "constraint": "{...}",
            "disableFlag": false,
            "type": "BOOLEAN"
          }
        }
      ]
    }
  ],
  "Customer": [
    {
      "name": "Customer_扩展属性",
      "children": [
        ...
      ]
    }
  ]
}

5、 JsonExtractColumn

public class JsonExtractColumn extends QueryColumn {

    private final String columnName;   // JSON 列名
    private final String jsonPath;     // JSON 路径表达式

    public JsonExtractColumn(String columnName, String jsonPath) {
        super(buildSql(columnName, jsonPath));
        this.columnName = columnName;
        this.jsonPath = jsonPath;
    }

    private static String buildSql(String columnName, String jsonPath) {
        String dbType = DbTypeHelper.getDbType();

        if ("mysql".equalsIgnoreCase(dbType)) {
            // MySQL: JSON_UNQUOTE(JSON_EXTRACT(col, '$.path'))
            return "JSON_UNQUOTE(JSON_EXTRACT(" + columnName + ", '" + jsonPath + "'))";
        } 
        else if ("postgresql".equalsIgnoreCase(dbType)) {
            // PostgreSQL: col #>> '{path1,path2}'
            return columnName + " #>> '{" + jsonPath.replace("$.", "").replace(".", ",") + "}'";
        } 
        else if ("oracle".equalsIgnoreCase(dbType)) {
            // Oracle: JSON_VALUE(col, '$.path')
            return "JSON_VALUE(" + columnName + ", '" + jsonPath + "')";
        } 
        else {
            throw new UnsupportedOperationException("暂不支持该数据库类型: " + dbType);
        }
    }
}

6、 模型选择（表）

MyBatis-Flex 在运行时维护了 表和实体的映射，可以直接拿到。

import com.mybatisflex.core.table.TableInfo;
import com.mybatisflex.core.table.TableInfoFactory;

public class FlexTableScanner {
    public static void main(String[] args) {
   

        // 如果你想获取所有注册过的实体表：
        TableInfoFactory.getTableInfos().forEach((cls, info) -> {
            System.out.println(cls.getSimpleName() + " -> " + info.getTableName());
        });
    }
}

四、 后端设计

1、 实体类

a) 定义父类


public class BaseExtAttrs implements Serializable {
    private String name;
    private String value;

}

import java.util.HashMap;
import java.util.Map;
@Data
public class BaseEntity {
    /** 扩展字段 */
   @Column("ext_attrs")
    private List<BaseExtAttrs> extAttrs; // 扩展字段映射
}

b) 定义业务类

@Data
@Table("customer")
public class Customer  extends BaseEntity{
    private Long id;
    private String name;
    private String email;
    private String phone;
}

2、 新增数据

a) 定义父类DTO

import java.util.HashMap;
import java.util.Map;
@Data
public class BaseDTO {

    /** 扩展字段 */
   @Column("ext_attrs")
  private List<BaseExtAttrs> extAttrs; // 扩展字段映射
  
}

b) 定义保存入参

@Data
public class CustomerSaveDTO extends  BaseDTO {
    private String name;
    private String email;
    private String phone;
}

这样前端可以提交如下 JSON：

{
  "name": "张三",
  "email": "zhangsan@example.com",
  "phone": "13800000000",
  "extAttrs": [{
    "name": "customerLevel",
    "value": "VIP"
  }]
}

3、 Controller 接收 DTO

@RestController
@RequestMapping("/customer")
public class CustomerController {

    @Autowired
    private CustomerMapper customerMapper;

    @PostMapping("/save")
    public Result save(@RequestBody CustomerSaveDTO dto){
    //放到seervice里的，为了方便说明写这里。
        Customer customer = new Customer();
        customer.setName(dto.getName());
        customer.setEmail(dto.getEmail());
        customer.setPhone(dto.getPhone());
        customer.setExtArrts(dto.getExtAttrs()); // 扩展字段
        customerMapper.insert(customer);
        return Result.ok();
    }
}

4、 查询数据

a) 定义 Query DTO

import lombok.Data;
import java.util.Map;

@Data
public class CustomerQueryDTO extends  BaseDTO {

    // ===== 普通字段 =====
    private Long id;
    private String name;
    private String email;
    private String phone;
}

b) Controller 查询接口

@PostMapping("/query")
public List<Customer> query(@RequestBody CustomerQueryDTO dto) {
    QueryWrapper query = QueryWrapper.create().from("customer");

    // 普通字段
    if (dto.getId() != null) {
        query.and(CustomerTableDef.CUSTOMER.ID.eq(dto.getId()));
    }
    if (dto.getName() != null) {
        query.and(CustomerTableDef.CUSTOMER.NAME.eq(dto.getName()));
    }
    if (dto.getEmail() != null) {
        query.and(CustomerTableDef.CUSTOMER.EMAIL.eq(dto.getEmail()));
    }
    if (dto.getPhone() != null) {
        query.and(CustomerTableDef.CUSTOMER.PHONE.eq(dto.getPhone()));
    }
    // 扩展字段
    if (dto.getExtAttrs() != null) {
        dto.getExtAttrs().forEach(item -> {
            JsonExtractColumn col = metaService.buildColumn(CustomerTableDef.CUSTOMER, item.name);
            query.and(col.eq(item.value));
        });
    }
    return customerMapper.selectListByQuery(query);
}

c) 前端请求示例

{
  "name": "张三",
  "extAttrs": [{
    "name": "customerLevel",
    "value": "VIP"
  },
    "name": "company",
    "value": "华为"
  }]
}

d) 生成 SQL (MySQL 场景)

SELECT *
FROM customer
WHERE name = '张三'
  AND JSON_UNQUOTE(JSON_EXTRACT(ext_attrs, '$.company')) = '华为'
  AND JSON_UNQUOTE(JSON_EXTRACT(ext_attrs, '$.level')) = 'VIP';
Kubernetes Master...
自动化部署使用文档

Comment