Series/Learn Java Data Access Pattern In Action

Deepen PracticeOrdered learning track

MyBatis Mapper Pattern

Learn Java Data Access Pattern In Action - Part 047

MyBatis mapper pattern untuk Java production: XML vs annotation, mapper interface, parameter object, dynamic SQL, resultMap, nested mapping, type handler, transaction integration, SQL ownership, dan testing.

[2026-07-05]10 min read1968 words

In This Lesson

1. Core Thesis 2. MyBatis Position 3. Mapper Interface

PrevNext

Lesson 4760 lesson track34–50 Deepen Practice

#java#data-access#mybatis#mapper+5 more

Part 047 — MyBatis Mapper Pattern

MyBatis berada di antara raw JDBC dan ORM.

Ia tidak mencoba membuat database menjadi object graph otomatis seperti Hibernate.

Ia juga tidak memberi DSL type-safe seperti jOOQ.

MyBatis memberi pola:
SQL eksplisit + mapper interface + parameter binding + result mapping.
Ini cocok untuk tim yang ingin SQL tetap visible, tetapi tetap ingin menghindari boilerplate JDBC manual.

Part ini membahas MyBatis Mapper Pattern untuk production-grade Java data access.

1. Core Thesis

MyBatis adalah framework mapper SQL.

Ia cocok ketika kamu ingin:

SQL ditulis eksplisit;
mapping ResultSet ke object dikelola framework;
dynamic SQL lebih terstruktur daripada string builder manual;
XML query bisa direview;
annotation query untuk kasus sederhana;
transaction tetap dikelola Spring/Jakarta;
stored procedure/native SQL mudah dipanggil;
ORM hidden behavior dihindari.

MyBatis bukan ORM Unit of Work.

Tidak ada dirty checking otomatis, tidak ada lazy loading entity graph sebagai default mental model, dan tidak ada automatic aggregate persistence. Kamu tetap mendesain DAO/repository/query service.

2. MyBatis Position

Tool	Mental Model
JDBC	manual SQL, manual mapping, manual resource handling
MyBatis	explicit SQL + mapper + result mapping
jOOQ	generated schema + type-safe SQL DSL
JPA/Hibernate	entity/persistence context/dirty checking
Spring Data JPA	repository abstraction over JPA

MyBatis biasanya fit untuk:

SQL-first project;
legacy database;
stored procedure;
complex query with hand-written SQL;
team comfortable reviewing XML SQL;
systems needing predictable SQL without ORM graph behavior.

3. Mapper Interface

public interface CaseFileMapper {
    Optional<CaseFileRow> findByTenantAndId(
            @Param("tenantId") UUID tenantId,
            @Param("caseId") UUID caseId
    );

    int updateStatusWithVersion(CaseStatusUpdateRow row);

    int insert(CaseFileInsertRow row);
}

Mapper interface adalah contract Java.

SQL bisa diletakkan di:

XML mapper;
annotation;
MyBatis Dynamic SQL;
provider class.

Untuk production complex SQL, XML sering lebih reviewable daripada annotation panjang.

4. XML Mapper Basic

<mapper namespace="com.example.data.CaseFileMapper">

  <select id="findByTenantAndId" resultMap="CaseFileRowMap">
    select
      id,
      tenant_id,
      case_number,
      status,
      version,
      updated_at
    from case_file
    where tenant_id = #{tenantId}
      and id = #{caseId}
  </select>

</mapper>

Parameter binding:

#{tenantId}

binds safely through prepared statement.

Avoid raw substitution:

${tenantId}

except for server-side whitelisted fragments like sort SQL.

5. `#{}` vs `${}`

#{}:

where status = #{status}

Safe parameter binding.

${}:

order by ${sortSql}

Raw string substitution.

Use ${} only when:

value is not user input;
value comes from whitelist enum;
impossible to bind as parameter, such as column/order keyword;
SQL injection risk reviewed.

Never:

where status = '${request.status}'

6. Result Map

<resultMap id="CaseFileRowMap" type="com.example.data.CaseFileRow">
  <constructor>
    <arg column="id" javaType="java.util.UUID"/>
    <arg column="tenant_id" javaType="java.util.UUID"/>
    <arg column="case_number" javaType="java.lang.String"/>
    <arg column="status" javaType="java.lang.String"/>
    <arg column="version" javaType="long"/>
    <arg column="updated_at" javaType="java.time.Instant"/>
  </constructor>
</resultMap>

Constructor mapping is good for immutable row DTO.

If mapping to Java records, ensure MyBatis version/config supports constructor/record mapping as expected. Test it.

7. Row DTO

public record CaseFileRow(
        UUID id,
        UUID tenantId,
        String caseNumber,
        String status,
        long version,
        Instant updatedAt
) {}

Data access layer maps row DTO to domain:

public CaseFile toDomain(CaseFileRow row) {
    return CaseFile.rehydrate(
            new CaseFileId(row.id()),
            new TenantId(row.tenantId()),
            new CaseNumber(row.caseNumber()),
            CaseStatus.fromDbCode(row.status()),
            row.version(),
            row.updatedAt()
    );
}

Do not make MyBatis row DTO your domain object if domain behavior is complex.

8. TypeHandler

TypeHandler maps between JDBC type and Java type.

Example for CaseStatus:

public final class CaseStatusTypeHandler extends BaseTypeHandler<CaseStatus> {
    @Override
    public void setNonNullParameter(
            PreparedStatement ps,
            int i,
            CaseStatus parameter,
            JdbcType jdbcType
    ) throws SQLException {
        ps.setString(i, parameter.dbCode());
    }

    @Override
    public CaseStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String value = rs.getString(columnName);
        return value == null ? null : CaseStatus.fromDbCode(value);
    }

    @Override
    public CaseStatus getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String value = rs.getString(columnIndex);
        return value == null ? null : CaseStatus.fromDbCode(value);
    }

    @Override
    public CaseStatus getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String value = cs.getString(columnIndex);
        return value == null ? null : CaseStatus.fromDbCode(value);
    }
}

Use TypeHandler for repeated enum/value mappings.

Test unknown code behavior.

9. TypeHandler Registration

Registration options:

MyBatis config XML;
Spring Boot MyBatis configuration;
annotation;
package scanning.

Example concept:

mybatis:
  type-handlers-package: com.example.data.mybatis.typehandler

Keep TypeHandler registration visible and tested.

If TypeHandler missing, mapping can degrade to string/object errors at runtime.

10. Parameter Object

Avoid long parameter list.

Bad:

List<Row> search(
    UUID tenantId,
    String status,
    String priority,
    UUID officerId,
    Integer limit,
    Integer offset
);

Better:

List<CaseDashboardRow> search(CaseDashboardQuery query);

Query object validates:

tenant;
scope;
status/priority;
date range;
sort enum;
page limit.

Mapper XML references fields:

where tenant_id = #{tenantId}

depending property names/getters.

11. Dynamic SQL With XML

<select id="search" resultMap="CaseDashboardRowMap">
  select
    c.id,
    c.case_number,
    c.status,
    c.priority,
    o.display_name as assigned_officer_name,
    c.updated_at
  from case_file c
  left join officer o on o.id = c.assigned_officer_id
  where c.tenant_id = #{tenantId}

  <if test="status != null">
    and c.status = #{status}
  </if>

  <if test="priority != null">
    and c.priority = #{priority}
  </if>

  <if test="assignedOfficerId != null">
    and c.assigned_officer_id = #{assignedOfficerId}
  </if>

  order by ${sortSql}
  limit #{limit}
</select>

sortSql must come from whitelist enum, not request string.

12. Whitelisted Sort

Java enum:

public enum CaseSort {
    UPDATED_DESC("c.updated_at desc, c.id desc"),
    PRIORITY_DESC("c.priority desc, c.updated_at desc, c.id desc"),
    CASE_NUMBER_ASC("c.case_number asc, c.id asc");

    private final String sql;

    public String sql() {
        return sql;
    }
}

Query object exposes:

public String sortSql() {
    return sort.sql();
}

XML:

order by ${sortSql}

This is acceptable only because sortSql is server-defined.

13. Dynamic WHERE With `<where>`

<select id="search" resultMap="CaseDashboardRowMap">
  select ...
  from case_file c
  <where>
    c.tenant_id = #{tenantId}

    <if test="status != null">
      and c.status = #{status}
    </if>

    <if test="priority != null">
      and c.priority = #{priority}
    </if>
  </where>
  order by ${sortSql}
  limit #{limit}
</select>

<where> helps trim leading and.

Still keep tenant condition required.

14. Dynamic SET With `<set>`

<update id="updateMutableFields">
  update case_file
  <set>
    <if test="title != null">
      title = #{title},
    </if>
    <if test="priority != null">
      priority = #{priority},
    </if>
    updated_at = #{updatedAt},
    version = version + 1
  </set>
  where id = #{caseId}
    and version = #{expectedVersion}
</update>

Use carefully.

For domain commands, explicit update method per intent is often clearer than generic dynamic patch.

15. `<foreach>` for IN

<select id="findByIds" resultMap="CaseFileRowMap">
  select ...
  from case_file
  where tenant_id = #{tenantId}
    and id in
    <foreach collection="ids" item="id" open="(" separator="," close=")">
      #{id}
    </foreach>
</select>

Handle empty list before calling mapper.

Cap size to avoid huge IN.

For very large list, use temp/staging table or chunk.

16. `<choose>`

<choose>
  <when test="assignmentFilter == 'UNASSIGNED'">
    and c.assigned_officer_id is null
  </when>
  <when test="assignmentFilter == 'ASSIGNED_TO'">
    and c.assigned_officer_id = #{assignedOfficerId}
  </when>
  <otherwise>
    <!-- any assignment -->
  </otherwise>
</choose>

Good for sealed/filter variants.

Avoid vague nullable flags.

17. SQL Fragments

<sql id="CaseDashboardColumns">
  c.id,
  c.case_number,
  c.status,
  c.priority,
  o.display_name as assigned_officer_name,
  c.updated_at
</sql>

<select id="search" resultMap="CaseDashboardRowMap">
  select
    <include refid="CaseDashboardColumns"/>
  from ...
</select>

Useful for repeated column lists.

But don't overuse fragments until query becomes hard to understand.

18. Result Map for Projection

<resultMap id="CaseDashboardRowMap" type="com.example.query.CaseDashboardRow">
  <constructor>
    <arg column="id" javaType="java.util.UUID"/>
    <arg column="case_number" javaType="java.lang.String"/>
    <arg column="status" javaType="com.example.domain.CaseStatus" typeHandler="CaseStatusTypeHandler"/>
    <arg column="priority" javaType="com.example.domain.Priority" typeHandler="PriorityTypeHandler"/>
    <arg column="assigned_officer_name" javaType="java.lang.String"/>
    <arg column="updated_at" javaType="java.time.Instant"/>
  </constructor>
</resultMap>

For nullable assigned_officer_name, DTO constructor should allow null or mapper wraps later.

19. Nested Mapping Caveat

MyBatis can map nested objects/collections.

<collection property="assignments" ofType="CaseAssignmentRow">
  ...
</collection>

Caution:

one-to-many join duplicates parent rows;
multiple collections cause cartesian explosion;
memory mapping complexity;
query may be hard to paginate;
resultMap can hide heavy query.

For complex detail view, separate section queries are often clearer.

20. Nested Select N+1 Risk

MyBatis supports nested select.

<collection property="assignments"
            select="findAssignmentsByCaseId"
            column="id"/>

This can create N+1:

1 query parent
N queries child

Use with caution.

Prefer join for bounded single parent, or batch/separate query for page.

21. Detail View Section Queries

public CaseDetailView getDetail(CaseFileId id) {
    CaseHeaderRow header = caseMapper.findHeader(id).orElseThrow();
    List<AssignmentRow> assignments = assignmentMapper.findActiveByCaseId(id);
    List<DocumentRow> documents = documentMapper.findByCaseId(id);
    List<ActionRow> actions = actionMapper.findRecentByCaseId(id, 20);

    return mapper.toView(header, assignments, documents, actions);
}

This is explicit and avoids cartesian explosion.

22. Mapper XML Naming

Use query names with intent:

<select id="findHeaderByTenantAndId">
<select id="searchDashboardRows">
<select id="readExportRowsAfter">
<update id="approveWithExpectedVersion">
<insert id="insertAuditRecord">

Avoid generic:

<select id="select">
<update id="update">

Names appear in stack traces/logging and help reviews.

23. Update Count

Mapper update returns int.

int updated = caseFileMapper.approveWithExpectedVersion(row);

if (updated == 0) {
    throw new OptimisticConflict(row.caseId(), row.expectedVersion());
}

if (updated != 1) {
    throw new DataAccessInvariantViolation("Expected 1 row updated, got " + updated);
}

Do not ignore int result.

24. Conditional Update XML

<update id="approveWithExpectedVersion">
  update case_file
  set
    status = 'APPROVED',
    approved_at = #{approvedAt},
    approved_by = #{approvedBy},
    version = version + 1,
    updated_at = #{approvedAt}
  where tenant_id = #{tenantId}
    and id = #{caseId}
    and status = 'UNDER_REVIEW'
    and version = #{expectedVersion}
</update>

This expresses state transition atomically.

25. Insert XML

<insert id="insert">
  insert into case_file (
    id,
    tenant_id,
    case_number,
    status,
    version,
    created_at,
    updated_at
  ) values (
    #{id},
    #{tenantId},
    #{caseNumber},
    #{status},
    0,
    #{createdAt},
    #{createdAt}
  )
</insert>

If using app-generated ID, no generated key needed.

Map duplicate constraint to semantic exception.

26. Generated Keys

<insert id="insert" useGeneratedKeys="true" keyProperty="id">
  insert into case_file (
    tenant_id,
    case_number,
    status
  ) values (
    #{tenantId},
    #{caseNumber},
    #{status}
  )
</insert>

Generated keys are convenient, but retry/idempotency can be harder.

For command-create with retry risk, app-generated IDs are often cleaner.

27. Transaction Integration

With Spring:

@Transactional
public void approve(ApproveCaseCommand command) {
    int updated = caseFileMapper.approveWithExpectedVersion(row);
    if (updated != 1) {
        throw new OptimisticConflict(...);
    }

    auditMapper.insert(audit);
    outboxMapper.insert(event);
}

MyBatis mapper participates in Spring transaction when configured with SqlSessionTemplate/transaction manager.

Test rollback across mapper calls.

28. Rollback Test

@Test
void rollbackAcrossMyBatisMappers() {
    assertThatThrownBy(() -> service.approveButOutboxFails(command))
            .isInstanceOf(RuntimeException.class);

    assertThat(caseQuery.status(command.caseId())).isEqualTo(UNDER_REVIEW);
    assertThat(auditQuery.findByCommand(command.commandId())).isEmpty();
}

This proves transaction integration.

29. Batch Insert

MyBatis can batch through executor type or foreach multi-row insert.

Multi-row insert:

<insert id="insertAuditBatch">
  insert into case_audit (
    id,
    case_id,
    action,
    actor_id,
    created_at
  )
  values
  <foreach collection="rows" item="row" separator=",">
    (
      #{row.id},
      #{row.caseId},
      #{row.action},
      #{row.actorId},
      #{row.createdAt}
    )
  </foreach>
</insert>

Cap batch size to avoid huge SQL/parameter limit.

30. MyBatis ExecutorType BATCH

MyBatis supports batch executor.

Use when same statement repeated.

Cautions:

flush statements behavior;
generated keys;
partial failure;
transaction boundary;
memory;
Spring integration configuration.

For many cases, chunked multi-row insert or JDBC batch may be simpler.

Test counts and rollback.

31. Pagination

Offset:

order by ${sortSql}
limit #{limit}
offset #{offset}

Keyset:

<if test="cursor != null">
  and (
    c.updated_at &lt; #{cursor.updatedAt}
    or (c.updated_at = #{cursor.updatedAt} and c.id &lt; #{cursor.id})
  )
</if>
order by c.updated_at desc, c.id desc
limit #{limitPlusOne}

XML requires escaping < as <.

Cursor fields must match order.

32. Count Query

Do not blindly count complex queries.

Mapper can expose:

List<Row> searchSlice(Query query);
long countSearch(Query query);

Use count only if needed.

If join duplicates parent, count query must use distinct or exists.

Review separately.

33. Stored Procedure

MyBatis supports callable statements.

Use when DB owns logic.

Contract must document:

transaction;
parameters;
output;
error codes;
idempotency;
locks;
migration/versioning.

Do not hide business workflow in stored procedure without test and review path.

34. Annotation Mapper

@Select("""
    select id, case_number, status
    from case_file
    where tenant_id = #{tenantId}
      and id = #{caseId}
    """)
Optional<CaseFileRow> findByTenantAndId(UUID tenantId, UUID caseId);

Good for small simple SQL.

For complex dynamic SQL, XML is usually more readable.

Annotations can become unreadable for long query.

35. Provider/Dynamic SQL Java API

MyBatis supports provider classes and MyBatis Dynamic SQL library.

Useful if:

team wants Java DSL;
dynamic query complex;
compile-time composition desired.

But compare with jOOQ. If you want type-safe SQL DSL with codegen, jOOQ may be a stronger fit.

Use MyBatis Dynamic SQL when it fits ecosystem and mapping style.

36. SQL Ownership

MyBatis makes SQL visible.

Use that visibility:

review SQL as first-class artifact;
name mapper methods by use case;
include query comments for critical SQL;
keep XML close to mapper interface;
test SQL with real DB;
review query plans.

Do not treat XML as second-class generated detail.

37. Query Comment

<select id="searchDashboardRows" resultMap="CaseDashboardRowMap">
  /* query=CaseDashboardMapper.searchDashboardRows */
  select ...
</select>

Helps DB logs/tracing.

Avoid including user/tenant values in comments.

38. Error Translation

MyBatis/Spring may throw persistence exceptions.

Translate:

unique constraint -> duplicate;
FK violation -> invalid reference/not found;
deadlock/serialization -> retryable;
timeout -> data access timeout;
too many rows -> invariant violation.

Constraint names still matter.

39. Testing Mapper SQL

Test each critical mapper against real DB:

insert;
optional find;
conditional update count;
dynamic filters;
sort whitelist;
pagination;
keyset cursor;
count query;
resultMap nullability;
enum TypeHandler;
batch insert rollback;
tenant isolation.

Do not rely only on unit tests for XML.

40. TypeHandler Test

@Test
void mapsCaseStatusCode() {
    fixture.insertCase(status("UNDER_REVIEW"));

    CaseFileRow row = mapper.findByTenantAndId(tenantId, caseId).orElseThrow();

    assertThat(row.status()).isEqualTo(CaseStatus.UNDER_REVIEW);
}

Also test unknown code if possible via corrupt fixture.

41. Dynamic SQL Test

@Test
void searchAppliesOnlyProvidedFilters() {
    fixture.caseFile(tenantA, OPEN, HIGH);
    fixture.caseFile(tenantA, CLOSED, HIGH);
    fixture.caseFile(tenantA, OPEN, LOW);

    Query q = Query.builder()
            .tenantId(tenantA)
            .status(OPEN)
            .priority(HIGH)
            .limit(20)
            .build();

    List<Row> rows = mapper.search(q);

    assertThat(rows).hasSize(1);
}

Dynamic SQL must be tested by combinations.

42. Sort Injection Test

@Test
void sortIsWhitelistedBeforeMapper() {
    assertThatThrownBy(() -> Query.fromRequest(sort("updated_at desc; drop table case_file")))
            .isInstanceOf(InvalidQuery.class);
}

Mapper should receive enum-derived sortSql, not raw request string.

43. ResultMap Drift Test

If migration renames column or query alias changes, resultMap can break.

Integration test catches it.

Critical projection tests should assert fields, not just row count.

44. MyBatis and Domain Repository

Repository implementation:

public Optional<CaseFile> loadForApproval(CaseFileId id) {
    return caseFileMapper.findByTenantAndId(currentTenant.id(), id.value())
            .map(row -> domainMapper.toCaseFile(row));
}

public void save(CaseFile caseFile) {
    int updated = caseFileMapper.updateStatusWithVersion(
            CaseStatusUpdateRow.from(caseFile)
    );

    if (updated == 0) {
        throw new OptimisticConflict(caseFile.id(), caseFile.version());
    }
}

MyBatis mapper remains persistence detail.

45. MyBatis and Query Service

public Slice<CaseDashboardRow> search(CaseDashboardQuery query) {
    List<CaseDashboardRow> rows = caseDashboardMapper.search(query.withLimitPlusOne());

    return Slice.fromLimitPlusOne(rows, query.limit());
}

This is natural fit.

Read DTO mapping is explicit in resultMap.

46. MyBatis vs jOOQ

Need	MyBatis	jOOQ
hand-written SQL XML review	excellent	possible but DSL focus
type-safe schema	limited	strong
dynamic SQL	XML tags / dynamic SQL library	DSL conditions
codegen schema compile safety	not core	core
stored procedure legacy	strong	strong
mapping result maps	strong	mapper/converter
SQL DSL composition	less type-safe unless dynamic-sql	strong
learning curve for SQL team	often easy	moderate

Both are SQL-first. Choose based on team and system needs.

47. MyBatis Anti-Pattern: Raw `${}` From Request

SQL injection.

Use #{} and whitelisted fragments.

48. MyBatis Anti-Pattern: Massive XML With Hidden Business Logic

SQL mapper should not become unreadable procedural business engine.

Keep application/domain logic in Java unless DB procedure deliberately owns it.

49. MyBatis Anti-Pattern: Nested Select N+1

Nested select can hide N+1 like ORM lazy loading.

Use explicit query strategy.

50. MyBatis Anti-Pattern: Generic Mapper for Every Table

Generic CRUD mapper hides semantics.

Use intent-specific methods for domain-critical operations.

51. MyBatis Anti-Pattern: Ignoring Update Count

Same as JDBC/jOOQ. Affected rows are correctness signal.

52. MyBatis Anti-Pattern: Unbounded Select

Always limit/page/chunk.

53. Production Checklist

54. Mini Lab

Implement MyBatis mapper for:

Case dashboard search:
- tenant scoped;
- visible unit IDs;
- optional status;
- optional priority;
- optional assigned officer;
- keyword case number;
- sort whitelist;
- keyset cursor;
- limit + 1;
- left join officer name.

Tasks:

Define query object.
Define sort enum.
Write mapper interface.
Write XML select with dynamic SQL.
Write resultMap.
Handle empty visible unit IDs.
Add keyset cursor condition.
Add integration tests for filters/sort/cursor.
Add SQL injection test for sort.
Add explain plan/index review.

55. Summary

MyBatis is a powerful SQL mapper when used with discipline.

You must master:

mapper interface;
XML vs annotation choice;
#{} vs ${};
resultMap;
constructor mapping;
TypeHandler;
parameter object;
dynamic SQL tags;
foreach/choose/where/set;
sort whitelist;
keyset pagination;
nested mapping risk;
nested select N+1;
transaction integration;
update count discipline;
batch insert;
error translation;
real DB tests;
SQL ownership.

Part berikutnya membahas SQL Ownership and Reviewability: explain plan, query naming, schema coupling, generated SQL visibility, regulatory defensibility, and how to build an engineering culture where SQL is reviewed like code.

56. References

MyBatis 3 Documentation: https://mybatis.org/mybatis-3/
MyBatis XML Mapper: https://mybatis.org/mybatis-3/sqlmap-xml.html
MyBatis Dynamic SQL: https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html
MyBatis Spring: https://mybatis.org/spring/

Lesson Recap

You just completed lesson 47 in deepen practice. Use the series map if you want to review the broader track, or continue directly into the next lesson while the context is still warm.

Back To Series Next Lesson

Continue The Track

Keep the momentum while the lesson is still fresh. Move backward for review or continue forward into the next concept.

Previous Lesson

Lesson 46

jOOQ Transactions and Batching

Next Lesson

Lesson 48

SQL Ownership and Reviewability