Mybatis-pagehelper在一对多查询的问题

mybatis一对多查询使用pagehelper发生错误问题

问题描述

做项目使用了mybatis框架的分页助手，在多表查询，而且又是一对多的查询，我使用了左连接，然后就发现返回的记录总数并不是我想要的正确数目。

解决办法

在xml文件中进行修改 在该映射中嵌套一个子查询

具体例子

分页显示数目不正确的案例

<select id="queryMyOrdersNotUse" parameterType="Map" resultMap="myOrdersVO">
        SELECT
        od.id as orderId,
        od.created_time as createdTime,
        od.pay_method as payMethod,
        od.real_pay_amount as realPayAmount,
        od.post_amount as postAmount,
        os.order_status as orderStatus,
        oi.item_id as itemId,
        oi.item_name as itemName,
        oi.item_img as itemImg,
        oi.item_spec_id as itemSpecId,
        oi.item_spec_name as itemSpecName,
        oi.buy_counts as buyCounts,
        oi.price as price
        FROM
        orders od
        LEFT JOIN
        order_status os
        on od.id = os.order_id
        LEFT JOIN
        order_items oi
        ON
        od.id = oi.order_id
        WHERE
        od.user_id = #{paramsMap.userId}

        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>

正确改法如下：

<select id="queryMyOrders" parameterType="Map" resultMap="myOrdersVO">
        SELECT
        od.id as orderId,
        od.created_time as createdTime,
        od.pay_method as payMethod,
        od.real_pay_amount as realPayAmount,
        od.post_amount as postAmount,
        os.order_status as orderStatus,
        od.is_comment as isComment
        FROM
        orders od
        LEFT JOIN
        order_status os
        on od.id = os.order_id
        WHERE
        od.user_id = #{paramsMap.userId}
        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>
    <select id="getSubItems" parameterType="String" resultType="com.imooc.pojo.vo.MySubOrderItemVO">
        SELECT
            oi.item_id        AS itemId,
            oi.item_name      AS itemName,
            oi.item_img       AS itemImg,
            oi.item_spec_id   AS itemSpecId,
            oi.item_spec_name AS itemSpecName,
            oi.buy_counts     AS buyCounts,
            oi.price          AS price
        FROM
            order_items oi
        WHERE
            oi.order_id = #{orderId}
    </select>

<resultMap id="myOrdersVO" type="com.imooc.pojo.vo.MyOrderVO">
        <id column="orderId" property="orderId"/>
        <result column="createdTime" property="createdTime"/>
        <result column="payMethod" property="payMethod"/>
        <result column="realPayAmount" property="realPayAmount"/>
        <result column="postAmount" property="postAmount"/>
        <result column="orderStatus" property="orderStatus"/>
        <result column="isComment" property="isComment"/>
        <collection property="subOrderItemList"
                    select="getSubItems"
                    column="orderId" 
                // 联合查询两张表之间的关联条件，也就是sql语句中的on条件
                    ofType="com.imooc.pojo.vo.MySubOrderItemVO">
            <result column="itemId" property="itemId"/>
            <result column="itemName" property="itemName"/>
            <result column="itemImg" property="itemImg"/>
            <result column="itemSpecName" property="itemSpecName"/>
            <result column="buyCounts" property="buyCounts"/>
            <result column="price" property="price"/>
        </collection>
    </resultMap>

import java.util.Date;
import java.util.List;

public class MyOrderVO {
    private String orderId;
    private Date createdTime;
    private Integer payMethod;
    private Integer realPayAmount;
    private Integer postAmount;
    private Integer isComment;
    private Integer orderStatus;
    private List<MySubOrderItemVO> subOrderItemList;

    public String getOrderId() {
        return orderId;
    }

    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }

    public Date getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(Date createdTime) {
        this.createdTime = createdTime;
    }

    public Integer getPayMethod() {
        return payMethod;
    }

    public void setPayMethod(Integer payMethod) {
        this.payMethod = payMethod;
    }

    public Integer getRealPayAmount() {
        return realPayAmount;
    }

    public void setRealPayAmount(Integer realPayAmount) {
        this.realPayAmount = realPayAmount;
    }

    public Integer getPostAmount() {
        return postAmount;
    }

    public void setPostAmount(Integer postAmount) {
        this.postAmount = postAmount;
    }

    public Integer getIsComment() {
        return isComment;
    }

    public void setIsComment(Integer isComment) {
        this.isComment = isComment;
    }

    public Integer getOrderStatus() {
        return orderStatus;
    }

    public void setOrderStatus(Integer orderStatus) {
        this.orderStatus = orderStatus;
    }

    public List<MySubOrderItemVO> getSubOrderItemList() {
        return subOrderItemList;
    }

    public void setSubOrderItemList(List<MySubOrderItemVO> subOrderItemList) {
        this.subOrderItemList = subOrderItemList;
    }
}

public class MySubOrderItemVO {

    private String itemId;
    private String itemImg;
    private String itemName;
    private String itemSpecName;
    private Integer buyCounts;
    private Integer price;

    public String getItemId() {
        return itemId;
    }

    public void setItemId(String itemId) {
        this.itemId = itemId;
    }

    public String getItemImg() {
        return itemImg;
    }

    public void setItemImg(String itemImg) {
        this.itemImg = itemImg;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public String getItemSpecName() {
        return itemSpecName;
    }

    public void setItemSpecName(String itemSpecName) {
        this.itemSpecName = itemSpecName;
    }

    public Integer getBuyCounts() {
        return buyCounts;
    }

    public void setBuyCounts(Integer buyCounts) {
        this.buyCounts = buyCounts;
    }

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }
}

参考链接：
blog.csdn.net/egbertasd/a…