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

2023-12-15 23:44:40

分步查询

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

文章来源:https://blog.csdn.net/weixin_48668249/article/details/135026368
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。