使用PageHelper分页嵌套查询结果映射导致的BUG

image.png
image.png
image.png
我们在查询订单的时候是按规格查询出来，最后根据orderId合并成一个完整的订单，也就是我们查询的是规格，之后嵌套进了订单中，而我们在显示的时候应该显示的是订单条数，每页也应该根据订单来显示10条订单数据，但现在PageHelper却是以规格数来进行统计。
解决方案：
一：先使用订单ID查询出所有订单，再对订单中的每一条商品信息进行查询。
二：PageHelper内置了方法来解决

  1. <resultMap id="myOrdersVO" type="com.shiers.pojo.vo.MyOrdersVO">
  2.   <id column="orderId" property="orderId"/>
  3.   <result column="createdTime" property="createdTime"/>
  4.   <result column="payMethod" property="payMethod"/>
  5.   <result column="realPayAmount" property="realPayAmount"/>
  6.   <result column="postAmount" property="postAmount"/>
  7.   <result column="orderStatus" property="orderStatus"/>
  8.   <result column="isComment" property="isComment"/>
  10.   ================================👇👇👇嵌套👇👇👇================================
  11.   <collection property="subOrderItemList" 
  12.               =======👇👇👇新增加的解决方案👇👇👇=======
  13.               select="getSubItems"   👈这里对这个对象属性进行查询，定义查询方法名
  14.               column="orderId"       👈传入参数
  15.               =======👆👆👆新增加的解决方案👆👆👆=======
  16.               ofType="com.shiers.pojo.vo.MySubOrderItemVO">
  17.     <result column="itemId" property="itemId"/>
  18.     <result column="itemName" property="itemName"/>
  19.     <result column="itemImg" property="itemImg"/>
  20.     <result column="itemSpecName" property="itemSpecName"/>
  21.     <result column="buyCounts" property="buyCounts"/>
  22.     <result column="price" property="price"/>
  23.   </collection>
  24.   ================================👆👆👆嵌套👆👆👆================================
  25. </resultMap>
  27.   ==================👇👇👇SQL将不包含规格表，只查询了订单👇👇👇====================
  28. <select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map">
  29.   SELECT
  30.   od.id AS orderId,
  31.   od.created_time AS createdTime,
  32.   od.pay_method AS payMethod,
  33.   od.real_pay_amount AS realPayAmount,
  34.   od.post_amount AS orderStatus,
  35.   os.order_status AS orderStatus
  36.   FROM
  37.   orders od
  38.   LEFT JOIN
  39.   order_status os
  40.   ON
  41.   od.id = os.order_id
  42.   WHERE
  43.   od.user_id = #{paramsMap.userId}
  44.   AND
  45.   od.is_delete = 0
  46.   <if test="paramsMap.orderStatus != null">
  47.     AND os.order_status = #{paramsMap.orderStatus}
  48.   </if>
  49.   ORDER BY
  50.   od.updated_time ASC
  51. </select>
  52.   =============👇👇👇根据订单表的id查询对应规格表，填充到属性中👇👇👇==============
  53. <select id="getSubItems" parameterType="String" resultType="com.shiers.pojo.vo.MySubOrderItemVO">
  54.   select
  55.   oi.item_id AS itemId,
  56.   oi.item_name AS itemName,
  57.   oi.item_img AS itemImg,
  58.   oi.item_spec_name AS itemSpecName,
  59.   oi.buy_counts AS buyCounts,
  60.   oi.price AS price
  61.   from order_items oi 
  62.   where oi.order_id = #{orderId}
  63. </select>

Controller

  1. package com.shiers.controller.center;
  3. import com.shiers.controller.BaseController;
  4. import com.shiers.pojo.Orders;
  5. import com.shiers.service.center.MyOrdersService;
  6. import com.shiers.utils.MyJSONResult;
  7. import com.shiers.utils.PagedGridResult;
  8. import io.swagger.annotations.Api;
  9. import io.swagger.annotations.ApiOperation;
  10. import io.swagger.annotations.ApiParam;
  11. import org.apache.commons.lang3.StringUtils;
  12. import org.springframework.beans.factory.annotation.Autowired;
  13. import org.springframework.http.HttpStatus;
  14. import org.springframework.web.bind.annotation.*;
  16. /**
  17.  * Demo class
  18.  *
  19.  * @author shierS
  20.  * @date 2021/6/6
  21.  */
  23. @Api(value = "用户中心我的订单", tags = {"用户中心我的订单相关接口", "巴拉巴拉"})
  24. @RestController
  25. @RequestMapping("myorders")
  26. public class MyOrdersController extends BaseController {
  28.     @Autowired
  29.     private MyOrdersService myOrdersService;
  31.     @ApiOperation(value = "查询订单列表", notes = "查询订单列表", httpMethod = "POST")
  32.     @PostMapping("query")
  33.     public MyJSONResult query(
  34.         @ApiParam(name = "userId", value = "用户id", required = true)
  35.         @RequestParam String userId,
  36.         @ApiParam(name = "orderStatus", value = "订单状态", required = false)
  37.         @RequestParam Integer orderStatus,
  38.         @ApiParam(name = "page", value = "查询第几页", required = false)
  39.         @RequestParam Integer page,
  40.         @ApiParam(name = "pageSize", value = "分页的每一页显示条数", required = false)
  41.         @RequestParam Integer pageSize) {
  43.         System.out.println("xxxx");
  44.         if (StringUtils.isBlank(userId)) {
  45.             return MyJSONResult.errorMsg(null);
  46.         }
  48.         if (page == null) {
  49.             page = 1;
  50.         }
  51.         if (pageSize == null) {
  52.             pageSize = COMMEN_PAGE_SIZE;
  53.         }
  55.         PagedGridResult grid = myOrdersService.queryMyOrders(userId, orderStatus, page, pageSize);
  58.         return MyJSONResult.ok(grid);
  60.     }
  63.     // 商家发货没有后端，所以这个接口仅仅只是用于模拟
  64.     @ApiOperation(value = "商家发货", notes = "商家发货", httpMethod = "GET")
  65.     @GetMapping("/deliver")
  66.     public MyJSONResult deliver(
  67.         @ApiParam(name = "orderId", value = "订单id", required = true)
  68.         @RequestParam String orderId) throws Exception {
  70.         if (StringUtils.isBlank(orderId)) {
  71.             return MyJSONResult.errorMsg("订单ID不能为空");
  72.         }
  73.         myOrdersService.updateDeliverOrderStatus(orderId);
  74.         return MyJSONResult.ok();
  75.     }
  78.     @ApiOperation(value = "用户确认收货", notes = "用户确认收货", httpMethod = "POST")
  79.     @PostMapping("/confirmReceive")
  80.     public MyJSONResult confirmReceive(
  81.         @ApiParam(name = "orderId", value = "订单id", required = true)
  82.         @RequestParam String orderId,
  83.         @ApiParam(name = "userId", value = "用户id", required = true)
  84.         @RequestParam String userId) throws Exception {
  86.         MyJSONResult checkResult = checkUserOrder(userId, orderId);
  87.         if (checkResult.getStatus() != HttpStatus.OK.value()) {
  88.             return checkResult;
  89.         }
  91.         boolean res = myOrdersService.updateReceiveOrderStatus(orderId);
  92.         if (!res) {
  93.             return MyJSONResult.errorMsg("订单确认收货失败！");
  94.         }
  96.         return MyJSONResult.ok();
  97.     }
  99.     @ApiOperation(value = "用户删除订单", notes = "用户删除订单", httpMethod = "POST")
  100.     @PostMapping("/delete")
  101.     public MyJSONResult delete(
  102.         @ApiParam(name = "orderId", value = "订单id", required = true)
  103.         @RequestParam String orderId,
  104.         @ApiParam(name = "userId", value = "用户id", required = true)
  105.         @RequestParam String userId) throws Exception {
  107.         MyJSONResult checkResult = checkUserOrder(userId, orderId);
  108.         if (checkResult.getStatus() != HttpStatus.OK.value()) {
  109.             return checkResult;
  110.         }
  112.         boolean res = myOrdersService.deleteOrder(userId, orderId);
  113.         if (!res) {
  114.             return MyJSONResult.errorMsg("订单删除失败！");
  115.         }
  117.         return MyJSONResult.ok();
  118.     }
  120.     /**
  121.      * 用于验证用户和订单是否有关联关系，避免非法用户调用
  122.      *
  123.      * @return
  124.      */
  125.     private MyJSONResult checkUserOrder(String userId, String orderId) {
  126.         Orders order = myOrdersService.queryMyOrder(userId, orderId);
  127.         if (order == null) {
  128.             return MyJSONResult.errorMsg("订单不存在！");
  129.         }
  130.         return MyJSONResult.ok();
  131.     }
  133. }

Service

  1. package com.shiers.service.center;
  3. import com.shiers.pojo.Orders;
  4. import com.shiers.utils.PagedGridResult;
  6. /**
  7.  * Demo class
  8.  *
  9.  * @author shierS
  10.  * @date 2021/6/6
  11.  */
  12. public interface MyOrdersService {
  14.     /**
  15.      * 查询我的订单列表
  16.      * @param userId
  17.      * @param orderStatus
  18.      * @param page
  19.      * @param pageSize
  20.      * @return
  21.      */
  22.     public PagedGridResult queryMyOrders(String userId,
  23.                                          Integer orderStatus,
  24.                                          Integer page,
  25.                                          Integer pageSize);
  27.     /**
  28.      * @Description: 订单状态 --> 商家发货
  29.      */
  30.     public void updateDeliverOrderStatus(String orderId);
  32.     /**
  33.      * 查询我的订单，用于用户确认订单时，判断用户id和订单id是否相互关联
  34.      *
  35.      * @param userId
  36.      * @param orderId
  37.      * @return
  38.      */
  39.     public Orders queryMyOrder(String userId, String orderId);
  41.     /**
  42.      * 更新订单状态 —> 确认收货
  43.      *
  44.      * @return
  45.      */
  46.     public boolean updateReceiveOrderStatus(String orderId);
  48.     /**
  49.      * 删除订单（逻辑删除）
  50.      * @param userId
  51.      * @param orderId
  52.      * @return
  53.      */
  54.     public boolean deleteOrder(String userId, String orderId);
  56. }

ServiceImpl

  1. package com.shiers.service.impl.center;
  3. import com.github.pagehelper.PageHelper;
  4. import com.github.pagehelper.PageInfo;
  5. import com.shiers.enums.OrderStatusEnum;
  6. import com.shiers.enums.YesOrNo;
  7. import com.shiers.mapper.OrderStatusMapper;
  8. import com.shiers.mapper.OrdersMapper;
  9. import com.shiers.mapper.OrdersMapperCustom;
  10. import com.shiers.pojo.OrderStatus;
  11. import com.shiers.pojo.Orders;
  12. import com.shiers.pojo.vo.MyOrdersVO;
  13. import com.shiers.service.center.MyOrdersService;
  14. import com.shiers.utils.PagedGridResult;
  15. import org.springframework.beans.factory.annotation.Autowired;
  16. import org.springframework.stereotype.Service;
  17. import org.springframework.transaction.annotation.Propagation;
  18. import org.springframework.transaction.annotation.Transactional;
  19. import tk.mybatis.mapper.entity.Example;
  21. import java.util.Date;
  22. import java.util.HashMap;
  23. import java.util.List;
  24. import java.util.Map;
  26. /**
  27.  * Demo class
  28.  *
  29.  * @author shierS
  30.  * @date 2021/6/6
  31.  */
  32. @Service
  33. public class MyOrdersServiceImpl implements MyOrdersService {
  35.     @Autowired
  36.     private OrdersMapperCustom ordersMapperCustom;
  37.     @Autowired
  38.     private OrderStatusMapper orderStatusMapper;
  39.     @Autowired
  40.     private OrdersMapper ordersMapper;
  43.     @Transactional(propagation = Propagation.SUPPORTS)
  44.     @Override
  45.     public PagedGridResult queryMyOrders(String userId, Integer orderStatus, Integer page, Integer pageSize) {
  47.         Map<String, Object> map = new HashMap<>();
  48.         map.put("userId", userId);
  49.         if(orderStatus != null){
  50.             map.put("orderStatus",orderStatus);
  51.         }
  53.         PageHelper.startPage(page,pageSize);
  55.         List<MyOrdersVO> list = ordersMapperCustom.queryMyOrders(map);
  57.         return setterPagedGrid(list,page);
  58.     }
  60.     private PagedGridResult setterPagedGrid(List<?> list,Integer page){
  61.         PageInfo<?> pageList = new PageInfo<>(list);
  62.         PagedGridResult grid = new PagedGridResult();
  63.         grid.setPage(page);
  64.         grid.setRows(list);
  65.         grid.setTotal(pageList.getPages());
  66.         grid.setRecords(pageList.getTotal());
  67.         return grid;
  68.     }
  70.     @Transactional(propagation=Propagation.REQUIRED)
  71.     @Override
  72.     public void updateDeliverOrderStatus(String orderId) {
  74.         OrderStatus updateOrder = new OrderStatus();
  75.         updateOrder.setOrderStatus(OrderStatusEnum.WAIT_RECEIVE.type);
  76.         updateOrder.setDeliverTime(new Date());
  78.         Example example = new Example(OrderStatus.class);
  79.         Example.Criteria criteria = example.createCriteria();
  80.         criteria.andEqualTo("orderId", orderId);
  81.         criteria.andEqualTo("orderStatus", OrderStatusEnum.WAIT_DELIVER.type);
  83.         System.out.println(orderId);
  84.         orderStatusMapper.updateByExampleSelective(updateOrder, example);
  85.     }
  87.     @Transactional(propagation=Propagation.SUPPORTS)
  88.     @Override
  89.     public Orders queryMyOrder(String userId, String orderId) {
  91.         Orders orders = new Orders();
  92.         orders.setUserId(userId);
  93.         orders.setId(orderId);
  94.         orders.setIsDelete(YesOrNo.NO.type);
  96.         return ordersMapper.selectOne(orders);
  97.     }
  99.     @Transactional(propagation=Propagation.REQUIRED)
  100.     @Override
  101.     public boolean updateReceiveOrderStatus(String orderId) {
  103.         OrderStatus updateOrder = new OrderStatus();
  104.         updateOrder.setOrderStatus(OrderStatusEnum.SUCCESS.type);
  105.         updateOrder.setSuccessTime(new Date());
  107.         Example example = new Example(OrderStatus.class);
  108.         Example.Criteria criteria = example.createCriteria();
  109.         criteria.andEqualTo("orderId", orderId);
  110.         criteria.andEqualTo("orderStatus", OrderStatusEnum.WAIT_RECEIVE.type);
  112.         int result = orderStatusMapper.updateByExampleSelective(updateOrder, example);
  114.         return result == 1 ? true : false;
  115.     }
  117.     @Transactional(propagation=Propagation.REQUIRED)
  118.     @Override
  119.     public boolean deleteOrder(String userId, String orderId) {
  121.         Orders updateOrder = new Orders();
  122.         updateOrder.setIsDelete(YesOrNo.YES.type);
  123.         updateOrder.setUpdatedTime(new Date());
  125.         Example example = new Example(Orders.class);
  126.         Example.Criteria criteria = example.createCriteria();
  127.         criteria.andEqualTo("id", orderId);
  128.         criteria.andEqualTo("userId", userId);
  130.         int result = ordersMapper.updateByExampleSelective(updateOrder, example);
  132.         return result == 1 ? true : false;
  133.     }
  134. }

补充：

由于没有商城后台管理，商家发货功能只是开发了接口，使用GET方法，可在浏览器直接调用，或者使用Swagger2进行调试更改订单状态