MyBatis, in depth: mappers, result maps, dynamic SQL, batching and the caches that bite

From mapper call to JDBC

The execution model and setup

At startup MyBatis reads your mapper XML files and annotated interfaces into one Configuration object holding a MappedStatement per statement, identified by namespace plus id, for example com.shop.OrderMapper.findById. Your mapper interface has no implementation; MyBatis generates a proxy that turns each method call into a lookup of the matching statement and a call on a SqlSession.

The session delegates to an Executor. SIMPLE (the default) prepares a new statement each time, REUSE caches prepared statements by SQL text within the session, and BATCH queues updates for JDBC batching. The executor then uses a StatementHandler to prepare and run the statement, a ParameterHandler to bind parameters through TypeHandlers, and a ResultSetHandler to build objects. With Spring, SqlSessionTemplate binds one session to each Spring transaction, so all mapper calls inside one @Transactional method share a session and a connection.

<!-- Maven: the Spring Boot starter wires the factory, template and mapper scanning. -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version><!-- pick the line matching your Spring Boot major version --></version>
</dependency>

# application.properties
mybatis.mapper-locations=classpath:mappers/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

Mappers and parameter binding

Statements can live in annotations or XML. Annotations suit short, fixed queries; XML suits anything with dynamic parts or nested results. Both can coexist on the same interface, as long as each method is defined once.

@Mapper
public interface OrderMapper {
  @Select("SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE id = #{id}")
  Order findById(long id);

  @Insert("INSERT INTO orders (customer_id, status, total_cents) VALUES (#{customerId}, #{status}, #{totalCents})")
  @Options(useGeneratedKeys = true, keyProperty = "id")
  int insert(Order order);

  List<OrderSummary> search(OrderSearch criteria);   // defined in OrderMapper.xml
}

The most important rule in MyBatis is the difference between #{} and ${}. #{status} becomes a JDBC ? placeholder and the value is bound as a parameter, so it cannot change the SQL. ${column} is pasted into the SQL text as is. If any part of a ${} value comes from a request, you have SQL injection. The legitimate use is identifiers that cannot be parameters, such as a sort column; validate those against an allow-list in Java and never pass them through raw.

With map-underscore-to-camel-case on, customer_id fills customerId without a result map. useGeneratedKeys writes the database-generated ID back into the object you passed.

Result maps and the N+1 choice

Flat rows map automatically. Object graphs need a <resultMap> with <association> for one-to-one and <collection> for one-to-many. There are two ways to fill them. A nested select runs a second query per parent row: simple to write and an N+1 problem the moment you load a list of 50 orders and fire 50 line-item queries. Nested results map a single JOIN, and MyBatis groups rows back into parents using the <id> elements.

<resultMap id="orderWithLines" type="com.shop.Order">
  <id property="id" column="order_id"/>
  <result property="status" column="status"/>
  <result property="totalCents" column="total_cents"/>
  <collection property="lines" ofType="com.shop.OrderLine">
    <id property="id" column="line_id"/>
    <result property="sku" column="sku"/>
    <result property="quantity" column="quantity"/>
  </collection>
</resultMap>

<select id="findWithLines" resultMap="orderWithLines">
  SELECT o.id AS order_id, o.status, o.total_cents,
         l.id AS line_id, l.sku, l.quantity
  FROM orders o LEFT JOIN order_lines l ON l.order_id = o.id
  WHERE o.id = #{id}
</select>

Always declare <id> elements: without them MyBatis compares all columns to decide whether two rows are the same parent, which is slower and can merge distinct objects. And never put LIMIT on a query that joins a collection, because it limits joined rows, not parents, and the last order loses some of its lines. Page the parent IDs first, then fetch children for those IDs.

Dynamic SQL

Dynamic SQL is MyBatis's main reason to use XML. <if> includes a fragment when a test is true, <where> adds WHERE only if something inside produced text and removes a leading AND or OR, <set> does the same for UPDATE and trailing commas, <choose> is a switch, and <foreach> expands collections, typically for IN lists.

<select id="search" resultType="com.shop.OrderSummary">
  SELECT id, customer_id, status, total_cents, created_at
  FROM orders
  <where>
    <if test="customerId != null">AND customer_id = #{customerId}</if>
    <if test="statuses != null and !statuses.isEmpty()">
      AND status IN
      <foreach item="s" collection="statuses" open="(" separator="," close=")">#{s}</foreach>
    </if>
    <if test="createdAfter != null">AND created_at &gt;= #{createdAfter}</if>
    <if test="afterId != null">AND id &lt; #{afterId}</if>
  </where>
  ORDER BY ${sortColumn} DESC, id DESC
  LIMIT #{pageSize}
</select>

Note that <foreach> over an empty list would produce IN (), a syntax error, which is why the test checks for emptiness. Very large IN lists hit driver and database parameter limits; chunk them or use a temporary table. The ${sortColumn} is safe only because the service sets it from an allow-list. Comparison operators are written as > and < because the file is XML.

Batch writes

Inserting 100,000 rows one statement at a time is 100,000 round trips. The BATCH executor queues statements and sends them with JDBC addBatch and executeBatch. Flush in chunks so memory stays bounded.

try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  OrderLineMapper mapper = session.getMapper(OrderLineMapper.class);
  int n = 0;
  for (OrderLine line : lines) {
    mapper.insert(line);
    if (++n % 1000 == 0) session.flushStatements();   // send this chunk
  }
  session.flushStatements();
  session.commit();
}

Inside Spring, define a second SqlSessionTemplate bean constructed with ExecutorType.BATCH and inject mappers from it, so batching joins the Spring transaction. Return values in batch mode are not row counts until the batch is flushed, and whether generated keys come back for batched inserts depends on the driver; test with yours. With MySQL Connector/J, rewriteBatchedStatements=true on the URL lets the driver rewrite a batch into multi-row INSERTs, which is often the bigger win.

The two caches

MyBatis has two caches, and both surprise people. The first-level (local) cache is per session and always on: run the same select with the same parameters twice in one session and the second call returns the cached object without touching the database. Any insert, update, delete, commit or rollback in that session clears it. Under Spring, outside a transaction each mapper call gets its own session, so the cache barely matters; inside a long transaction, a re-read after another process changed the row returns the old object, and if your code mutated that object, the mutation shows up in the next read too. Setting localCacheScope=STATEMENT limits the cache to a single statement.

The second-level cache is per mapper namespace and off until you add <cache/> to a mapper XML. Writes through that namespace flush it, but writes through another namespace or another service do not. A query joining orders and customers cached in the orders namespace stays stale when customers change. Use it only for read-mostly reference data owned by one namespace, or leave it off and cache at the service layer where you control invalidation.

Type handlers and plugins

Two extension points cover most needs. A TypeHandler converts between a Java type and JDBC, for example a value object or a JSON column. A plugin, an Interceptor, wraps one of the four core components; use it for cross-cutting concerns such as slow-query logging or tenant checks.

@Intercepts(@Signature(type = Executor.class, method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
public class SlowQueryInterceptor implements Interceptor {
  @Override public Object intercept(Invocation inv) throws Throwable {
    long start = System.nanoTime();
    try { return inv.proceed(); }
    finally {
      long ms = (System.nanoTime() - start) / 1_000_000;
      if (ms > 200) log.warn("slow mapper {} took {} ms", ((MappedStatement) inv.getArgs()[0]).getId(), ms);
    }
  }
}

Avoid RowBounds for paging: it fetches rows and skips them in Java, so page 500 reads 500 pages. Put LIMIT in the SQL. For exports, return a Cursor<T> from the mapper and iterate it inside a transaction with a fetch size set, so rows stream instead of loading into a list.

Worked example: an order search endpoint

Worked example: a support tool needs an order search filtered by customer, statuses and date, sorted by a user-chosen column, paged. The search statement above is the whole data layer. The service maps the requested sort to an allow-list (created_at or total_cents; anything else falls back to created_at), caps page size at 100, and uses keyset paging: the last ID of the previous page becomes afterId (strictly correct when sorting by created_at with increasing IDs; for other sorts, carry the sort value too). That keeps every page an index range scan instead of OFFSET reading and discarding earlier rows.

When the agent opens an order, findWithLines loads it and its lines in one JOIN. Test both statements against the real database engine with Testcontainers, including an empty status list, a sort value outside the allow-list and an order with no lines; a LEFT JOIN with no lines should yield an empty list, not a list containing one empty line, which is exactly what the <id> element guarantees.

Failure modes

• SQL injection through ${} with request data. Fix: #{} everywhere, allow-lists for identifiers, and a code-review grep for ${.
• N+1 queries from nested selects over lists. Fix: nested results with a JOIN, or one IN query for all children.
• Truncated collections from LIMIT on a joined query. Fix: page parents first.
• Stale reads from the local cache in long transactions or a namespace-scoped second-level cache. Fix: STATEMENT scope where it matters; second-level cache only for single-owner reference data.
• Silent mapping gaps. A renamed column leaves a property null with no error. Fix: set autoMappingUnknownColumnBehavior to WARNING or FAILING in tests.
• Memory blow-ups from selecting huge result sets into a List. Fix: Cursor or a ResultHandler with a fetch size.

Trade-offs

Compared with Hibernate and JPA, MyBatis removes the persistence context, so there are no surprise flushes or lazy-loading exceptions, but also no automatic change tracking: every update is a statement you write. Compared with jOOQ, it keeps SQL as text instead of type-checked Java, so a schema change is caught at test time, not compile time. Compared with Spring Data repositories, simple CRUD takes more code. Choose MyBatis when the SQL is the hard part, when DBAs review queries as SQL, or when you inherit a schema that resists object mapping.

Mixing is also legitimate. Many codebases use JPA or Spring Data for routine CRUD and MyBatis for reporting and search queries in the same service. If you do this, share one DataSource and one transaction manager, so a MyBatis read inside a JPA transaction sees that transaction's writes. Remember that Hibernate may not have flushed pending changes yet, so flush explicitly before the MyBatis query when it needs to see them.

What to do next

1. Grep your mappers for ${ and replace or allow-list every hit.
2. Find nested selects used over lists and convert them to JOINs with <id> elements.
3. Remove RowBounds and OFFSET paging on large tables in favour of keyset paging.
4. Decide on caching explicitly: STATEMENT local scope for long transactions, no second-level cache without a single owner.
5. Switch bulk loads to the BATCH executor with chunked flushes.
6. Add a slow-query interceptor and Testcontainers tests for every dynamic statement.