pageHelper只会对紧跟的一条sql有效

pageHelper只会对紧跟的一条sql有效

问题：mybatis的结果集可以帮助我们组装查询对象，如果结果集包含list属性，那么实际sql查询出来的结果会是m * n条，这样这条语句直接使用pageHelper插件实际分页的就是m * n的结果。多数情况不满足业务需求

    <resultMap id="inventoryDtoResultMap" type="com.local.baiyuan.bean.dto.InventoryDto">
        <id property="id" column="id" />
        <result property="availableInventory" column="availableInventory" />
        <result property="roadInventory" column="roadInventory" />
        <result property="loadingInventory" column="loadingInventory" />
        <result property="date" column="date" />
        <result property="skuId" column="skuId" />

        <collection property="inventoryExcels" ofType="com.local.baiyuan.bean.InventoryExcel">
            <result property="sku" column="sku" />
            <result property="brandName" column="brandName" />
            <result property="warehouseName" column="warehouseName" />
            <result property="availableInventory" column="availableInventory" />
            <result property="roadInventory" column="roadInventory" />
            <result property="loadingInventory" column="loadingInventory" />
            <result property="statisticalDate" column="statisticalDate" />
            <result property="importRecordId" column="importRecordId" />
            <result property="opsType" column="opsType" />
        </collection>
    </resultMap>

 

解决：

1(推荐)

可以用两条sql，第一条用来分页找到需要分页的主表id，第二条用来查询全量数据，让mybatis帮助我们完成组装

   <select id="selectPageSku" resultType="string">
        SELECT s.sku
        FROM inventory i
        INNER JOIN sku s
        ON i.sku_id = s.id
        <where>
            <if test="startDate != null and startDate != ''">
                AND i.date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != ''">
                AND i.date &lt;= #{endDate}
            </if>
            <if test="sku != null and sku != ''">
                AND s.sku = #{sku}
            </if>
        </where>
    </select>

    <select id="selectInventoryDtos" resultMap="inventoryDtoResultMap">
        SELECT
            i.id,
            i.available_inventory availableInventory,
            i.road_inventory roadInventory,
            i.loading_inventory loadingInventory,
            i.date,
            i.sku_id skuId,
            ie.sku,
            ie.brand_name brandName,
            ie.warehouse_name warehouseName,
            ie.available_inventory availableInventory,
            ie.road_inventory roadInventory,
            ie.loading_inventory loadingInventory,
            ie.statistical_date statisticalDate,
            ie.import_record_id importRecordId,
            ie.ops_type opsType
        FROM inventory i
        INNER JOIN sku s
        ON i.sku_id = s.id
        LEFT JOIN inventory_excel ie
        ON s.sku = ie.sku
        WHERE s.sku IN
        <foreach collection="skus" item="skuItem" separator="," open="(" close=")">
            #{skuItem}
        </foreach>
        ORDER BY i.date DESC

    </select>

public PageInfo<InventoryDto> queryDtos(InventoryDto inventoryDto) {
    PageHelper.startPage(inventoryDto.getPageNum(), inventoryDto.getPageSize());
    List<String> skus = inventoryMapper.selectPageSku(inventoryDto);

    inventoryDto.setSkus(skus);
    return new PageInfo<>(inventoryMapper.selectInventoryDtos(inventoryDto));
}