MyBatis 進階映射：resultMap 實現一對一與一對多關聯查詢實戰

項目結構與配置

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/order
jdbc.username=root
jdbc.password=root

SqlMapConfig.xml 核心配置

<configuration>
  <properties resource="db.properties"/>
  <typeAliases>
    <package name="com.mybatis.bean"/>
  </typeAliases>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <package name="com.mybatis.mapper"/>
  </mappers>
</configuration>

實體類設計

// User.java
public class User implements Serializable {
    private int id;
    private String username;
    private Date birthday;
    private List orders; // 1 對多
    // getter/setter
}

// Orders.java
public class Orders {
    private int id;
    private int user_id;
    private String order_number;
    private User user; // 多對 1
    private List orderdetails; // 1 對多
    // getter/setter
}

// Orderdetail.java
public class Orderdetail {
    private int id;
    private int orders_id;
    private int item_id;
    private int item_num;
    private Float item_price;
    private Items items; // 多對 1
    // getter/setter
}

// Items.java
public class Items {
    private int id;
    private String item_name;
    private Float item_price;
    private String item_detail;
    // getter/setter
}

Mapper XML 關鍵映射

<mapper namespace="com.mybatis.mapper.OrderMapper">

  <!-- 一對一：訂單 -> 用戶 -->
  <resultMap type="Orders" id="orderUserResultMap">
    <id column="id" property="id"/>
    <result column="order_number" property="order_number"/>
    <result column="user_id" property="user_id"/>
    <association property="user" javaType="com.mybatis.bean.User">
      <id column="user_id" property="id"/>
      <result column="username" property="username"/>
      <result column="birthday" property="birthday"/>
    </association>
  </resultMap>

  <!-- 一對多：訂單 -> 明細列表 (繼承上面 resultMap) -->
  <resultMap type="Orders" id="orderUserDetailResultMap" extends="orderUserResultMap">
    <collection property="orderdetails" ofType="com.mybatis.bean.Orderdetail">
      <id column="order_detail_id" property="id"/>
      <result column="item_id" property="item_id"/>
      <result column="item_num" property="item_num"/>
      <result column="item_price" property="item_price"/>
    </collection>
  </resultMap>

  <!-- 查詢 1：resultType 簡單映射 (扁平化 DTO) -->
  <select id="findOrderUserList" resultType="OrderUserCustom">
    SELECT o.id, o.order_number, o.user_id, u.username, u.birthday
    FROM orders o INNER JOIN user u ON o.user_id = u.id
  </select>

  <!-- 查詢 2：resultMap 一對一 -->
  <select id="findOrderUserListByResultMap" resultMap="orderUserResultMap">
    SELECT o.id, o.order_number, o.user_id, u.username, u.birthday
    FROM orders o INNER JOIN user u ON o.user_id = u.id
  </select>

  <!-- 查詢 3：resultMap 一對多 -->
  <select id="findOrderItemsList" resultMap="orderUserDetailResultMap">
    SELECT o.id, o.order_number, o.user_id, u.username, u.birthday,
           od.item_id, od.item_num, od.item_price, od.id as order_detail_id
    FROM orders o
    INNER JOIN user u ON o.user_id = u.id
    LEFT JOIN orderdetail od ON o.id = od.orders_id
  </select>

</mapper>

Mapper 接口

public interface OrderMapper {
    List findOrderUserList() throws Exception;
    List findOrderUserListByResultMap() throws Exception;
    List findOrderItemsList() throws Exception;
}

關鍵點解析

SQL 寫法注意

• 列名別名 as 對應 property，避免同名列衝突（如 od.id as order_detail_id）
• LEFT JOIN 保證無明細訂單也能查出
• 查詢列順序無要求，但建議主表列在前，關聯表列在後

進階技巧

1. 嵌套查詢：<association select="selectUserById" column="user_id"/> 按需加載，解決 N+1 問題需配合 fetchType="lazy"
2. 鑒別器：<discriminator javaType="int" column="type"> 根據列值選擇不同 resultMap
3. 自動映射：<resultMap autoMapping="true"> 簡單屬性自動匹配，複雜關聯手動配置

源碼地址

GitHub 完整項目