如何将JSON数组列映射为非主键关联的Child实体List或Map？

一、映射为List<Child> #

方案1：SQL关联查询+结果映射（推荐，性能更优） #

利用数据库JSON函数拆分child_keys数组，关联child表获取完整实体，再通过ORM框架聚合映射到Parent对象中。

以MySQL为例，查询SQL如下：

SELECT 
    p.id AS parent_id,
    p.child_keys,
    c.id AS child_id,
    c.`key` AS child_key
FROM parent p
LEFT JOIN child c ON JSON_CONTAINS(p.child_keys, CONCAT('"', c.`key`, '"'))
WHERE p.id = ?

MyBatis结果映射配置

<resultMap id="ParentWithChildrenMap" type="Parent">
    <id column="parent_id" property="id"/>
    <result column="child_keys" property="childKeys" jdbcType="JSON"/>
    <collection property="children" ofType="Child">
        <id column="child_id" property="id"/>
        <result column="child_key" property="key"/>
    </collection>
</resultMap>

Spring Data JDBC写法

@Query("SELECT p.id AS parent_id, p.child_keys, c.id AS child_id, c.`key` AS child_key FROM parent p LEFT JOIN child c ON JSON_CONTAINS(p.child_keys, CONCAT('"', c.`key`, '"')) WHERE p.id = :id")
Parent findByIdWithChildren(@Param("id") Long id);

方案2：自定义TypeHandler #

通过自定义类型处理器，读取JSON数组后查询对应Child实体并组装为List：

public class ChildListTypeHandler extends BaseTypeHandler<List<Child>> {

    private final JdbcTemplate jdbcTemplate;

    public ChildListTypeHandler(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<Child> parameter, JdbcType jdbcType) throws SQLException {
        List<String> keys = parameter.stream().map(Child::getKey).collect(Collectors.toList());
        ps.setString(i, new ObjectMapper().writeValueAsString(keys));
    }

    @Override
    public List<Child> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        if (json == null) return Collections.emptyList();
        List<String> keys = new ObjectMapper().readValue(json, new TypeReference<List<String>>() {});
        return jdbcTemplate.query("SELECT * FROM child WHERE `key` IN (:keys)", 
            new MapSqlParameterSource("keys", keys), 
            (rs2, rowNum) -> new Child(rs2.getLong("id"), rs2.getString("key")));
    }

    @Override
    public List<Child> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        // 同上面的解析逻辑
        return Collections.emptyList();
    }

    @Override
    public List<Child> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        // 同上面的解析逻辑
        return Collections.emptyList();
    }
}

实体类配置：

class Parent {
    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "child_keys")
    List<String> childKeys;

    @Column(name = "child_keys")
    @TypeHandler(ChildListTypeHandler.class)
    List<Child> children;
}

二、映射为Map<String, Child> #

方案1：SQL关联查询+结果映射 #

在MyBatis中通过结果映射指定Map的键为Child的key字段：

<resultMap id="ParentWithChildMap" type="Parent">
    <id column="parent_id" property="id"/>
    <result column="child_keys" property="childKeys" jdbcType="JSON"/>
    <collection property="children" ofType="Child" javaType="java.util.Map" keyColumn="child_key" valueColumn="child_id">
        <id column="child_id" property="id"/>
        <result column="child_key" property="key"/>
    </collection>
</resultMap>

方案2：自定义TypeHandler #

修改TypeHandler逻辑，将查询结果组装为Map：

public class ChildMapTypeHandler extends BaseTypeHandler<Map<String, Child>> {

    private final JdbcTemplate jdbcTemplate;

    public ChildMapTypeHandler(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Map<String, Child> parameter, JdbcType jdbcType) throws SQLException {
        List<String> keys = new ArrayList<>(parameter.keySet());
        ps.setString(i, new ObjectMapper().writeValueAsString(keys));
    }

    @Override
    public Map<String, Child> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        if (json == null) return Collections.emptyMap();
        List<String> keys = new ObjectMapper().readValue(json, new TypeReference<List<String>>() {});
        List<Child> children = jdbcTemplate.query("SELECT * FROM child WHERE `key` IN (:keys)", 
            new MapSqlParameterSource("keys", keys), 
            (rs2, rowNum) -> new Child(rs2.getLong("id"), rs2.getString("key")));
        return children.stream().collect(Collectors.toMap(Child::getKey, Function.identity()));
    }

    @Override
    public Map<String, Child> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        // 同上面的解析逻辑
        return Collections.emptyMap();
    }

    @Override
    public Map<String, Child> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        // 同上面的解析逻辑
        return Collections.emptyMap();
    }
}

实体类配置：

class Parent {
    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "child_keys")
    List<String> childKeys;

    @Column(name = "child_keys")
    @TypeHandler(ChildMapTypeHandler.class)
    Map<String, Child> children;
}

注意事项 #

• SQL关联方案性能更优，仅需一次查询；TypeHandler方案会触发额外查询，可能出现N+1问题。
• 不同数据库的JSON函数语法不同，需根据实际数据库调整SQL（比如PostgreSQL用jsonb_array_elements_text）。
• MyBatis需确保TypeHandler注册到配置中；Spring Data JDBC会自动识别自定义TypeHandler。

内容的提问来源于stack exchange，提问作者Jin Kwon