Mapper Design for Maintainability

Good mapper design reduces duplication and eases schema changes and extensions. This article covers naming, SQL reuse, parameter and result encapsulation, and separation of concerns with examples and a reference table.

Overview

• Naming and responsibility: Method names should match SQL semantics (e.g. selectById, selectByUserId, updateStatusById). One Mapper per table or aggregate root; avoid mixing many unrelated tables in one Mapper.
• SQL reuse: Use <sql id="..."> for shared column lists and WHERE fragments, and <include refid="..."> to reference them. Change column names in one place.
• Parameters and results: Use DTOs/VOs or Maps for complex conditions to avoid method explosion. Use ResultMap or DTOs for complex results with explicit mapping.
• Layering: Mappers handle data access only. Build pagination, sort, and filter logic in the Service layer and pass clean parameters to the Mapper.

Example

Example 1: Shared SQL fragments

XMLCopy
<sql id="Order_Columns">
  id, user_id, amount, status, created_at, updated_at
</sql>
<select id="selectById" resultType="Order">
  SELECT <include refid="Order_Columns"/> FROM orders WHERE id = #{id}
</select>
<select id="selectByUser" resultType="Order">
  SELECT <include refid="Order_Columns"/> FROM orders
  <where>
    <if test="userId != null">AND user_id = #{userId}</if>
    <if test="status != null">AND status = #{status}</if>
  </where>
</select>

• Column list is centralized; schema changes are made in one place.

Example 2: Query DTO for conditions

JavaCopy
@Data
public class OrderQuery {
    private Long userId;
    private String status;
    private BigDecimal minAmount;
    private String orderBy;   // Whitelist: id, createdAt, amount
    private String orderDir;  // ASC/DESC
}

List<Order> selectByCondition(OrderQuery q);

• Mapper has a clear, stable signature. Sort and filter are validated (e.g. whitelist) in the service or mapper to avoid ${} injection.

Example 3: One Mapper per table (or aggregate)

• OrderMapper: CRUD and queries for orders. OrderItemMapper: order_item table. Put join queries in OrderMapper (e.g. OrderWithItems) or a dedicated Mapper, but avoid mixing unrelated tables with no clear semantic.
• This helps with caching, permissions, and later microservice splitting by aggregate.

Example 4: Summary

Aspect	Recommendation	Avoid
Naming	selectByXxx, updateXxx; match SQL semantics	Vague names like get, query, find
Reuse	<sql> + <include>	Copy-pasting large blocks
Parameters	DTO/Query object or explicit Map	Many parameters or ad-hoc Map
Results	ResultMap/ResultType aligned with entities; DTO for complex	Scattered AS aliases without ResultMap
Responsibility	Mapper = data access only	Business logic or calling other Mappers

Core Mechanism / Behavior

• <include> — Replaces the reference with the contents of the <sql> at parse time. refid must exist and there must be no circular references.
• Namespace — Each <mapper> namespace maps to a Mapper interface; id maps to method name. Keep namespace aligned with package for easier navigation.

Key Rules

• Extract shared columns and WHERE fragments into <sql> and reference with <include>. Update these when the schema changes.
• Use Query DTOs for complex conditions. Use whitelists or enums for sort and pagination; never pass raw user input into ${}.
• Mappers should only define how to read/write data; what to load, when to invalidate, and caching strategy belong in the Service layer.

What's Next

See Dynamic SQL and Parameter Binding for flexible, safe conditions. See ResultMap vs ResultType and N+1 for result structure and performance.