PageHelper在對mybatis一對多分頁不正確的問題

1. 其實這個問題仔細一想也沒什么

1.首先使用PageHelper單表沒有問題
2.連表查詢不牽扯到一對多也沒有問題
3.連表查詢牽扯到一對多分頁錯誤：

比如：查第一頁，查詢10條資料，卻只得到三條，因為那七條資料mybatis進行了一對多映射，一個對七個，所以只有三條資料了

原sql

<select id="sparePartsManagement" resultMap="sparePartsManagementResultMap">
        select r.SAP_NO rSAP_NO,
        s.CONSIGNEE sCONSIGNEE,s.SHIPPING_ADDRESS sSHIPPING_ADDRESS,s.COMPANY sCOMPANY,
        s.ID sID,s.PHONE sPHONE,s.DESR sDESR,
        e.EXPRESS_NO eEXPRESS_NO,e.ID eID
        from spares_requirement s
        left join repair_product r on  r.ID = s.REPAIR_PRODUCT_ID and r.IS_DEL = 0
        left join express e on  r.ID = e.REPAIR_PRODUCT_ID and e.IS_DEL = 0
        <where>
            s.IS_DEL = 0
<!--            and r.IS_DEL = 0 and e.IS_DEL = 0-->
            <if test="consignee != null and consignee != ''">
                and s.CONSIGNEE = #{consignee}
            </if>
            <if test="sapNo != null and sapNo != ''">
                and r.SAP_NO = #{sapNo}
            </if>
            <if test="expressNo != null and expressNo != ''">
                and e.EXPRESS_NO = #{expressNo}
            </if>
        </where>
    </select>
    <resultMap id="sparePartsManagementResultMap" type="com.santank.domain.vo.VoSparePartsManagement">
        <result property="id" column="sID"/>
        <result property="sapNo" column="rSAP_NO"/>
        <result property="consignee" column="sCONSIGNEE"/>
        <result property="shippingAddress" column="sSHIPPING_ADDRESS"/>
        <result property="company" column="sCOMPANY"/>
        <result property="phone" column="sPHONE"/>
        <result property="desr" column="sDESR"/>
        <collection property="expressList" ofType="com.santank.domain.vo.VoExpress">
            <result property="id" column="eID"/>
            <result property="expressNo" column="eEXPRESS_NO"/>
        </collection>
    </resultMap>

因為PageHelper默認查詢下面的第一條資料，因為collection進行了一對多映射，所有資料分頁不對，所有我們將collection sql部分拆開來，讓collection去查另外一個sql，然后在對應相應的resultMap即可，

因為collection導致的錯誤，所有我們將collection部分拆開單獨寫sql，sql需要的連接其他表的欄位值我們帶過來即可

新的寫法

<select id="selectExpressRepair_product" resultMap="expressRepair_productMap">
    select
    e.EXPRESS_NO eEXPRESS_NO,e.ID eID
    from  express e
    where e.REPAIR_PRODUCT_ID = #{id}
</select>

<select id="sparePartsManagement" resultMap="sparePartsManagementResultMap">
    select r.SAP_NO rSAP_NO,r.ID rID,s.CONSIGNEE sCONSIGNEE,s.SHIPPING_ADDRESS sSHIPPING_ADDRESS,s.COMPANY sCOMPANY,
    s.ID sID,s.PHONE sPHONE,s.DESR sDESR
    from repair_product r
    left join spares_requirement s on  r.ID = s.REPAIR_PRODUCT_ID and r.IS_DEL = 0
    <where>
        r.IS_DEL = 0
        <if test="consignee != null and consignee != ''">
            and s.CONSIGNEE = #{consignee}
        </if>
        <if test="sapNo != null and sapNo != ''">
            and r.SAP_NO = #{sapNo}
        </if>
       <!-- <if test="expressNo != null and expressNo != ''">
            and e.EXPRESS_NO = #{expressNo}
        </if>-->
    </where>
</select>
<resultMap id="sparePartsManagementResultMap" type="com.santank.domain.vo.VoSparePartsManagement">
    <result property="id" column="rID"/>
    <result property="sapNo" column="rSAP_NO"/>
    <result property="consignee" column="sCONSIGNEE"/>
    <result property="shippingAddress" column="sSHIPPING_ADDRESS"/>
    <result property="company" column="sCOMPANY"/>
    <result property="phone" column="sPHONE"/>
    <result property="desr" column="sDESR"/>
    <collection property="expressList" ofType="com.santank.domain.vo.VoExpress" select="selectExpressRepair_product" column="rID">
       <!-- <result property="id" column="eID"/>
        <result property="expressNo" column="eEXPRESS_NO"/>-->
    </collection>
</resultMap>
<resultMap id="expressRepair_productMap" type="com.santank.domain.vo.VoExpress">
    <result property="id" column="eID"/>
    <result property="expressNo" column="eEXPRESS_NO"/>
</resultMap>

PageHelper分頁出現問題主要就是mybatis一對多出現問題，連表查詢并不會出現分頁錯誤，但是連表查詢牽扯到一對多就會出現分頁錯誤，所以我們只要將collection部分單獨拆出來寫sql就行了，就像上面的寫法即可，需要連接的欄位值引數使用column帶過去就行了，