#mybatis #mapper.xml 分步查询 与 级联查询 一对多案例

分步查询

<!-- 部门 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>