mybatis的conllection+orcale+多表关联+Ipage分页查询结果问题的解决(column)

一.问题描述

leftjoin多表关联查询时,当使用了mybatis的conllection方式,将关联表的结果集转为了list,在使用Ipage分页查询的时候,会出现下面两种错误情况:
1.总条数不对:数据库查出的条数是A,mybatis的conllection查出的数据是B,而B是left 左侧的表查出的记录总数
2.当前页查询的记录数不对:例如:你希望当前页展示100条数据,结果查询出的条数少于100,是因为mybatis的conllection将left 右侧的表转为了list
我遇到的是问题2,当前页展示的结果不对

二.解决方法:使用conllection的column进行多表关联

column的用法：

<collection property="javaBean的list名称" 
			ofType="List映射的javaBean"
			column="{随便起变量名1=数据库列1,随便起变量名2=数据库列2}"
            javaType="ArrayList"
            select="关联的sql的id">
    </collection>

然后多表之间的关联关系用随便起变量名1和随便起变量名2去关联

三.错误截图

四.未修改前的写法(ipage分页结果不正确)

    <resultMap type="com.dto.NonClientInfoResponseDto" id="nonClientInfoTodoMap">
        <result property="clientId" column="CLIENT_ID" jdbcType="VARCHAR"/>
        <result property="systemId" column="SYSTEM_ID" jdbcType="VARCHAR"/>
        <result property="clientUnicode" column="CLIENT_UNICODE" jdbcType="VARCHAR"/>
        <result property="relevanceId" column="RELEVANCE_ID" jdbcType="VARCHAR"/>
        <collection property="rmDcClientKeywordLogEntityList"
                    ofType="com.entity.RmDcClientKeywordLogEntity">
            <result property="logId" column="LOG_ID" jdbcType="VARCHAR"/>
            <result property="clientId" column="CLIENT_ID" jdbcType="VARCHAR"/>
            <result property="systemId" column="SYSTEM_ID" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="selectNonClientInfoAndChanged" resultMap="nonClientInfoTodoMap">
        select a.*,b.* from RM_CLIENT_INFO_TODO a
        LEFT JOIN (
        select LOG_ID,CLIENT_ID,SYSTEM_ID,KEYWORD,OLD_VALUES,CREATEBY,CREATETIME from
        (select row_number() over(partition by t.KEYWORD order by t.CREATETIME desc) as rn,
        t.*
        from RM_DC_CLIENT_KEYWORD_LOG t, RM_CLIENT_INFO_TODO c
        where t.client_id = c.CLIENT_ID
        and t.system_id = c.SYSTEM_ID
        order by t.keyword, t.createtime) where rn=1
        ) b on a.CLIENT_ID=b.CLIENT_ID AND a.SYSTEM_ID=b.SYSTEM_ID
        where a.IS_OPEN_CLIENT=#{isOldOpen}
        <if test="startDate!= null and startDate != '' ">
            AND a.createtime > #{startDate}
        </if>
        <if test="queryName!= null and queryName != '' ">
            AND (
            a.CLIENT_NAME like '%' || #{queryName} || '%' OR
            a.CLIENT_UNICODE like '%' || #{queryName} || '%' OR
            a.CLIENT_FULL_NAME like '%' || #{queryName} || '%'
            )
        </if>
    </select>

五.修改后的写法(ipage分页结果正确)

 <resultMap type="com.dto.NonClientInfoResponseDto" id="nonClientInfoTodoMapByPage">
        <result property="clientId" column="CLIENT_ID" jdbcType="VARCHAR"/>
        <result property="systemId" column="SYSTEM_ID" jdbcType="VARCHAR"/>
        <result property="clientUnicode" column="CLIENT_UNICODE" jdbcType="VARCHAR"/>
        <result property="relevanceId" column="RELEVANCE_ID" jdbcType="VARCHAR"/>
        <collection property="rmDcClientKeywordLogEntityList"
                    ofType="com.entity.RmDcClientKeywordLogEntity"
                    javaType="ArrayList" 
                    select="leftjoin" 
                    column="{CLIENT_ID=CLIENT_ID,SYSTEM_ID=SYSTEM_ID}">
        </collection>
    </resultMap>

    <resultMap id="rmDcClientKeywordLogEntityMap" type="com.entity.RmDcClientKeywordLogEntity">
        <result property="logId" column="LOG_ID" jdbcType="VARCHAR"/>
        <result property="clientId" column="keyClientId" jdbcType="VARCHAR"/>
        <result property="systemId" column="keySystemId" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="leftjoin" resultMap="rmDcClientKeywordLogEntityMap">
       select LOG_ID,CLIENT_ID as keyClientId,SYSTEM_ID as keySystemId,KEYWORD,OLD_VALUES,CREATEBY as keyCreateBy,CREATETIME as keyCreateTime
         from ( select row_number() over(partition by t.KEYWORD order by t.CREATETIME desc) as rn, t.*
            from RM_DC_CLIENT_KEYWORD_LOG t, RM_CLIENT_INFO_TODO c
            where t.client_id = c.CLIENT_ID
            and t.system_id =c.SYSTEM_ID
            order by t.keyword, t.createtime )
        where rn=1 
        # 下面这句是关联关系
        and CLIENT_ID =#{CLIENT_ID} and SYSTEM_ID =#{SYSTEM_ID}
    </select>

    <select id="selectNonClientInfoAndChangedByPage" resultMap="nonClientInfoTodoMapByPage">
        select a.* from RM_CLIENT_INFO_TODO a
        where a.IS_OPEN_CLIENT=#{isNonOpen}
        <if test="startDate!= null and startDate != '' ">
            AND a.createtime > #{startDate}
        </if>
        <if test="queryName!= null and queryName != '' ">
            AND (
            a.CLIENT_NAME like '%' || #{queryName} || '%' OR
            a.CLIENT_UNICODE like '%' || #{queryName} || '%' OR
            a.CLIENT_FULL_NAME like '%' || #{queryName} || '%'
            )
        </if>
    </select>

== 需要注意的是,
1.此种方法改写的sql,查询所有效率较低,分页查询效率还可以;
2.至于改写的方法,对照我上面的案例即可 ,
3.我将变量名与列名起的相同 ==