分步查询
<!-- 部门 Mapper XML 文件 -->
<!-- 查询某个部门及其下的所有员工，使用 resultMap 定义嵌套查询 -->
<select id="selectDepartmentWithEmployees" resultMap="DepartmentWithEmployeesResultMap">
SELECT * FROM department WHERE department_id = #{departmentId}
</select>
<resultMap id="DepartmentWithEmployeesResultMap" type="Department">
<!-- 部门的属性映射 -->
<id property="departmentId" column="department_id"/>
<result property="departmentName" column="department_name"/>
<!-- 嵌套查询，查询该部门下的所有员工 -->
<collection property="employees" ofType="Employee" column="department_id" select="selectEmployeesByDepartmentId"/>
</resultMap>
<!-- 员工 Mapper XML 文件 -->
<!-- 查询某个部门下的所有员工 -->
<select id="selectEmployeesByDepartmentId" resultType="Employee">
SELECT * FROM employee WHERE department_id = #{departmentId}
</select>
级联查询
<!-- 部门 Mapper XML 文件 -->
<!-- 查询某个部门及其下的所有员工，使用级联查询 -->
<select id="selectDepartmentWithEmployees" resultMap="DepartmentWithEmployeesResultMap">
SELECT d.*, e.* FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
WHERE d.department_id = #{departmentId}
</select>
<resultMap id="DepartmentWithEmployeesResultMap" type="Department">
<id property="departmentId" column="department_id"/>
<result property="departmentName" column="department_name"/>
<!-- 嵌套结果集映射，将员工的属性映射到 employees 集合中 -->
<collection property="employees" ofType="Employee">
<id property="employeeId" column="employee_id"/>
<result property="employeeName" column="employee_name"/>
<!-- 其他员工的属性映射 -->
</collection>
</resultMap>
