将多值类型类以键值对形式持久化到SQL表

1. 表结构定义 #

先明确两张表的职责：

• 主表：存储实体的固定核心字段，用id作为主键
• 属性子表：存储实体的多类型动态属性，通过fkId关联主表主键，用字符串类型统一存储不同类型的属性值

主表DDL示例 #

CREATE TABLE main_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    a VARCHAR(255),
    b VARCHAR(255),
    c VARCHAR(255)
);

属性子表DDL示例 #

CREATE TABLE entity_properties (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fkId INT NOT NULL,
    propertyName VARCHAR(50) NOT NULL,
    propertyValue VARCHAR(255) NOT NULL,
    FOREIGN KEY (fkId) REFERENCES main_table(id)
);

2. 持久化逻辑实现（Java示例） #

步骤1：完善实体类 #

class MyClass {
    // 动态属性
    String p1;
    Double p2;
    // 主表核心字段
    String a;
    String b;
    String c;

    // 构造器、getter/setter 自行补充
}

步骤2：插入数据 #

先插入主表获取主键，再批量插入属性子表：

// 1. 插入主表并获取生成的主键
String mainInsertSql = "INSERT INTO main_table(a, b, c) VALUES (?, ?, ?)";
PreparedStatement mainStmt = connection.prepareStatement(mainInsertSql, Statement.RETURN_GENERATED_KEYS);
mainStmt.setString(1, myClass.getA());
mainStmt.setString(2, myClass.getB());
mainStmt.setString(3, myClass.getC());
mainStmt.executeUpdate();

ResultSet rs = mainStmt.getGeneratedKeys();
int fkId = -1;
if (rs.next()) {
    fkId = rs.getInt(1);
}

// 2. 插入属性子表
String propInsertSql = "INSERT INTO entity_properties(fkId, propertyName, propertyValue) VALUES (?, ?, ?)";
PreparedStatement propStmt = connection.prepareStatement(propInsertSql);

// 处理String类型属性p1
propStmt.setInt(1, fkId);
propStmt.setString(2, "p1");
propStmt.setString(3, myClass.getP1());
propStmt.executeUpdate();

// 处理Double类型属性p2，转成字符串存储
propStmt.setInt(1, fkId);
propStmt.setString(2, "p2");
propStmt.setString(3, String.valueOf(myClass.getP2()));
propStmt.executeUpdate();

步骤3：查询数据 #

通过主表ID关联子表，将属性映射回实体类：

// 1. 查询主表核心数据
String mainQuerySql = "SELECT a, b, c FROM main_table WHERE id = ?";
PreparedStatement mainQueryStmt = connection.prepareStatement(mainQuerySql);
mainQueryStmt.setInt(1, targetId);
ResultSet mainRs = mainQueryStmt.executeQuery();

MyClass myClass = new MyClass();
if (mainRs.next()) {
    myClass.setA(mainRs.getString("a"));
    myClass.setB(mainRs.getString("b"));
    myClass.setC(mainRs.getString("c"));
}

// 2. 查询属性子表并映射回实体
String propQuerySql = "SELECT propertyName, propertyValue FROM entity_properties WHERE fkId = ?";
PreparedStatement propQueryStmt = connection.prepareStatement(propQuerySql);
propQueryStmt.setInt(1, targetId);
ResultSet propRs = propQueryStmt.executeQuery();

while (propRs.next()) {
    String propName = propRs.getString("propertyName");
    String propValue = propRs.getString("propertyValue");
    switch (propName) {
        case "p1":
            myClass.setP1(propValue);
            break;
        case "p2":
            myClass.setP2(Double.valueOf(propValue));
            break;
    }
}

3. 关键注意事项 #

• 类型转换：所有属性统一转字符串存储，查询时需转回对应类型，注意处理空值和转换异常
• 索引优化：给属性子表的fkId和propertyName建立联合索引，提升关联查询效率
• 事务控制：主表与子表的增删改操作必须放在同一事务中，避免数据不一致

内容的提问来源于stack exchange，提问作者Abhinav Mani