MyBatis注解实现单查询获取复杂对象的最优方案咨询

Got it, let's break this down for you. I’ve run into exactly this nested object mapping issue with MyBatis before, so I’ll walk you through the best solutions—including the TypeHandler approach you’re curious about, plus a more straightforward alternative that’s usually the better choice.

This is the cleanest, most maintainable way to map nested objects in a single SQL query, no extra TypeHandler code required. Here’s how to implement it step by step:

1. First, let’s assume your entity classes look like this (adjust to your actual structure): #

public class AttachmentHierarchy {
    private Long id;
    private String hierarchyName;
    private Attachment attachment; // Your nested Attachment object
    
    // Getters and setters omitted for brevity
}

public class Attachment {
    private Long attachId;
    private String fileName;
    private String fileUrl;
    
    // Getters and setters omitted for brevity
}

2. Write a single SQL query that joins both tables and selects all needed fields #

You’ll fetch all columns for both AttachmentHierarchy and Attachment in one go:

SELECT 
    ah.id, ah.hierarchy_name,
    a.attach_id, a.file_name, a.file_url
FROM attachment_hierarchy ah
LEFT JOIN attachment a ON ah.attach_id = a.attach_id
WHERE ah.id = #{id}

3. Define nested ResultMaps to map columns to your objects #

Use MyBatis’s <association> tag to link the nested Attachment object to its parent AttachmentHierarchy. This tells MyBatis how to map the joined columns to the nested class without triggering extra queries.

If you’re using XML Mappers:

<!-- ResultMap for the nested Attachment object -->
<resultMap id="AttachmentResultMap" type="com.yourpackage.Attachment">
    <id column="attach_id" property="attachId"/>
    <result column="file_name" property="fileName"/>
    <result column="file_url" property="fileUrl"/>
</resultMap>

<!-- ResultMap for AttachmentHierarchy, linking to the Attachment ResultMap -->
<resultMap id="AttachmentHierarchyResultMap" type="com.yourpackage.AttachmentHierarchy">
    <id column="id" property="id"/>
    <result column="hierarchy_name" property="hierarchyName"/>
    <!-- Map the nested Attachment using the pre-defined ResultMap -->
    <association property="attachment" resultMap="AttachmentResultMap"/>
</resultMap>

If you prefer annotation-based Mappers:

@Select("SELECT ah.id, ah.hierarchy_name, a.attach_id, a.file_name, a.file_url FROM attachment_hierarchy ah LEFT JOIN attachment a ON ah.attach_id = a.attach_id WHERE ah.id = #{id}")
@Results({
    @Result(column = "id", property = "id"),
    @Result(column = "hierarchy_name", property = "hierarchyName"),
    // Use @Result with a nested ResultMap for the Attachment object
    @Result(property = "attachment", resultMap = "com.yourpackage.AttachmentMapper.AttachmentResultMap")
})
AttachmentHierarchy getAttachmentHierarchyById(Long id);

// In your AttachmentMapper, define the nested ResultMap
@ResultMap("AttachmentResultMap")
@Results({
    @Result(column = "attach_id", property = "attachId"),
    @Result(column = "file_name", property = "fileName"),
    @Result(column = "file_url", property = "fileUrl")
})
Attachment getAttachmentById(Long id);

This approach runs exactly one SQL query and maps all columns directly to your nested objects—no N+1 issues, no extra conversion logic. It’s the standard MyBatis way to handle nested associations in single queries.

TypeHandlers are better suited for converting a single column value to an object, but you can adapt them for multi-column mapping by first concatenating the nested object’s fields into a single column (like JSON) in your SQL. Here’s how:

1. Modify your SQL to serialize Attachment fields into a JSON string #

Most databases support JSON functions (e.g., MySQL’s JSON_OBJECT, PostgreSQL’s json_build_object):

SELECT 
    ah.id, ah.hierarchy_name,
    JSON_OBJECT(
        'attachId', a.attach_id,
        'fileName', a.file_name,
        'fileUrl', a.file_url
    ) AS attachment_json
FROM attachment_hierarchy ah
LEFT JOIN attachment a ON ah.attach_id = a.attach_id
WHERE ah.id = #{id}

2. Create a custom TypeHandler to parse the JSON string into an Attachment object #

Use a JSON library like Jackson to handle the serialization/deserialization:

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AttachmentTypeHandler extends BaseTypeHandler<Attachment> {
    private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();

    @Override
    public void setParameter(PreparedStatement ps, int i, Attachment attachment, JdbcType jdbcType) throws SQLException {
        if (attachment == null) {
            ps.setString(i, null);
            return;
        }
        try {
            ps.setString(i, OBJECT_MAPPER.writeValueAsString(attachment));
        } catch (Exception e) {
            throw new SQLException("Failed to convert Attachment to JSON", e);
        }
    }

    @Override
    public Attachment getResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        return parseJson(json);
    }

    @Override
    public Attachment getResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        return parseJson(json);
    }

    @Override
    public Attachment getResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        return parseJson(json);
    }

    private Attachment parseJson(String json) throws SQLException {
        if (json == null || json.isEmpty()) {
            return null;
        }
        try {
            return OBJECT_MAPPER.readValue(json, Attachment.class);
        } catch (Exception e) {
            throw new SQLException("Failed to parse JSON to Attachment", e);
        }
    }
}

3. Use the TypeHandler in your @Result mapping #

With annotations:

@Select("SELECT ah.id, ah.hierarchy_name, JSON_OBJECT('attachId', a.attach_id, 'fileName', a.file_name, 'fileUrl', a.file_url) AS attachment_json FROM attachment_hierarchy ah LEFT JOIN attachment a ON ah.attach_id = a.attach_id WHERE ah.id = #{id}")
@Results({
    @Result(column = "id", property = "id"),
    @Result(column = "hierarchy_name", property = "hierarchyName"),
    @Result(
        column = "attachment_json",
        property = "attachment",
        typeHandler = AttachmentTypeHandler.class
    )
})
AttachmentHierarchy getAttachmentHierarchyById(Long id);

With XML:

<resultMap id="AttachmentHierarchyTypeHandlerMap" type="com.yourpackage.AttachmentHierarchy">
    <id column="id" property="id"/>
    <result column="hierarchy_name" property="hierarchyName"/>
    <result column="attachment_json" property="attachment" typeHandler="com.yourpackage.AttachmentTypeHandler"/>
</resultMap>

Stick with the nested ResultMap + association approach. It’s simpler, avoids the overhead of JSON serialization/deserialization, and is the idiomatic way to handle nested objects in MyBatis for single queries. The TypeHandler method is useful only if you have a specific requirement that forces you to map multi-column data through a single column (which is rare in this scenario).

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