resultMap+collection应用

问题说明

一个部门有很多员工，查询部门及该部门下所有员工

实现

表

create table DEPARTMENT
(
  id     VARCHAR2(64) default sys_guid() not null,
  branch VARCHAR2(64)
);
create table STAFF
(
  staff_id          VARCHAR2(64) default sys_guid(),
  staff_no          VARCHAR2(64) not null,
  staff_name        VARCHAR2(64),
  staff_gender      VARCHAR2(20),
  staff_in_date     DATE default sysdate,
  del_flag          VARCHAR2(10),
  branch_id         VARCHAR2(64)
)

实体

Department 部门

@Data
@TableName("department")
public class Department {
    @TableId
    private String id;
    private String branch;
    private List<Staff> staffList;
}

Staff 员工

@Data
@TableName("staff")
public class Staff {
    @TableId
    private String id;

    private String branchId;

    private String staffNo;

    private String staffName;

    private String staffGender;

    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date staffInDate;
}

XML中SQL

    <resultMap id="departmentResultMap" type="com.base.staff.entity.Department">
        <id property="id" column="ID" />
        <result property="branch" column="BRANCH" />
        <collection property="staffList" ofType="com.base.staff.entity.Staff">
            <id property="id" column="STAFF_ID" />
            <result property="staffNo" column="STAFF_NO" />
            <result property="staffName" column="STAFF_NAME" />
            <result property="staffGender" column="STAFF_GENDER" />
            <result property="staffInDate" column="STAFF_IN_DATE" />
            <result property="branchId" column="BRANCH_ID" />
        </collection>
    </resultMap>

    <select id="queryDepartment" resultMap="departmentResultMap">
        SELECT d.id,
            d.branch,
            s.staff_id,
            s.staff_no,
            s.staff_Name,
            s.staff_gender,
            s.staff_in_date,
            s.branch_id
        FROM department d
        LEFT JOIN staff s ON d.id = s.branch_id
        ORDER BY s.staff_id DESC
    </select>

测试