Here is the English translation of the article body.

Writing a SELECT in a Mapper goes smoothly enough, but the moment you need to “JOIN orders and their line items and pack a List<OrderItem> inside Order,” things tend to grind to a halt. I hear the same questions all the time: “I can’t tell when to use association versus collection in a resultMap,” or “I got it working with a nested select, and now my SQL log is flooded with SELECT statements.”

This article focuses squarely on hierarchical mapping with resultMap: how to build parent-child objects from a single JOIN, how the number of issued SQL statements differs from nested selects, and the trap where row counts go wrong when you combine this with pagination. For an introduction to MyBatis and the basics of Mappers, see the MyBatis implementation guide.

Everything was verified with Spring Boot 3.x, mybatis-spring-boot-starter 3.0.4 (MyBatis 3.5.16), H2, and Lombok (@Data).

The Three Tables and Sample Data Used in This Article

We’ll use three tables: customers, orders, and order items. Just drop schema.sql and data.sql into an H2 in-memory database and it works, so no Docker required. We’ll insert 10 orders and 25 line items, and reuse exactly these counts for the N+1 measurements later on.

-- schema.sql
CREATE TABLE customers (
  id BIGINT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
CREATE TABLE orders (
  id BIGINT PRIMARY KEY, customer_id BIGINT, ordered_at TIMESTAMP, status VARCHAR(20));
CREATE TABLE order_items (
  id BIGINT PRIMARY KEY, order_id BIGINT, product_name VARCHAR(100),
  quantity INT, unit_price DECIMAL(10, 2));

-- data.sql（顧客2件、注文10件、明細25件）
INSERT INTO customers VALUES (1, '山田太郎', '[email protected]'), (2, '鈴木花子', '[email protected]');
INSERT INTO orders VALUES
  (1, 1, '2026-09-01 10:00:00', 'PAID'),    (2, 2, '2026-09-01 11:00:00', 'PAID'),
  (3, 1, '2026-09-02 10:00:00', 'SHIPPED'), (4, 2, '2026-09-02 11:00:00', 'PAID'),
  (5, 1, '2026-09-03 10:00:00', 'PAID'),    (6, 2, '2026-09-03 11:00:00', 'SHIPPED'),
  (7, 1, '2026-09-04 10:00:00', 'PAID'),    (8, 2, '2026-09-04 11:00:00', 'PAID'),
  (9, 1, '2026-09-05 10:00:00', 'SHIPPED'), (10, 2, '2026-09-05 11:00:00', 'PAID');
INSERT INTO order_items VALUES
  (1, 1, 'コーヒー豆', 2, 1200),  (2, 1, 'ドリッパー', 1, 800),   (3, 1, 'フィルター', 1, 300),
  (4, 2, 'マグカップ', 3, 1500),  (5, 2, 'コーヒー豆', 1, 1200),  (6, 2, 'ミル', 1, 4500),
  (7, 3, 'ケトル', 1, 6000),      (8, 3, 'スケール', 1, 3000),    (9, 3, 'コーヒー豆', 3, 1200),
  (10, 4, 'サーバー', 1, 2500),   (11, 4, 'フィルター', 2, 300),  (12, 4, 'ドリッパー', 1, 800),
  (13, 5, 'コーヒー豆', 5, 1200), (14, 5, 'マグカップ', 2, 1500), (15, 5, 'ミル', 1, 4500),
  (16, 6, 'ケトル', 1, 6000),     (17, 6, 'コーヒー豆', 1, 1200),
  (18, 7, 'スケール', 1, 3000),   (19, 7, 'フィルター', 3, 300),
  (20, 8, 'サーバー', 1, 2500),   (21, 8, 'ドリッパー', 2, 800),
  (22, 9, 'コーヒー豆', 2, 1200), (23, 9, 'マグカップ', 1, 1500),
  (24, 10, 'ミル', 1, 4500),      (25, 10, 'コーヒー豆', 1, 1200);

Here are the Java-side DTOs. With nested mapping, MyBatis creates the parent object first and then adds children to it afterward, so we use classes with setters rather than records.

@Data
public class Order {
    private Long id;
    private LocalDateTime orderedAt;
    private String status;
    private Customer customer;          // 多対1
    private List<OrderItem> items;      // 1対多
}

@Data
public class Customer { private Long id; private String name; private String email; }

@Data
public class OrderItem {
    private Long id; private String productName; private Integer quantity; private BigDecimal unitPrice;
}

Columns are snake_case and Java is camelCase, but every resultMap in this article uses explicit mappings like <result column="ordered_at" property="orderedAt"/>, so nothing depends on the camelCase conversion setting. map-underscore-to-camel-case only takes effect during auto-mapping. We enable it because the annotation example later on relies on auto-mapping, but if you want to lean on auto-mapping inside a nested resultMap, read the autoMapping caveat further down first. The logging and lazy-loading settings are included here as well.

spring:
  datasource:
    url: jdbc:h2:mem:shop
mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true   # 自動マッピング時のみ作用する
    lazy-loading-enabled: true
    aggressive-lazy-loading: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

Mapping Many-to-One (Order → Customer) with association

Let’s start with the simplest case: many-to-one. From an order’s perspective there is exactly one customer, so we use association.

<resultMap id="orderWithCustomer" type="com.example.Order">
  <id property="id" column="id"/>
  <result property="orderedAt" column="ordered_at"/>
  <result property="status" column="status"/>
  <association property="customer" javaType="com.example.Customer">
    <id property="id" column="customer_id"/>
    <result property="name" column="customer_name"/>
    <result property="email" column="customer_email"/>
  </association>
</resultMap>

<select id="selectWithCustomer" resultMap="orderWithCustomer">
  SELECT o.id, o.ordered_at, o.status,
         c.id AS customer_id, c.name AS customer_name, c.email AS customer_email
  FROM orders o JOIN customers c ON c.id = o.customer_id
</select>

The key is to alias columns on the SQL side, such as c.id AS customer_id, so they correspond one-to-one with the column attributes in the resultMap. This is the inline notation where <id> and <result> are written directly inside the association, and it’s enough to get things working.

Always write the <id> element. MyBatis uses the <id> value to decide whether two rows are “the same” and reuses the object accordingly. The impact is small for many-to-one, but it becomes critical with collection, which comes next.

Mapping One-to-Many (Order → List of OrderItem) with collection

This is the main event. When you LEFT JOIN orders with order items, the result rows multiply into “parent × child.”

idstatusitem_idproduct_name
1PAID1コーヒー豆
1PAID2ドリッパー
2PAID4マグカップ

collection is what folds these three rows into “order 1 (two items) and order 2 (one item).” Rows sharing the same parent <id> are grouped into a single Order, and each child row is added to items.

<resultMap id="orderWithItems" type="com.example.Order">
  <id property="id" column="id"/>
  <result property="orderedAt" column="ordered_at"/>
  <result property="status" column="status"/>
  <collection property="items" ofType="com.example.OrderItem">
    <id property="id" column="item_id"/>
    <result property="productName" column="product_name"/>
    <result property="quantity" column="quantity"/>
    <result property="unitPrice" column="unit_price"/>
  </collection>
</resultMap>

<select id="selectWithItems" resultMap="orderWithItems">
  SELECT o.id, o.ordered_at, o.status,
         i.id AS item_id, i.product_name, i.quantity, i.unit_price
  FROM orders o LEFT JOIN order_items i ON i.order_id = o.id
  ORDER BY o.id, i.id
</select>

With collection, you specify the element type with ofType, not javaType. Get this wrong and MyBatis will try to stuff an Order into a List, producing a baffling error, so be careful.

Omitting <id> and misconfiguring it produce different symptoms. If you omit it, MyBatis compares the values of every <result> column to determine row identity, so in this example the grouping happens to come out right. But the comparison is slower, and the grouping breaks easily the moment you add a column or a NULL column sneaks in, so treat it as a future accident waiting to happen. Misconfiguration, on the other hand, shows up loudly. Point the parent <id> at item_id and each order comes back duplicated once per line item; if the child <id> points at a value that’s identical across all rows, the items get merged into one and vanish. Whenever “the counts look wrong,” suspect <id> first.

Orders with no line items are also picked up by the LEFT JOIN. In that case every column mapped in the child resultMap is NULL, so no OrderItem is created and items becomes an empty List. If the child side includes non-NULL columns such as a foreign key or a default value and you end up with phantom empty items, specify the column used for the check explicitly, for example notNullColumn="id".

association and collection can coexist in a single resultMap. Here is the finished form for all three tables, along with the matching SELECT.

<resultMap id="orderDetail" type="com.example.Order">
  <id property="id" column="id"/>
  <result property="orderedAt" column="ordered_at"/>
  <result property="status" column="status"/>
  <association property="customer" javaType="com.example.Customer">
    <id property="id" column="customer_id"/>
    <result property="name" column="customer_name"/>
  </association>
  <collection property="items" ofType="com.example.OrderItem">
    <id property="id" column="item_id"/>
    <result property="productName" column="product_name"/>
    <result property="quantity" column="quantity"/>
    <result property="unitPrice" column="unit_price"/>
  </collection>
</resultMap>

<select id="selectDetailInline" resultMap="orderDetail">
  SELECT o.id, o.ordered_at, o.status,
         c.id AS customer_id, c.name AS customer_name,
         i.id AS item_id, i.product_name, i.quantity, i.unit_price
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  LEFT JOIN order_items i ON i.order_id = o.id
  ORDER BY o.id, i.id
</select>

Reusing Nested resultMaps and Avoiding Column Collisions with columnPrefix

Inline notation is easy to read, but the resultMap bloats as tables are added. And since all three tables have an id column, coming up with aliases every time gets tedious.

So we define the child side as an independent resultMap and reference it via the resultMap attribute. Add columnPrefix and the child resultMap can be written with unprefixed column names.

<resultMap id="customerResultMap" type="com.example.Customer">
  <id property="id" column="id"/>
  <result property="name" column="name"/>
  <result property="email" column="email"/>
</resultMap>

<resultMap id="orderItemResultMap" type="com.example.OrderItem">
  <id property="id" column="id"/>
  <result property="productName" column="product_name"/>
  <result property="quantity" column="quantity"/>
  <result property="unitPrice" column="unit_price"/>
</resultMap>

<!-- 基本形。単体SELECTでも使う -->
<resultMap id="orderResultMap" type="com.example.Order">
  <id property="id" column="id"/>
  <result property="orderedAt" column="ordered_at"/>
  <result property="status" column="status"/>
</resultMap>

<!-- extendsで基本形を継承し、ネスト部分だけ足す -->
<resultMap id="orderDetailResultMap" type="com.example.Order" extends="orderResultMap">
  <association property="customer" resultMap="customerResultMap" columnPrefix="c_"/>
  <collection property="items" resultMap="orderItemResultMap" columnPrefix="i_"/>
</resultMap>

<select id="selectDetail" resultMap="orderDetailResultMap">
  SELECT o.id, o.ordered_at, o.status,
         c.id AS c_id, c.name AS c_name, c.email AS c_email,
         i.id AS i_id, i.product_name AS i_product_name,
         i.quantity AS i_quantity, i.unit_price AS i_unit_price
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  LEFT JOIN order_items i ON i.order_id = o.id
  ORDER BY o.id, i.id
</select>

The correspondence is simple: an association with columnPrefix="c_" passes c_id as id and c_name as name to customerResultMap. As long as the SQL aliases start with c_, customerResultMap can be reused both for a standalone customer SELECT and for the JOIN.

One caveat. Under the default autoMappingBehavior=PARTIAL, a resultMap that contains nested resultMaps is not auto-mapped at all, including its top level. You can enable it individually by adding autoMapping="true" to each resultMap / association / collection, but that risks accidentally picking up colliding id or name columns, so for nested mapping the safe choice is explicit mapping plus columnPrefix.

Writing It with Annotations (@Results / @One / @Many)

For projects that avoid XML, here is the same thing written with annotations. The referenced CustomerMapper and OrderItemMapper only hold standalone SELECTs, and because they use auto-mapping, this is where map-underscore-to-camel-case comes into play.

@Mapper
public interface OrderMapper {

    @Select("SELECT id, ordered_at, status, customer_id FROM orders")
    @Results(id = "orderDetail", value = {
        @Result(property = "id", column = "id", id = true),
        @Result(property = "orderedAt", column = "ordered_at"),
        @Result(property = "status", column = "status"),
        @Result(property = "customer", column = "customer_id",
                one = @One(select = "com.example.CustomerMapper.findById", fetchType = FetchType.EAGER)),
        @Result(property = "items", column = "id",
                many = @Many(select = "com.example.OrderItemMapper.findByOrderId", fetchType = FetchType.LAZY))
    })
    List<Order> selectAll();

    @Select("SELECT id, ordered_at, status, customer_id FROM orders WHERE id = #{id}")
    @ResultMap("orderDetail")   // 定義済みresultMapの再利用
    Order findById(Long id);
}

@Mapper
public interface CustomerMapper {
    @Select("SELECT id, name, email FROM customers WHERE id = #{id}")
    Customer findById(Long id);
}

@Mapper
public interface OrderItemMapper {
    @Select("SELECT id, product_name, quantity, unit_price FROM order_items WHERE order_id = #{orderId}")
    List<OrderItem> findByOrderId(Long orderId);
}

The mapping to XML is shown in this table.

XMLAnnotation
<association>@One
<collection>@Many
<resultMap id="...">@Results(id = "...")
Reference via resultMap="..."@ResultMap("...")
fetchType="lazy"FetchType.LAZY

As you may have noticed from the example above, @One / @Many are fundamentally nested-select style. That means one parent SQL plus N child SQL statements are issued. From MyBatis 3.5.5 onward you can write a single-JOIN nested resultMap with @Many(resultMap = "...", columnPrefix = "i_"), but the referenced resultMap must be defined as @Results(id = ...) on a separate method, and since the SQL lives inside a string, long JOINs become hard to read.

A practical rule of thumb that keeps things consistent: if you want hierarchical mapping via a single JOIN, use XML; if you write annotations, either accept the N+1 of the nested-select approach or mitigate it.

Measuring the Number of Issued SQL Statements: Nested select vs. Nested resultMap

The N+1 problem is quicker to grasp by counting statements in the SQL log than by discussing it in the abstract. Let’s write the same order list two ways and compare.

(A) is the nested-select approach. The select attribute names the SELECT used to fetch children, and the parent’s id is passed via column. To measure the count reliably, we use fetchType="eager" for immediate loading. The lazy behavior is covered in the next section, so a lazy version is included in the same block.

<!-- (A) ネストselect方式。eagerで即時ロード -->
<resultMap id="orderNestedSelect" type="com.example.Order" extends="orderResultMap">
  <collection property="items" column="id" ofType="com.example.OrderItem"
              select="selectItemsByOrderId" fetchType="eager"/>
</resultMap>

<!-- 次節で使うlazy版 -->
<resultMap id="orderNestedSelectLazy" type="com.example.Order" extends="orderResultMap">
  <collection property="items" column="id" ofType="com.example.OrderItem"
              select="selectItemsByOrderId" fetchType="lazy"/>
</resultMap>

<select id="selectAllNestedSelect" resultMap="orderNestedSelect">
  SELECT id, ordered_at, status FROM orders ORDER BY id
</select>

<select id="selectAllNestedSelectLazy" resultMap="orderNestedSelectLazy">
  SELECT id, ordered_at, status FROM orders ORDER BY id
</select>

<select id="selectItemsByOrderId" resultMap="orderItemResultMap">
  SELECT id, product_name, quantity, unit_price FROM order_items WHERE order_id = #{orderId}
</select>

(B) is the selectDetail from earlier, unchanged: the single-JOIN nested resultMap approach. Since log-impl is configured, the SQL is printed to standard output.

# (A) ネストselect方式。親1本 + 子10本 = 11本
==>  Preparing: SELECT id, ordered_at, status FROM orders ORDER BY id
====>  Preparing: SELECT id, product_name, quantity, unit_price FROM order_items WHERE order_id = ?
====> Parameters: 1(Long)
====>  Preparing: SELECT id, product_name, quantity, unit_price FROM order_items WHERE order_id = ?
====> Parameters: 2(Long)
... （注文10まで続く）
<==      Total: 10

# (B) ネストresultMap方式。1本
==>  Preparing: SELECT o.id, o.ordered_at, o.status, c.id AS c_id, ... LEFT JOIN order_items i ON i.order_id = o.id ORDER BY o.id, i.id
<==      Total: 25

What happens as the number of parents grows is obvious at a glance.

Parent rows(A) Nested select(B) Nested resultMap
1011 statements1 statement
100101 statements1 statement
1,0001,001 statements1 statement

The structure is exactly the same as @OneToMany in JPA, and the mitigation strategies are the same ones covered in the JPA performance optimization article.

To be fair, (B) has a weakness too. As its Total: 25 shows, the number of transferred rows expands to parent × child (25 line items’ worth). With data where a single order has hundreds of items, the parent columns get transferred hundreds of times over, so the nested select can actually be lighter in some cases.

The Trap Where Lazy Loading Plus Jackson Serialization Ends Up as N+1 Anyway

You might think, “I set fetchType="lazy" on the nested select, so as long as I don’t touch it, no SQL will run.” That is true as far as it goes. lazy-loading-enabled=true makes lazy the global default, and a fetchType written on an individual element takes precedence. With aggressive-lazy-loading=false (the default), calling one getter won’t trigger loading of the other lazy properties.

The problem is REST APIs. Lazy loading is implemented with Javassist proxies, and if you return Order directly from a @RestController, Jackson calls every getter. The instant getItems() is called, the proxy resolves, and you end up with N+1 SQL statements after all. The only difference is timing: eager loads at parent fetch time, lazy loads when Jackson calls the getter, but the total is the same 11 statements.

@RestController
@RequiredArgsConstructor
public class OrderController {
    private final OrderMapper orderMapper;

    // NG: lazyでもシリアライズ時にgetItems()が呼ばれ、注文の数だけSELECTが流れる
    @GetMapping("/orders/lazy")
    public List<Order> listLazy() {
        return orderMapper.selectAllNestedSelectLazy();
    }

    // OK: 一覧はJOIN一発で取り、レスポンス用DTOに詰め替える
    @GetMapping("/orders")
    public List<OrderResponse> list() {
        return orderMapper.selectDetail().stream()
            .map(OrderResponse::from)
            .toList();
    }
}

public record OrderResponse(Long id, String status, String customerName, List<OrderItem> items) {
    static OrderResponse from(Order o) {
        return new OrderResponse(o.getId(), o.getStatus(), o.getCustomer().getName(), o.getItems());
    }
}

On top of that, Jackson may pick up the proxy’s internal handler field as a property and throw a serialization error. And since the default lazy-load-trigger-methods include equals, hashCode, and toString, simply printing the object to a log via toString() triggers a full load, which is a subtle gotcha.

The workaround is simple: either explicitly copy into a response DTO as in the OK example above, or use a single-JOIN resultMap for list APIs and don’t rely on lazy loading. JPA has the same structural problem, which shows up as LazyInitializationException. Reading the LazyInitializationException article alongside this one makes it click.

The Trap Where Row Counts Go Wrong with Pagination, and How to Avoid It

Slapping a LIMIT onto a resultMap that has a collection produces a particularly confusing bug. The most straightforward workaround is a two-step query that narrows down the parent IDs first. Here it is alongside the NG example.

<!-- NG: JOIN後の行が5行で切られる -->
<select id="selectDetailPaged" resultMap="orderDetailResultMap">
  SELECT o.id, o.ordered_at, o.status,
         c.id AS c_id, c.name AS c_name, c.email AS c_email,
         i.id AS i_id, i.product_name AS i_product_name, i.quantity AS i_quantity, i.unit_price AS i_unit_price
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  LEFT JOIN order_items i ON i.order_id = o.id
  ORDER BY o.id, i.id
  LIMIT 5 OFFSET 0
</select>

<!-- OK 1本目: 親だけをページングしてIDを取る -->
<select id="selectOrderIds" resultType="long">
  SELECT id FROM orders ORDER BY id LIMIT #{limit} OFFSET #{offset}
</select>

<!-- OK 2本目: 絞ったIDでJOINし、collectionを畳む -->
<select id="selectDetailByIds" resultMap="orderDetailResultMap">
  SELECT o.id, o.ordered_at, o.status,
         c.id AS c_id, c.name AS c_name, c.email AS c_email,
         i.id AS i_id, i.product_name AS i_product_name, i.quantity AS i_quantity, i.unit_price AS i_unit_price
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  LEFT JOIN order_items i ON i.order_id = o.id
  WHERE o.id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
  ORDER BY o.id, i.id
</select>

The NG example is meant to return “5 per page,” but because LIMIT applies to the parent × child rows, only 2 orders come back. Worse, order 2, which was cut off at row 5, only has some of its line items. The same thing happens with RowBounds and with PageHelper. PageHelper simply injects a LIMIT into the SQL, so total is counted as 25 child rows too, and the page count no longer matches the number of orders.

On the Mapper side, the two-step query is declared as List<Long> selectOrderIds(@Param("limit") int limit, @Param("offset") int offset) and List<Order> selectDetailByIds(@Param("ids") List<Long> ids). @Param is required for multiple arguments and for foreach; forget it and you get a BindingException. Apply PageHelper to the first query and total correctly reflects the number of orders. When there are zero IDs, the IN clause becomes empty and causes a SQL error, so return early on the Java side.

If you want to do it in a single SQL statement, you can turn orders into a LIMITed subquery in the FROM clause and then LEFT JOIN order_items onto it. However, PageHelper will add another LIMIT on the outside, so this doesn’t play well with it and is better suited to hand-written LIMITs. Another option is to fetch the list without the collection, fetch the items in a separate query using a bulk IN clause, and assemble them into a Map<Long, List<OrderItem>> in Java. The counts are guaranteed correct, but the code grows.

The configuration of PageHelper and RowBounds themselves is covered in the MyBatis pagination article.

Choosing Between Nested select and Nested resultMap

The basic policy is to “default to the single-JOIN nested resultMap.” A single SQL statement gives you a predictable statement count and works well with REST APIs.

AspectNested resultMap (single JOIN)Nested select
SQL statements issued11 + N
Transfer volumeExpands to parent × childOnly what’s needed
PaginationRequires narrowing parent IDs firstParent LIMIT works as-is
Annotation supportPossible from 3.5.5, but effectively XML territoryStraightforward
Lazy loadingNot possiblePossible (beware in REST APIs)

Nested select + lazy is the better fit when there are few parents and a very large number of children, when most screens don’t use the children at all, or when you want to share the child-fetching logic with a standalone Mapper.

Many-to-many works the same way: JOIN through the join table and fold one side with collection. If you’re curious about how this differs from JPA relationship mapping, see the JPA entity relationship mapping article, and if you’re still deciding between MyBatis and JPA in the first place, see the MyBatis vs. JPA comparison article.

Summary

The four things to remember about hierarchical mapping with resultMap: association for many-to-one, collection for one-to-many, <id> is the key that groups rows, and columnPrefix avoids column collisions.

Beyond that, get into the habit of turning on SQL logging and checking the statement count. It’s safer to assume that lazy loading is essentially ineffective in REST APIs, and when combining collection with pagination, narrowing down the parent IDs first is the iron rule.

To go back to Mapper basics, see the MyBatis implementation guide, and for choosing a pagination approach, see the pagination article.