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_NAMEfrom XMXL_WL_OPER_ITEM T1left join XMXL_WL_PRO_OPER T2 on T1.ID = T2.OPERATE_IDwhereT1.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 updateTimefrom XMXL_WL_OPER_CERTIFICATE T1where 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 updateTimefrom XMXL_WL_OPER_RESUME T1where 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 updateTimefrom XMXL_WL_OPER_BLACKLIST T1where T1.OPERATE_ID = #{id}order by T1.CREATE_TIME DESC</select>