为什么mybatis-plus进行分页查询时向mapper中传入Page实例就可以完成分页查询

先看代码：

serviceImpl：

    @Override
    public Page<GoodsAndOrderVo> getGoodAndOrder(RequestPageBase req) {
        Page<GoodsAndOrderVo> page = new Page<>(req.getCurrentPage(), req.getPageSize());
        return goodsMapper.getGoodsAndOrders(page);
    }

mapper：

public interface GoodsMapper extends BaseMapper<Goods> {
    @Select("SELECT " +
            "order_goods.id as order_id, " +
            "goods.id as goods_id, " +
            "goods.name as goods_name, " +
            "order_goods.orders_number as order_number " +
            "FROM order_goods " +
            "LEFT JOIN goods ON order_goods.goods_id = goods.id")
    Page<GoodsAndOrderVo> getGoodsAndOrders(Page<GoodsAndOrderVo> page);
}

响应结果：

{
    "code": "SUCCESS",
    "message": "成功",
    "success": true,
    "data": {
        "records": [
            {
                "orderId": 1,
                "goodsId": 1,
                "goodsName": "扑热息痛",
                "orderNumber": 3
            },
            {
                "orderId": 2,
                "goodsId": 1,
                "goodsName": "扑热息痛",
                "orderNumber": 15
            }
        ],
        "total": 2,
        "size": 10,
        "current": 1,
        "orders": [],
        "optimizeCountSql": true,
        "hitCount": false,
        "countId": null,
        "maxLimit": null,
        "searchCount": true,
        "pages": 1
    }
}

原因：

1.MyBatis-Plus的分页是通过拦截器实现的。在配置类中需要有这样的配置

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

2.当我们执行带有Page参数的查询时，分页插件会自动：

• 先执行一次 COUNT 查询，获取总记录数

• 然后在原SQL的基础上添加 LIMIT 语句进行分页查询

例如，我们的原始SQL是：

SELECT 
    order_goods.id as order_id,
    goods.id as goods_id,
    goods.name as goods_name,
    order_goods.orders_number as order_number
FROM order_goods 
LEFT JOIN goods ON order_goods.goods_id = goods.id

MyBatis-Plus会自动转换成两条SQL：

首先执行COUNT查询：

SELECT COUNT(*) FROM (
    SELECT 
        order_goods.id as order_id,
        goods.id as goods_id,
        goods.name as goods_name,
        order_goods.orders_number as order_number
    FROM order_goods 
    LEFT JOIN goods ON order_goods.goods_id = goods.id
) tmp

然后执行分页查询：

SELECT 
    order_goods.id as order_id,
    goods.id as goods_id,
    goods.name as goods_name,
    order_goods.orders_number as order_number
FROM order_goods 
LEFT JOIN goods ON order_goods.goods_id = goods.id
LIMIT ?,?   -- 这里的参数会被自动替换为(currentPage-1)*pageSize和pageSize

3.返回的Page对象会包含：

• 分页数据（records）

• 总记录数（total）

• 当前页（current）

• 每页大小（size）

• 总页数（pages）等信息

所以虽然在Mapper接口中我们只看到传入了Page对象，但实际上MyBatis-Plus的分页插件在底层帮我们完成了所有分页相关的工作。这就是为什么我们不需要在Mapper中手动处理分页逻辑的原因。 

可以通过在application.yml中添加以下配置来查看实际执行的SQL：

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl