mybatis-plus动态分页

一、需求

mybatis-plus自带了CRUD和单表的分页方法，但是真实场景中，我们可能需要多表关联，自己写list查询，这就面临分页问题，之前都是通过内存分页，感觉是颗定时炸弹，所以有没有办法通过数据库物理分页，读了官方文档终于找到了方法。mybatis-plus依赖包自己去引入，这里默认大家已经集成了mybatis-plus。

二、xml文件sql

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.iocoder.yudao.module.product.dal.mysql.sku.ProductSkuMapper">

  <select id="selectSkuList1" parameterType="cn.iocoder.yudao.module.product.controller.admin.spu.vo.ProductSpuPageReqqVO"
          resultType="cn.iocoder.yudao.module.product.controller.admin.spu.vo.ProductSkuRespReVO">
      SELECT sk.id AS skuId,
      sp.id AS spuId,
      sp.`name`,
      pb.`name` AS brandName,
      sk.stock AS stock,
      sk.properties AS propertiesStr,
      sk.price as price,
      sk.pic_url as picUrl
      FROM product_sku sk
      LEFT JOIN product_spu sp ON sk.spu_id = sp.id
      LEFT JOIN product_brand pb ON pb.id = sp.brand_id
      WHERE
      sk.`deleted` =0
      <if test="vo.brandName != null and vo.brandName != ''">
          and pb.`name` like concat('%',#{vo.brandName},'%')
      </if>
      <if test="vo.name != null and vo.name != ''">
          and sp.`name` like concat('%',#{vo.name},'%')
      </if>
      <if test="vo.categoryId != null">
          and sp.`category_id` =#{vo.categoryId}
      </if>
      <if test="vo.skuIds != null and vo.skuIds.size > 0">
          and sk.id in
          <foreach item="item" collection="vo.skuIds" open="(" separator="," close=")" index="">
              #{item}
          </foreach>
      </if>

  </select>

</mapper>

三、三个实体类（需要替换成自己项目的实体）

根据自己项目的实体类替换，这里只是举例子。
1、实体一

import com.baomidou.mybatisplus.annotation.KeySequence;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import lombok.*;

import java.util.List;

/**
* 商品 SKU DO
*
* @author 
*/
@TableName(value = "product_sku", autoResultMap = true)
@KeySequence("product_sku_seq") // 用于 Oracle、PostgreSQL、Kingbase、DB2、H2 数据库的主键自增。如果是 MySQL 等数据库，可不写。
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ProductSkuDO {

   /**
    * 商品 SKU 编号，自增
    */
   @TableId
   private Long id;
   /**
    * SPU 编号
    *
    */
   private Long spuId;
   /**
    * 属性数组，JSON 格式
    */
   @TableField(typeHandler = JacksonTypeHandler.class)
   private List<Property> properties;
   /**
    * 商品价格，单位：分
    */
   private Integer price;
   /**
    * 市场价，单位：分
    */
   private Integer marketPrice;
   /**
    * 成本价，单位：分
    */
   private Integer costPrice;
   /**
    * 商品条码
    */
   private String barCode;
   /**
    * 图片地址
    */
   private String picUrl;
   /**
    * 库存
    */
   private Integer stock;
   /**
    * 商品重量，单位：kg 千克
    */
   private Double weight;
   /**
    * 商品体积，单位：m^3 平米
    */
   private Double volume;

   /**
    * 一级分销的佣金，单位：分
    */
   private Integer firstBrokeragePrice;
   /**
    * 二级分销的佣金，单位：分
    */
   private Integer secondBrokeragePrice;

   // ========== 营销相关字段 =========

   // ========== 统计相关字段 =========
   /**
    * 商品销量
    */
   private Integer salesCount;

   /**
    * 商品属性
    */
   @Data
   @NoArgsConstructor
   @AllArgsConstructor
   public static class Property {

       /**
        * 属性编号
        */
       private Long propertyId;
       /**
        * 属性名字
        *
        * 注意：每次属性名字发生变化时，需要更新该冗余
        */
       private String propertyName;

       /**
        * 属性值编号
        */
       private Long valueId;
       /**
        * 属性值名字
        *
        * 注意：每次属性值名字发生变化时，需要更新该冗余
        */
       private String valueName;

   }

}

2、实体二

import com.alibaba.fastjson.JSONArray;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;

import java.io.Serializable;
import java.util.Collections;
import java.util.List;

@Data
public class ProductSkuRespReVO implements Serializable {

   private Long spuId;
   private Long skuId;
   private String name;
   private String brandName;

   private Integer stock;


   private String propertiesStr;

   private String picUrl;

   private List<ProductSkuSaveReqVO.Property> properties;

   public List<ProductSkuSaveReqVO.Property> getProperties() {
       if (StringUtils.isNotBlank(propertiesStr)) {
           return JSONArray.parseArray(propertiesStr, ProductSkuSaveReqVO.Property.class);
       }
       return Collections.emptyList();
   }
}

3、实体三

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

import java.util.List;


@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class ProductSpuPageReqqVO extends PageParam {


   private Integer pageNo ;

   private Integer pageSize;

   private String name;


   private String brandName;


   private Long categoryId;

   private List<Long> skuIds;

}

四、mapper接口

import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface ProductSkuMapper extends BaseMapperX<ProductSkuDO> {

   Page<ProductSkuRespReVO> selectSkuList1(Page<ProductSkuRespReVO> page, @Param("vo") ProductSpuPageReqqVO vo);
}

五、service接口

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import java.util.Collection;
import java.util.List;

/**
 * 商品 SKU Service 接口
 *
 * @author 
 */
public interface ProductSkuService {

    Page<ProductSkuRespReVO> selectSkuList1(Page<ProductSkuRespReVO> page, ProductSpuPageReqqVO vo);

}

六、service实现类

import java.util.*;
import java.util.stream.Collectors;

/**
 * 商品 SKU Service 实现类
 *
 * @author 
 */
@Service
@Validated
public class ProductSkuServiceImpl implements ProductSkuService {

    @Resource
    private ProductSkuMapper productSkuMapper;


    @Override
    public Page<ProductSkuRespReVO> selectSkuList1(Page<ProductSkuRespReVO> page, ProductSpuPageReqqVO vo) {
         return productSkuMapper.selectSkuList1(page, vo);
    }


}

七、controller

@RestController
@RequestMapping("/product/spu")
@Validated
public class ProductSpuController {

    @Resource
    private ProductSkuService productSkuService;

    @GetMapping("/page/sku")
    @Operation(summary = "套装关联spu/sku商品列表")
    @PreAuthorize("@ss.hasPermission('product:spu:sku:query')")
    public CommonResult<PageResult<ProductSkuRespReVO>> getSkuPage1(@Valid ProductSpuPageReqqVO pageVO) {
        Page<ProductSkuRespReVO> classInfoPage = new Page<>(pageVO.getPageNo(), pageVO.getPageSize());
        productSkuService.selectSkuList1(classInfoPage, pageVO);
        PageResult<ProductSkuRespReVO> bean = new PageResult<>();
        bean.setTotal(classInfoPage.getTotal());
        bean.setList(classInfoPage.getRecords());
        return success(bean);
    }
}