/mybatis-patterns

name: mybatis-patterns
description: MyBatis and MyBatis-Spring patterns for mapper design, XML and annotation SQL, result mapping, dynamic SQL safety, transactions, batching, pagination, and query performance. Use when building or reviewing Java persistence code with MyBatis, Spring Boot, MyBatis-Spring, or MyBatis-based legacy applications.
metadata:
  origin: ECC

MyBatis Patterns

Use MyBatis when SQL control and explicit mappings matter. Keep SQL readable, make mapper contracts small, and put business transactions in the service layer. Pair this skill with `springboot-patterns` for application structure, `database-migrations` for schema changes, `postgres-patterns` or `mysql-patterns` for engine-specific SQL, and `security-review` for untrusted input and access-control concerns.

When to Activate

• Adding or reviewing MyBatis mapper interfaces or XML files
• Choosing between XML mappers, annotation mappers, and generated SQL
• Designing `resultMap` mappings, joins, nested collections, or projections
• Reviewing dynamic SQL, parameter binding, pagination, or batch writes
• Debugging N+1 queries, slow statements, connection use, or transaction bugs
• Integrating MyBatis-Spring into Spring Boot or an XML-first legacy application

How It Works

Use a thin mapper for persistence operations and keep business rules in a service. A typical flow is:

Controller / batch job -> Service (@Transactional) -> Mapper -> Database

• Register mappers once, either with `@Mapper` on each interface or with

`@MapperScan` for a package. Do not mix registration approaches casually.

• Keep the XML `namespace` equal to the mapper interface's fully qualified name.
• Give each statement a stable, descriptive `id`; treat mapper methods as a

persistence API that can be tested independently.

• Return a domain DTO or projection for read paths instead of exposing a large

mutable map when the shape is known.

• Keep schema changes in migrations. Do not make mapper startup silently create

or alter production tables.

Examples

@MapperScan("com.example.user.persistence")
@Configuration
class MyBatisConfig {
}

public interface UserMapper {
  UserSummary findSummaryById(long userId);
}

<mapper namespace="com.example.user.persistence.UserMapper">
  <select id="findSummaryById"
          parameterType="long"
          resultType="com.example.user.persistence.UserSummary">
    SELECT id, display_name AS displayName, status
    FROM users
    WHERE id = #{userId}
  </select>
</mapper>

Parameter Binding and SQL Safety

Use `#{...}` for values. MyBatis binds it as a prepared-statement parameter. Treat `${...}` as raw SQL text: it is only appropriate for a value selected from a closed, application-owned allowlist such as a sort-column map.

private static final Map<String, String> SORT_COLUMNS = createSortColumns();

private static Map<String, String> createSortColumns() {
    Map<String, String> columns = new HashMap<>();
    columns.put("name", "display_name");
    columns.put("created", "created_at");
    return Collections.unmodifiableMap(columns);
}

String sortColumn = SORT_COLUMNS.getOrDefault(request.sort(), "created_at");
String sortDirection = request.descending() ? "DESC" : "ASC";
// The mapper receives only an allowlisted identifier; user input is never
// passed directly to ${sortColumn} or ${sortDirection}.

<select id="findPage" resultType="com.example.user.persistence.UserSummary">
  SELECT id AS id,
         display_name AS displayName,
         status AS status,
         created_at AS createdAt
  FROM users
  WHERE tenant_id = #{tenantId}
  ORDER BY ${sortColumn} ${sortDirection}
  LIMIT #{limit}
</select>

Derive `tenantId` from the authenticated principal or a server-side tenant context, never from a request field. Authenticate the principal and verify that it is authorized for the tenant before invoking the mapper; keep the `tenant_id` predicate in SQL as defense in depth.

If possible, avoid `${}` entirely by selecting among fixed statements or by using a database-specific query builder. Never interpolate user input into a table name, column name, `ORDER BY`, `WHERE` fragment, or SQL expression.

For multiple parameters, use a request object or explicit `@Param` names:

List<UserSummary> findActive(
    @Param("tenantId") long tenantId,
    @Param("statuses") Set<UserStatus> statuses
);

Do not use the deprecated external `parameterMap` element. Prefer inline parameter mappings and explicit `jdbcType` for nullable values when the JDBC driver needs it.

XML, Result Types, and Result Maps

• Use `resultType` for a simple one-to-one mapping whose column labels already

match the target properties.

• Use `resultMap` when aliases, type handlers, nested objects, or collections

need explicit mapping.

• Mark the identity column with `<id>` in a complex result map. It helps MyBatis

deduplicate nested results and documents the row identity.

• Prefer explicit column lists and aliases over relying on global automapping.
• Keep nested collections bounded. A multi-join can multiply rows and memory

use; for large child collections, fetch the parent page first and load children in a bounded second query.

• Treat nested `select` mappings as a possible N+1 query. Use a join, a batch

query, or an intentional prefetch when the access pattern requires it.

<resultMap id="orderResultMap" type="com.example.order.OrderView">
  <id property="id" column="order_id" />
  <result property="status" column="order_status" />
  <association property="customer" javaType="com.example.customer.CustomerView">
    <id property="id" column="customer_id" />
    <result property="name" column="customer_name" />
  </association>
</resultMap>

Use a type handler for deliberate conversions such as enums, JSON, or vendor types. Test the handler with `NULL`, invalid values, and both read and write paths; do not hide conversio