mybites查询 对象包含对象List的方法 一对多方法sql的写法

Dao.xml中要定义这两个resultMap ，当然这property就是对象里的字段，这column就是表中的字段

<resultMap id="pageFile" type="com.***************.bo.page.PageFile">
        <id property="pageId" column="page_id" />
        <result property="path" column="path" />
        <result property="auditStatus" column="audit_status" />
        <result property="pageName" column="page_name" />
        <result property="createTime" column="create_time" />
        <result property="modifyTime" column="modify_time" />
        <result property="deviceType" column="device_type" />
        <result property="pageType" column="page_type" />
        <result property="actualUser" column="actual_user" />
        <result property="urlSuffix" column="url_suffix" />
        <collection property="auditRecordPageList" resultMap="auditRecordPageList" />
    </resultMap>

    <resultMap id="auditRecordPageList" type="com.s*****.l*******.c*****.bo.page.AuditRecordLog">
        <id property="id" column="id" />
        <result property="auditStatus" column="auditStatus" />
        <result property="auditor" column="auditor" />
        <result property="reason" column="reason" />
        <result property="auditTime" column="audit_time" />
        <result property="wordPath" column="word_path" />
        <result property="wordName" column="word_name" />
        <result property="version" column="version" />
        <result property="url" column="url" />
    </resultMap>

然后查询的时候该怎么查询就怎么查询，结果会自动封装滴此处的resultMap就是我们上边定义的resultMap注意此处是resultMap，不是resultType，下边例子中t_landingpage_pagefile 是一， t_landingpage_audit_log 是多，当然要注意一对多的分页写法，没有分页更好写，此处一对多分页用的子查询，我们要对一对多当中的一进行分页,如SQL中同色部分，当然如果有查询条件属于一对多当中多的那个表我也用了一个子查询解决的如下例中同色部分

如：

   <select id="queryAuditPageList" resultMap="pageFile" parameterType="com.sunlands.landpage.core.dto.LandPageQuery">
        select a.page_id, a.path,a.page_name,a.create_time,a.modify_time,a.audit_status,a.device_type,a.page_type,a.actual_user,
        b.id,b.audit_time,b.audit_status as auditStatus,b.reason,b.word_path,b.word_name,b.version
        from (select page_id, path,page_name,create_time,modify_time,audit_status,device_type,page_type,actual_user,delete_flag
      fromt_landingpage_pagefile where page_type=#{pageType} and audit_status=#{auditStatus} and delete_flag = 0
<include refid="auditListSql" />
limit #{pageStart},#{pageSize})a
left JOIN t_landingpage_audit_log bon a.page_id = b.page_id
<if test="modifyTime != null and modifyTime!=''">
left join (select page_id,max(audit_time) time from t_landingpage_audit_log group by page_id ) c on a.page_id=c.page_id
where 1=1
<if test="null!=modifyTime.start and modifyTime.start!=''">
<![CDATA[and c.time>=#{modifyTime.start}]]>
</if>
<if test="null!=modifyTime.end and modifyTime.end!=''">
<![CDATA[and c.time<DATE_ADD(#{modifyTime.end},INTERVAL 1 DAY)]]>
</if>
</if>
order by b.audit_time desc
    </select> 

此处顺便说一下上例中的<include refid="auditListSql" />，这个只是为了便于查看对查询列表条件进行的公共部分提取，这样查询数量的selectCount，和查询实体的两个SQL公用一套查询条件，这样修改的时候只需要改一个地方就行啦：图中同色部分是不同角色的查询条件，属于权限管理

提取部分如下

<sql id="auditListSql">
<if test="userDetails.authTree.contains('FUN**************_LIST')">
AND (actual_user LIKE CONCAT('%','${userDetails.email}','%')
or creator LIKE CONCAT('%','${userDetails.email}','%'))
AND bu_id = #{userDetails.buId}
</if>
<if test="userDetails.authTree.contains('FUN*******UDIT_LIST')">
AND bu_id = #{userDetails.buId}
</if>
<if test="null!=pageId and pageId!=''">
and  page_id = #{pageId}
</if>
<if test="null!=pageName and pageName!=''">
and page_name LIKE CONCAT('%','${pageName}','%')
</if>
<if test="null!=actualUser and actualUser!=''">
and actual_user LIKE CONCAT('%','${actualUser}','%')
</if>
<if test="createTime != null">
<if test="null!=createTime.start and createTime.start!=''">
<![CDATA[and create_time>=#{createTime.start}]]>
</if>
<if test="null!=createTime.end and createTime.end!=''">
<![CDATA[and create_time<DATE_ADD(#{createTime.end},INTERVAL 1 DAY)]]>
</if>
</if>
</sql>