Chapter 08

ResultMaps, associations and collections

A resultMap is the most powerful element in MyBatis. It turns a flat, joined result set into a nested object graph — and it is the difference between one efficient query and four hundred inefficient ones.

⏱ 22 min readLevel: intermediate → advanced
Diagram showing a joined result set on the left being mapped into a User object with an Address association and a list of Orders
One join, de-duplicated by the <id> element, becomes one User with nested objects.

When you need one

resultType handles the easy case: column names match property names, or mapUnderscoreToCamelCase bridges the gap. Reach for a resultMap when any of these is true:

  • Column names cannot be aliased to match properties (legacy schema, shared views).
  • The result contains nested objects — one-to-one or one-to-many.
  • You need a custom TypeHandler for a specific column.
  • The class has no default constructor.
  • The concrete type depends on a column value (a discriminator).

The basic form

UserMapper.xml
<resultMap id="userMap" type="User">
  <id     property="id"        column="u_id"/>
  <result property="userName"  column="u_name"/>
  <result property="email"     column="u_email"/>
  <result property="status"    column="u_status"
          typeHandler="com.bitsnippets.shop.handler.UserStatusHandler"/>
  <result property="createdAt" column="u_created_at"/>
</resultMap>

<select id="findById" resultMap="userMap">
  SELECT id          AS u_id,
         user_name   AS u_name,
         email       AS u_email,
         status      AS u_status,
         created_at  AS u_created_at
  FROM app_user
  WHERE id = #{id}
</select>
<id> is not decoration

The <id> element tells MyBatis which columns identify a row. It is used to de-duplicate parents when a join repeats them, and as the cache key for nested objects. Omit it in a one-to-many mapping and you get one parent object per joined row instead of one per entity.

resultMap attributes

AttributeMeaning
idLocal name, referenced by resultMap="…".
typeTarget class (alias or FQN).
extendsInherit another map's mappings and add to them.
autoMappingOverride the global setting for this map only.
inheritance keeps maps DRY
<resultMap id="userBase" type="User">
  <id     property="id"       column="u_id"/>
  <result property="userName" column="u_name"/>
  <result property="email"    column="u_email"/>
</resultMap>

<resultMap id="userDetail" type="User" extends="userBase">
  <result property="createdAt" column="u_created_at"/>
  <result property="version"   column="u_version"/>
</resultMap>

association — one-to-one

Two ways to fill a nested object. Start with the good one.

Nested results (one join, one query)

UserMapper.xml
<resultMap id="userWithAddress" type="User">
  <id     property="id"       column="u_id"/>
  <result property="userName" column="u_name"/>

  <association property="address" javaType="Address">
    <id     property="id"      column="a_id"/>
    <result property="line1"   column="a_line1"/>
    <result property="city"    column="a_city"/>
    <result property="country" column="a_country"/>
  </association>
</resultMap>

<select id="findWithAddress" resultMap="userWithAddress">
  SELECT u.id      AS u_id,
         u.user_name AS u_name,
         a.id      AS a_id,
         a.line1   AS a_line1,
         a.city    AS a_city,
         a.country AS a_country
  FROM app_user u
  LEFT JOIN address a ON a.user_id = u.id
  WHERE u.id = #{id}
</select>

You can also point association at an existing map with resultMap="addressMap" plus columnPrefix="a_", which avoids repeating the child mapping:

columnPrefix reuse
<resultMap id="addressMap" type="Address">
  <id     property="id"      column="id"/>
  <result property="line1"   column="line1"/>
  <result property="city"    column="city"/>
</resultMap>

<resultMap id="userWithAddress2" type="User">
  <id     property="id"       column="u_id"/>
  <result property="userName" column="u_name"/>
  <association property="address" resultMap="addressMap" columnPrefix="a_"/>
</resultMap>

Nested select (one extra query per row)

UserMapper.xml
<resultMap id="userLazyAddress" type="User">
  <id     property="id"       column="id"/>
  <result property="userName" column="user_name"/>
  <association property="address"
               column="id"
               select="com.bitsnippets.shop.mapper.AddressMapper.findByUserId"
               fetchType="lazy"/>
</resultMap>
Nested resultsNested select
Queries for 100 parents1101
Can be lazyNoYes
Column aliasing neededYesNo
Works with second level cacheYesPer child namespace
Use forLists, reports, anything hotRarely-touched, expensive children

collection — one-to-many

UserMapper.xml
<resultMap id="userWithOrders" type="User">
  <id     property="id"       column="u_id"/>
  <result property="userName" column="u_name"/>
  <result property="email"    column="u_email"/>

  <association property="address" javaType="Address">
    <id     property="id"   column="a_id"/>
    <result property="city" column="a_city"/>
  </association>

  <collection property="orders" ofType="Order">
    <id     property="id"       column="o_id"/>
    <result property="total"    column="o_total"/>
    <result property="status"   column="o_status"/>
    <result property="placedAt" column="o_placed_at"/>
  </collection>
</resultMap>

<select id="findWithOrders" resultMap="userWithOrders">
  SELECT u.id          AS u_id,
         u.user_name   AS u_name,
         u.email       AS u_email,
         a.id          AS a_id,
         a.city        AS a_city,
         o.id          AS o_id,
         o.total       AS o_total,
         o.status      AS o_status,
         o.placed_at   AS o_placed_at
  FROM app_user u
  LEFT JOIN address        a ON a.user_id = u.id
  LEFT JOIN customer_order o ON o.user_id = u.id
  WHERE u.id = #{id}
  ORDER BY u.id, o.id
</select>
Three rules for one-to-many mapping

1. ofType is the element type, not the collection type.
2. Every level needs an <id>, or rows will not collapse.
3. Alias every column. Two tables both having id and status is the norm, and unaliased duplicates silently map to the wrong object.

LIMIT does not work with collection joins

LIMIT 10 limits rows, not parents. A user with twelve orders will fill the page alone. Page the parent ids in one query, then fetch the graph for those ids in a second — two queries, correct results.

-- step 1
SELECT id FROM app_user ORDER BY id LIMIT 20 OFFSET 40;
-- step 2
SELECT ... FROM app_user u LEFT JOIN customer_order o ON ...
WHERE u.id IN (?, ?, ...) ORDER BY u.id, o.id;

The N+1 problem

The single most common MyBatis performance bug. It appears when a nested select is used on a list-returning query.

Symptom

Preparing: SELECT id, user_name FROM app_user
Parameters:
Total: 500
Preparing: SELECT * FROM customer_order WHERE user_id = ?
Parameters: 1(Long)
Preparing: SELECT * FROM customer_order WHERE user_id = ?
Parameters: 2(Long)
... 498 more times

Cure

Preparing: SELECT u.id AS u_id, ..., o.id AS o_id, ...
           FROM app_user u
           LEFT JOIN customer_order o ON o.user_id = u.id
           ORDER BY u.id, o.id
Parameters:
Total: 1

One query. Rows collapsed by <id>.

How to catch it before your users do:

  1. Set the mapper logger to DEBUG in every integration test.
  2. Count Preparing: lines in tests that exercise list endpoints — assert an upper bound.
  3. Write a plugin that logs a warning when one request issues more than N statements (chapter 12 shows the interceptor).

Lazy loading

mybatis-config.xml
<settings>
  <setting name="lazyLoadingEnabled"    value="true"/>
  <setting name="aggressiveLazyLoading" value="false"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

MyBatis proxies the parent object; the child query fires on the first getter call. That requires the session still to be open — the same “lazy initialisation outside the transaction” failure mode Hibernate is famous for.

Two lazy-loading gotchas

A proxied object serialised to JSON will trigger every lazy load, because the serialiser calls every getter. And toString() is in the default trigger list, so a single debug log line can fire a hundred queries. Consider setting lazyLoadTriggerMethods to an empty value.

discriminator — polymorphic results

Pick a different sub-map based on a column value. Useful for single-table inheritance and typed events.

PaymentMapper.xml
<resultMap id="paymentMap" type="Payment">
  <id     property="id"     column="id"/>
  <result property="amount" column="amount"/>

  <discriminator javaType="string" column="payment_type">
    <case value="CARD"     resultType="CardPayment">
      <result property="last4"     column="card_last4"/>
      <result property="brand"     column="card_brand"/>
    </case>
    <case value="TRANSFER" resultType="TransferPayment">
      <result property="iban"      column="bank_iban"/>
      <result property="reference" column="bank_reference"/>
    </case>
    <case value="WALLET"   resultMap="walletPaymentMap"/>
  </discriminator>
</resultMap>

Constructor mapping for immutable classes

Records and other immutable types have no setters. Map through the constructor instead — argument order must match the signature exactly.

UserSummary.java
public record UserSummary(Long id, String userName, long orderCount) { }
UserMapper.xml
<resultMap id="userSummaryMap" type="UserSummary">
  <constructor>
    <idArg  column="id"          javaType="java.lang.Long"/>
    <arg    column="user_name"   javaType="java.lang.String"/>
    <arg    column="order_count" javaType="long"/>
  </constructor>
</resultMap>

<select id="summaries" resultMap="userSummaryMap">
  SELECT u.id, u.user_name, COUNT(o.id) AS order_count
  FROM app_user u
  LEFT JOIN customer_order o ON o.user_id = u.id
  GROUP BY u.id, u.user_name
  ORDER BY order_count DESC
</select>

Since MyBatis 3.4.3 you can add name="userName" to each <arg> and drop the positional requirement — provided the class was compiled with -parameters.

Auto-mapping interactions

autoMappingBehaviorEffect
NONEOnly explicitly declared mappings are applied. Safest, most verbose.
PARTIAL (default)Auto-maps unmatched columns at the top level, but never inside nested result maps.
FULLAuto-maps inside nested maps too — which is how a parent's status column ends up on a child object. Avoid.

Checklist

  • Alias every column in a join, with a per-table prefix.
  • Declare <id> at every nesting level.
  • Prefer nested results; keep nested selects for cold, expensive branches.
  • Never combine LIMIT with a collection join.
  • ORDER BY the parent id so rows group cleanly — and set resultOrdered="true" when they do, to avoid buffering.
  • Assert query counts in tests.

Your one-to-many mapping returns 12 User objects with the same id instead of one user with 12 orders. What is missing?

Without <id>, MyBatis has no way to tell that two rows describe the same parent, so it builds a new object per row. Adding <id property="id" column="u_id"/> collapses them and appends the children to one collection.