MyBatis 映射檔與實體類關聯最佳實踐：別名映射與聯表查詢

核心思想

利用 SQL 列別名 AS "property.nestedProperty" 直接映射到實體類的巢狀物件屬性，避免手寫冗長 <resultMap>。

實體類結構（節選）

public class User extends DataEntity<User> {
    private Office company;      // 歸屬公司
    private Office office;       // 歸屬部門
    private Dept dept;           // 部門
    private Staff staff;         // 職位
    private Role role;           // 角色
    private List<Role> roleList; // 角色列表
    private School school;       // 學校
    private User createBy;       // 創建者
    private User updateBy;       // 更新者
    // 大量基本型別欄位略...
}

映射檔 UserMap.xml 精華

1. 可重用 SQL 片段

<sql id="userColumns">
  a.id, a.company_id AS "company.id", a.office_id AS "office.id",
  a.login_name, a.password, a.no, a.name, a.email, a.phone, a.mobile,
  a.user_type, a.login_ip, a.login_date, a.remarks, a.login_flag,
  a.photo, a.qrcode, a.create_by AS "createBy.id", a.create_date,
  a.update_by AS "updateBy.id", a.update_date, a.del_flag,
  c.name AS "company.name", c.parent_id AS "company.parent.id",
  ca.id AS "company.area.id", ca.name AS "company.area.name",
  o.name AS "office.name", o.parent_id AS "office.parent.id",
  oa.id AS "office.area.id", oa.name AS "office.area.name",
  sc.xxlx AS "school.xxlx", sc.xxmc AS "school.xxmc"
</sql>

<sql id="userJoins">
  LEFT JOIN sys_office c ON c.id = a.company_id
  LEFT JOIN sys_area ca ON ca.id = c.area_id
  LEFT JOIN sys_office o ON o.id = a.office_id
  LEFT JOIN sys_area oa ON oa.id = o.area_id
  LEFT JOIN xj_school sc ON sc.id = a.school
</sql>

2. 查詢語句（自動映射）

<select id="getUserById" resultType="User">
  SELECT <include refid="userColumns"/>
  FROM sys_user a
  <include refid="userJoins"/>
  WHERE a.id = #{id}
</select>

<select id="getByLoginName" resultType="User" parameterType="User">
  SELECT <include refid="userColumns"/>
  FROM sys_user a
  <include refid="userJoins"/>
  WHERE a.login_name = #{loginName} AND a.del_flag = #{DEL_FLAG_NORMAL}
</select>

3. 插入/更新（反向映射）

<insert id="insert">
  INSERT INTO sys_user( id, company_id, office_id, login_name, password, ...,
    create_by, create_date, update_by, update_date, dept_id, staff_id, school )
  VALUES ( #{id}, #{company.id}, #{office.id}, #{loginName}, #{password}, ...,
    #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{dept.id}, #{staff.id}, #{school.id} )
</insert>

<update id="update">
  UPDATE sys_user SET
    company_id = #{company.id}, office_id = #{office.id}, login_name = #{loginName},
    update_by = #{updateBy.id}, update_date = #{updateDate}, school = #{school.id}
  WHERE id = #{id}
</update>

優勢分析

適用條件與注意

• 實體類巢狀物件需有無參建構子，且屬性名與別名點號路徑一致。
• 集合屬性（如 roleList）仍需 <resultMap> + <collection> 處理。
• 多對多、複雜巢狀建議混合使用：主表用別名，集合用 resultMap。
• MyBatis 版本需支援點號別名（3.4+ 完美支援）。

總結

「SQL 別名點號映射 + 可重用 SQL 片段」是 MyBatis 映射檔精簡化的關鍵模式，大幅減少 XML 體積，提升開發與維護效率。