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 picUrlFROM product_sku skLEFT JOIN product_spu sp ON sk.spu_id = sp.idLEFT JOIN product_brand pb ON pb.id = sp.brand_idWHEREsk.`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 编号，自增*/@TableIdprivate 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@AllArgsConstructorpublic 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 {@Resourceprivate ProductSkuMapper productSkuMapper;@Overridepublic Page<ProductSkuRespReVO> selectSkuList1(Page<ProductSkuRespReVO> page, ProductSpuPageReqqVO vo) {return productSkuMapper.selectSkuList1(page, vo);}}

七、controller

@RestController
@RequestMapping("/product/spu")
@Validated
public class ProductSpuController {@Resourceprivate 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);}
}