Remember the problems encountered when using the MyBatis paging plug-in

Problem description

in use Mybatis-PageHelper When paging, the result set queried is a nested result. At this time, it is found that the number of records queried is obviously inconsistent with the set pagesize.

Problem restore

This is what my mapper implementation wrote,

  <resultMap id="myOrdersVo" type="com.cjervin.vo.MyOrdersVO">
    <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" ofType="com.cjervin.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>

  <select id="queryMyOrderList" parameterType="map" resultMap="myOrdersVo">
      SELECT
          o.id AS orderId,
          o.created_time AS createdTime,
          o.pay_method AS payMethod,
          o.real_pay_amount AS realPayAmount,
          o.post_amount AS postAmount,
          os.order_status AS orderStatus,
          o.is_comment AS isComment,
          oi.item_id AS itemId,
          oi.item_name AS itemName,
          oi.item_img AS itemImg,
          oi.item_spec_name AS itemSpecName,
          oi.buy_counts AS buyCounts,
          oi.price AS price
      FROM orders o
      LEFT JOIN order_status os ON o.id = os.order_id
      LEFT JOIN order_items oi ON o.id = oi.order_id
      WHERE o. user_id = #{paramMap.userId}
      AND o.is_delete = 0
      <if test="paramMap.orderStatus != null">
        AND os.order_status = #{paramMap.orderStatus}
      </if>
      ORDER BY o.updated_time ASC
  </select>

MyOrdersVO:

public class MyOrdersVO {

    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;

MySubOrderItemVO:

public class MySubOrderItemVO {

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

As you can see, a collection sub result set is nested under a resultMap called myOrdersVo configured in xml.

servcice:

    @Override
    public PagedGridResult queryMyOrders(String userId, Integer orderStatus, Integer page, Integer pageSize) {
        Map<String,Object> map = new HashMap<>();
        map.put("userId",userId);
        if (orderStatus != null) {
            map.put("orderStatus", orderStatus);
        }
        PageHelper.startPage(page,pageSize);
        List<MyOrdersVO> orderList = ordersMapperCustom.queryMyOrderList(map);
        return setterPagedGrid(orderList,page);
    }
    public PagedGridResult setterPagedGrid(List<?> list, Integer page) {
        PageInfo<?> pageList = new PageInfo<>(list);
        PagedGridResult grid = new PagedGridResult();
        grid.setPage(page);
        grid.setRows(list);
        grid.setTotal(pageList.getPages());
        grid.setRecords(pageList.getTotal());
        return grid;
    }

controller:

    @PostMapping("/query")
    public JSONResult query(@RequestParam("userId") String userId,@RequestParam("orderStatus")Integer orderStatus,
                            @RequestParam(value ="page",defaultValue = "1")Integer page,
                            @RequestParam(value ="pageSize",defaultValue = "10")Integer pageSize){

        PagedGridResult pagedGridResult = myOrderService.queryMyOrders(userId, orderStatus, page, pageSize);

        return JSONResult.ok(pagedGridResult);
    }

Then use postman to test,

We can see that there are only three returned results, which obviously deviate from the 4 passed in before, but after expansion, if it is calculated according to the number of sub result sets, it can be aligned up

This is not in line with our expectations. What we hope is that the parent result set can show 4!!

Problem solving:

Mybatis-PageHelper Host the code in github. Let's see if there is a solution. Here is a screenshot:

The paging plug-in doesn't support nested result mapping, so we have to find our own way

Since one select is not good, we should extract the nested part and query twice to solve the problem

  <resultMap id="myOrdersVo" type="com.cjervin.vo.MyOrdersVO">
    <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" ofType="com.cjervin.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>

    <select id="getSubItems" parameterType="string" resultType="com.cjervin.vo.MySubOrderItemVO">
      select
        oi.item_id as itemId,
        oi.item_name as itemName,
        oi.item_img as itemImg,
        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>

  <select id="queryMyOrderList" parameterType="map" resultMap="myOrdersVo">
      SELECT
          o.id AS orderId,
          o.created_time AS createdTime,
          o.pay_method AS payMethod,
          o.real_pay_amount AS realPayAmount,
          o.post_amount AS postAmount,
          os.order_status AS orderStatus,
          o.is_comment AS isComment
      FROM orders o
      LEFT JOIN order_status os ON o.id = os.order_id
      WHERE o. user_id = #{paramMap.userId}
      AND o.is_delete = 0
      <if test="paramMap.orderStatus != null">
        AND os.order_status = #{paramMap.orderStatus}
      </if>
      ORDER BY o.updated_time ASC
  </select>

We can nest a query in a query with the select tag of the collection tag, and pass a value through the column tag of the collection tag. You can solve the above problems, and then test:

Problem solving!

Tags: Programming Mybatis xml github

Posted on Tue, 05 May 2020 07:44:04 -0400 by creativeimpact