Mybatis嵌套查询（一对多）-Toy模板网

这篇具有很好参考价值的文章主要介绍了Mybatis嵌套查询（一对多）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、返回数据Java类

@Data
public class PersonnelDetailsVO{
    /**
     * 主键
     */
    @Column(name = "ID", length = 36, precision = 0)
    private String id;
    
    /**
     * 人员姓名
     */
    @Column(name = "OPERATE_NAME", length = 36, precision = 0)
    private String operateName;
    
    /**
     * 单位id
     */
    @Column(name = "UNIT_ID", length = 36, precision = 0)
    private String unitId;
    
    /**
     * 单位名称
     */
    @Column(name = "UNIT_NAME", length = 36, precision = 0)
    private String unitName;
    
    /**
     * 身份证号
     */
    @Column(name = "IDCARD", length = 40, precision = 0)
    private String idcard;
    
    /**
     * 人员照片附件id
     */
    @Column(name = "PHOTO_ATT", length = 36, precision = 0)
    private String photoAtt;
    
    /**
     * 联系电话
     */
    @Column(name = "PHONE", length = 20, precision = 0)
    private String phone;
    
    /**
     * 人员状态
     */
    private String operStatus;
    
    /**
     * 进场时间
     */
    private Timestamp inTime;
    
    /**
     * 退场时间
     */
    private Timestamp outTime;
    
    /**
     * 人员类型id
     */
    private String personTypeId;
    
    /**
     * 人员类型名称
     */
    private String personTypeName;
    
    /**
     * 项目部/班组ID
     */
    private String teamId;
    
    /**
     * 项目部/班组名称
     */
    private String teamName;
    
    /**
     * 证书信息列表
     */
    private List<OperCertificateVO> certificateList;
    
    /**
     * 履历列表
     */
    private List<OperResumeVO> resumeList;
    
    /**
     * 黑名单列表
     */
    private List<OperBlacklistVO> blacklists;
}

二、Mybatis代码

	<resultMap id="personnelDetailsMap" type="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.PersonnelDetailsVO">
        <id column="ID" property="id"/>
        <result column="OPERATE_NAME" property="operateName"/>
        <result column="UNIT_ID" property="unitId"/>
        <result column="UNIT_NAME" property="unitName"/>
        <result column="IDCARD" property="idcard"/>
        <result column="PHOTO_ATT" property="photoAtt"/>
        <result column="PHONE" property="phone"/>
        <result column="OPER_STATUS" property="operStatus"/>
        <result column="IN_TIME" property="inTime"/>
        <result column="OUT_TIME" property="outTime"/>
        <result column="PERSON_TYPE_ID" property="personTypeId"/>
        <result column="PERSON_TYPE_NAME" property="personTypeName"/>
        <result column="TEAM_ID" property="teamId"/>
        <result column="TEAM_NAME" property="teamName"/>
        <collection property="certificateList" ofType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperCertificateVO" javaType="list" column="id"
                    select="selectCertificateListByOperateId"/>
        <collection property="resumeList" ofType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperResumeVO" javaType="list" column="id"
                    select="selectResumeListByOperateId"/>
        <collection property="blacklists" ofType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperBlacklistVO" javaType="list" column="id"
                    select="selectBlacklistsByOperateId"/>
    </resultMap>

    <select id="queryPersonnelDetails" resultMap="personnelDetailsMap" parameterType="java.util.List">
        select T1.ID,
               T1.OPERATE_NAME,
               T1.UNIT_ID,
               T1.UNIT_NAME,
               T1.IDCARD,
               T1.PHOTO_ATT,
               T1.PHONE,
               T2.OPER_STATUS,
               T2.IN_TIME,
               T2.OUT_TIME,
               T2.PERSON_TYPE_ID,
               T2.PERSON_TYPE_NAME,
               T2.TEAM_ID,
               T2.TEAM_NAME
        from XMXL_WL_OPER_ITEM T1
            left join XMXL_WL_PRO_OPER T2 on T1.ID = T2.OPERATE_ID
        where
            T1.ID IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        order by T1.CREATE_TIME DESC
    </select>

    <select id="selectCertificateListByOperateId" resultType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperCertificateVO" parameterType="java.lang.String">
        select T1.ID                     AS id,
               T1.OPERATE_ID             AS operateId,
               T1.CERTIFICATE_ATT_ID    AS certificateAttId,
               T1.CERTIFICATE_TYPE      AS certificateType,
               T1.CERTIFICATE_NAME      AS certificateName,
               T1.CERTIFICATE_UNIT      AS certificateUnit,
               T1.ISSUANCE_DATE          AS issuanceDate,
               T1.EFFECTIVE_DATE         AS effectiveDate,
               T1.CREATOR_ID             AS creatorId,
               T1.UPDATOR_ID             AS updatorId,
               T1.CREATE_TIME            AS createTime,
               T1.UPDATE_TIME            AS updateTime
        from XMXL_WL_OPER_CERTIFICATE T1
        where T1.OPERATE_ID = #{id}
        order by T1.CREATE_TIME DESC
    </select>

    <select id="selectResumeListByOperateId" resultType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperResumeVO" parameterType="java.lang.String">
        select T1.ID                     AS id,
               T1.OPERATE_ID             AS operateId,
               T1.RESUME_IN_TIME         AS resumeInTime,
               T1.RESUME_OUT_TIME        AS resumeOutTime,
               T1.RESUME_PRO_NAME        AS resumeProName,
               T1.RESUME_SINGLE_PRO_NAME AS resumeSingleProName,
               T1.RESUME_PERSONNEL_TYPE  AS resumePersonnelType,
               T1.RESUME_PRO_STATUS      AS resumeProStatus,
               T1.CREATOR_ID             AS creatorId,
               T1.UPDATOR_ID             AS updatorId,
               T1.CREATE_TIME            AS createTime,
               T1.UPDATE_TIME            AS updateTime
        from XMXL_WL_OPER_RESUME T1
        where T1.OPERATE_ID = #{id}
        order by T1.CREATE_TIME DESC
    </select>

    <select id="selectBlacklistsByOperateId" resultType="com.mdxc.zhgd.overview.personnelmanage.personnelmanageapi.model.OperBlacklistVO" parameterType="java.lang.String">
        select T1.ID                          AS id,
               T1.OPERATE_ID                  AS operateId,
               T1.BLACKLIST_PRO_NAME          AS blacklistProName,
               T1.BLACKLIST_SINGLE_PRO_NAME   AS blacklistSingleProName,
               T1.BLACKLIST_BID_NAME          AS blacklistBidName,
               T1.KEEPOUT_DATE                AS keepoutDate,
               T1.KEEPOUT_EFFECTIVE_DATE      AS keepoutEffectiveDate,
               T1.KEEPOUT_REASON              AS keepoutReason,
               T1.CREATOR_ID                  AS creatorId,
               T1.UPDATOR_ID                  AS updatorId,
               T1.CREATE_TIME                 AS createTime,
               T1.UPDATE_TIME                 AS updateTime
        from XMXL_WL_OPER_BLACKLIST T1
        where T1.OPERATE_ID = #{id}
        order by T1.CREATE_TIME DESC
    </select>