MyBatis Mapper Workflow

What it is

MyBatis maps Java method calls to explicit SQL statements and maps result sets back to Java objects. The Spring Boot starter wires a DataSource, SqlSessionFactory, SqlSessionTemplate, and mapper proxies; your code still owns SQL correctness and shape.

Why it matters

Enterprise systems often need transparent SQL, database-specific tuning, complex joins, and exact affected-row behavior. MyBatis provides that control, but weak parameter binding, unbounded dynamic SQL, accidental N+1 queries, or untested result maps can turn flexibility into risk.

How it works

Simple example

@Mapper
public interface TaskMapper {
    Optional<TaskRow> findById(@Param("id") long id);

    List<TaskRow> findPage(@Param("query") TaskPageQuery query);

    int insert(TaskRow row);

    int markDone(@Param("id") long id, @Param("actorId") long actorId);
}

<mapper namespace="com.henheang.task.persistence.TaskMapper">
  <resultMap id="taskRow" type="com.henheang.task.persistence.TaskRow">
    <id property="id" column="id"/>
    <result property="ownerId" column="owner_id"/>
    <result property="title" column="title"/>
    <result property="status" column="status"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findById" resultMap="taskRow">
    SELECT id, owner_id, title, status, created_at
    FROM task
    WHERE id = #{id}
  </select>
</mapper>

Backend example

<select id="findPage" resultMap="taskRow">
  SELECT id, owner_id, title, status, created_at
  FROM task
  <where>
    owner_id = #{query.ownerId}
    <if test="query.status != null">
      AND status = #{query.status}
    </if>
    <if test="query.cursorCreatedAt != null and query.cursorId != null">
      AND (created_at, id) &lt; (#{query.cursorCreatedAt}, #{query.cursorId})
    </if>
  </where>
  ORDER BY created_at DESC, id DESC
  LIMIT #{query.limit}
</select>

<update id="markDone">
  UPDATE task
  SET status = 'DONE', updated_at = now()
  WHERE id = #{id}
    AND owner_id = #{actorId}
    AND status = 'OPEN'
</update>

The atomic UPDATE combines ownership and valid-state checks. The service interprets an affected-row count of zero as not found, not owned, or invalid state according to a deliberate disclosure policy.

Production example

Common mistakes

• Using ${userInput} in SQL
• Depending on implicit auto-mapping for fragile joins
• Returning Map instead of a typed result
• Hiding many database round trips behind nested selects
• Assuming batch always improves throughput
• Testing SQL only against an in-memory dialect

Best practices

• Use typed parameter objects and @Param names
• Select explicit columns
• Keep mapper methods cohesive and statement IDs aligned
• Use reusable SQL fragments sparingly and visibly
• Interpret affected-row counts
• Test complex queries against the real PostgreSQL or Oracle dialect

Trade-offs

Interview questions

• How does a mapper interface connect to XML?
• Why is #{} safer than ${}?
• How would you implement allowlisted sorting?
• What does SqlSessionTemplate add in Spring?
• How do you detect and fix N+1 with MyBatis?

Hands-on task

1. 1

   Map

   Implement Task insert, find, keyset page, and conditional state transition mapper methods.

2. 2

   Attack

   Try malicious search and sort input and prove no raw text reaches an identifier or predicate.

3. 3

   Test

   Use PostgreSQL Testcontainers to verify result mapping, generated key, pagination order, affected rows, and rollback.

Related topics

• PostgreSQL Indexing
• Spring Transaction Fundamentals
• Backend Testing Strategy
• Task API Lab

References

• MyBatis: MyBatis 3 Mapper XML Files — https://mybatis.org/mybatis-3/sqlmap-xml.html
• MyBatis: MyBatis 3 Dynamic SQL — https://mybatis.org/mybatis-3/dynamic-sql.html
• MyBatis: MyBatis Spring Boot Starter Reference — https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/