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 = # AND o.is_delete = 0 <if test="paramMap.orderStatus != null"> AND os.order_status = # </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 = # </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 = # AND o.is_delete = 0 <if test="paramMap.orderStatus != null"> AND os.order_status = # </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!