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带过去就行了。