前言
MyBatis 的強大在於靈活的結果映射機制，支援複雜的對象關聯查詢。本文通過「丈夫-妻子」(一對一)、「鎖-鑰匙」(一對多) 經典案例，詳細講解 <association>、<collection>、嵌套查詢與嵌套結果映射，並實戰分頁查詢與批量操作。
一、一對一關聯映射
場景：一個丈夫對應一個妻子
1. 數據庫設計
CREATE TABLE t_husband (
id INT PRIMARY KEY AUTO_INCREMENT,
husband_name VARCHAR(50)
);
CREATE TABLE t_wife (
id INT PRIMARY KEY AUTO_INCREMENT,
wife_name VARCHAR(50),
fk_husband_id INT,
FOREIGN KEY (fk_husband_id) REFERENCES t_husband(id)
);
-- 測試數據
INSERT INTO t_husband VALUES (NULL, 'Tom');
INSERT INTO t_wife VALUES (NULL, 'Kitty', 1);
2. 實體類設計 (雙向關聯)
public class Husband {
private Integer id;
private String name;
private Wife wife; // 關聯對象
// getter/setter/toString
}
public class Wife {
private Integer id;
private String name;
private Husband husband; // 反向關聯
// getter/setter/toString
}
3. Mapper 介面
public interface HusbandMapper {
Husband selectById(int id);
Husband selectHusbandWithWife(int id); // 關聯查詢
}
4. 映射文件 - 嵌套查詢方式 (推薦)
<mapper namespace="com.example.mapper.HusbandMapper">
<!-- 結果映射：丈夫 + 妻子 -->
<resultMap id="husbandWifeMap" type="Husband">
<id property="id" column="id"/>
<result property="name" column="husband_name"/>
<!-- association: 一對一關聯 -->
<association property="wife"
column="id"
javaType="Wife"
select="com.example.mapper.WifeMapper.selectWifeByHusbandId">
</association>
</resultMap>
<select id="selectById" resultType="Husband">
SELECT * FROM t_husband WHERE id = #{id}
</select>
<select id="selectHusbandWithWife" resultMap="husbandWifeMap">
SELECT * FROM t_husband WHERE id = #{id}
</select>
</mapper>
5. 妻子 Mapper (被關聯查詢)
<mapper namespace="com.example.mapper.WifeMapper">
<select id="selectWifeByHusbandId" resultType="Wife">
SELECT * FROM t_wife WHERE fk_husband_id = #{id}
</select>
</mapper>
6. 關鍵屬性解析
| 屬性 | 說明 |
|---|
property | 實體類中關聯屬性名 |
column | 主查詢結果中作為參數的列名 |
javaType | 關聯對象的完整類名或別名 |
select | 關聯查詢的映射語句 ID (命名空間.方法名) |
fetchType | lazy (預設) / eager - 延遲/立即加載 |
二、一對多關聯映射
場景：一把鎖對應多把鑰匙
1. 數據庫設計
CREATE TABLE t_lock (
id INT PRIMARY KEY AUTO_INCREMENT,
lock_name VARCHAR(50)
);
CREATE TABLE t_key (
id INT PRIMARY KEY AUTO_INCREMENT,
key_name VARCHAR(50),
fk_lock_id INT,
FOREIGN KEY (fk_lock_id) REFERENCES t_lock(id)
);
2. 實體類
public class Lock {
private Integer id;
private String name;
private List<Key> keys; // 集合關聯
// getter/setter/toString
}
public class Key {
private Integer id;
private String name;
private Lock lock; // 反向引用
// getter/setter/toString
}
3. Mapper 介面
public interface LockMapper {
int insert(Lock lock);
Lock selectById(int id);
Lock selectLockWithKeys(int id); // 關聯查詢
}
public interface KeyMapper {
int batchInsert(@Param("keys") List<Key> keys);
List<Key> selectKeysByLockId(int lockId);
}
4. 映射文件 - LockMapper.xml
<mapper namespace="com.example.mapper.LockMapper">
<resultMap id="lockMap" type="Lock">
<id property="id" column="id"/>
<result property="name" column="lock_name"/>
</resultMap>
<resultMap id="lockKeysMap" type="Lock">
<id property="id" column="id"/>
<result property="name" column="lock_name"/>
<!-- collection: 一對多關聯 -->
<collection property="keys"
column="id"
ofType="Key"
select="com.example.mapper.KeyMapper.selectKeysByLockId">
</collection>
</resultMap>
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO t_lock (lock_name) VALUES (#{name})
</insert>
<select id="selectById" resultMap="lockMap">
SELECT * FROM t_lock WHERE id = #{id}
</select>
<select id="selectLockWithKeys" resultMap="lockKeysMap">
SELECT * FROM t_lock WHERE id = #{id}
</select>
</mapper>
5. 映射文件 - KeyMapper.xml (含批量插入)
<mapper namespace="com.example.mapper.KeyMapper">
<resultMap id="keyMap" type="Key">
<id property="id" column="id"/>
<result property="name" column="key_name"/>
</resultMap>
<!-- 批量插入：foreach 遍歷集合 -->
<insert id="batchInsert">
INSERT INTO t_key (key_name, fk_lock_id) VALUES
<foreach collection="keys" item="key" separator=",">
(#{key.name}, #{key.lock.id})
</foreach>
</insert>
<select id="selectKeysByLockId" resultMap="keyMap">
SELECT * FROM t_key WHERE fk_lock_id = #{id}
</select>
</mapper>
6. collection 關鍵屬性
ofType：集合泛型類型 (而非 javaType)collection (foreach)：遍歷的集合參數名item (foreach)：迭代變量名separator (foreach)：分隔符
三、分頁查詢與批量操作
1. 分頁工具類
public class PageHelper {
private static final ThreadLocal<PageParam> LOCAL_PARAM = new ThreadLocal<>();
public static void startPage(int pageNum, int pageSize) {
LOCAL_PARAM.set(new PageParam(pageNum, pageSize));
}
public static void clearPage() {
LOCAL_PARAM.remove();
}
public static PageParam getPageParam() {
return LOCAL_PARAM.get();
}
public static class PageParam {
private final int pageNum;
private final int pageSize;
public PageParam(int pageNum, int pageSize) {
this.pageNum = pageNum;
this.pageSize = pageSize;
}
public int getOffset() { return (pageNum - 1) * pageSize; }
public int getLimit() { return pageSize; }
}
}
2. 通用分頁查詢 Mapper
public interface UserMapper {
List<User> selectPage(@Param("params") Map<String, Object> params);
int selectCount(@Param("params") Map<String, Object> params);
}
3. 動態 SQL 分頁映射
<mapper namespace="com.example.mapper.UserMapper">
<resultMap id="userMap" type="User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="email" column="email"/>
</resultMap>
<sql id="commonWhere">
WHERE 1=1
<if test="params.username != null and params.username != ''">
AND username LIKE CONCAT('%', #{params.username}, '%')
</if>
<if test="params.email != null and params.email != ''">
AND email LIKE CONCAT('%', #{params.email}, '%')
</if>
</sql>
<select id="selectPage" resultMap="userMap">
SELECT * FROM t_user
<include refid="commonWhere"/>
ORDER BY id DESC
LIMIT #{params.offset}, #{params.limit}
</select>
<select id="selectCount" resultType="int">
SELECT COUNT(*) FROM t_user
<include refid="commonWhere"/>
</select>
</mapper>
4. Service 層分頁封裝
public PageResult<User> queryPage(int pageNum, int pageSize, String username, String email) {
PageHelper.startPage(pageNum, pageSize);
try {
Map<String, Object> params = new HashMap<>();
params.put("offset", PageHelper.getPageParam().getOffset());
params.put("limit", PageHelper.getPageParam().getLimit());
params.put("username", username);
params.put("email", email);
List<User> list = userMapper.selectPage(params);
int total = userMapper.selectCount(params);
return new PageResult<>(pageNum, pageSize, total, list);
} finally {
PageHelper.clearPage();
}
}
5. 批量操作最佳實踐
// 批量插入
@Insert("")
int batchInsert(@Param("list") List<User> list);
// 批量刪除
@Delete("")
int batchDelete(@Param("ids") List<Integer> ids);
四、關聯查詢優化建議
| 策略 | 適用場景 | 優點 | 缺點 |
|---|
| 嵌套查詢 (association/collection + select) | 關聯數據較大、按需加載 | 支援延遲加載、SQL 簡單 | N+1 問題、多次查詢 |
| 嵌套結果映射 (resultMap + join) | 關聯數據較小、經常一起使用 | 單次查詢、無 N+1 | 結果集冗余、映射複雜 |
| 嵌套結果 + 去重 (resultMap + join + distinct) | 一對多、需避免主表重複 | 單次查詢、數據準確 | SQL 複雜、需注意去重 |
嵌套結果映射範例 (一對多 Join)
<resultMap id="lockKeysJoinMap" type="Lock">
<id property="id" column="lock_id"/>
<result property="name" column="lock_name"/>
<collection property="keys" ofType="Key">
<id property="id" column="key_id"/>
<result property="name" column="key_name"/>
</collection>
</resultMap>
<select id="selectLockWithKeysJoin" resultMap="lockKeysJoinMap">
SELECT
l.id AS lock_id, l.lock_name,
k.id AS key_id, k.key_name
FROM t_lock l
LEFT JOIN t_key k ON l.id = k.fk_lock_id
WHERE l.id = #{id}
</select>
總結
- 一對一：使用
<association>，配合 select 實現延遲加載或 join 實現立即加載
- 一對多：使用
<collection>，注意 ofType 指定泛型類型
- 批量操作：善用
<foreach>，參數用 @Param 明確命名
- 分頁查詢：ThreadLocal 傳遞分頁參數，動態 SQL 組裝 WHERE/LIMIT
- 優化選擇：小數據量用 Join，大數據量用嵌套查詢+延遲加載，必要時引入緩存
