Spring Boot集成Mybatis Plus通过Pagehelper实现分页查询

geekmice

于 2023-08-10 10:26:50 发布

阅读量2.5k

点赞数

本文链接：https://blog.csdn.net/greek7777/article/details/132124084

版权

问题汇总专栏收录该内容

56 篇文章 1 订阅

订阅专栏

解决 PageInfo 返回的 total 不正确
 关于PageInfo的total属性得到的值等于当前页记录数
 Pagehelper官网
 Pagehelper使用入门

0 简要说明Pagehelper

在这里插入图片描述

1 搭建环境

1.1 项目目录

在这里插入图片描述

1.2 项目搭建需要的依赖

		 <!-- SpringBoot的依赖配置-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.5.10</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        <!--Mybatis Plus 核心依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--MySQL驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!--Mybatis Plus 扩展依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--pagehelper分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>

1.3 配置分页插件拦截器

在properties或者yml配置拦截器信息，使其生效。
Spring Boot 引入 starter 后自动生效，对分页插件进行配置时，在 Spring Boot 对应的配置文件 application.[properties|yaml] 中配置

# DataSource Config
spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true&serverTimezone=UTC&useSSL=false&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
  mvc:
    pathmatch:
      matching-strategy: ANT_PATH_MATCHER

#配置日志
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapper/*.xml

server:
  port: 8089

pagehelper:
  reasonable: true
  support-methods-arguments: true
  params: countSql
  helperDialect: mysql

reasonable:分页合理化参数，默认值为false。当该参数设置为 true 时，pageNum<=0 时会查询第一页， pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询。

supportMethodsArguments：支持通过 Mapper 接口参数来传递分页参数，默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页。使用方法可以参考测试代码中的 com.github.pagehelper.test.basic 包下的 ArgumentsMapTest 和 ArgumentsObjTest。

helperDialect：分页插件会自动检测当前的数据库链接，自动选择合适的分页方式。

params：为了支持startPage(Object params)方法，增加了该参数来配置参数映射，用于从对象中根据属性名取值，可以配置 pageNum,pageSize,count,pageSizeZero,reasonable，不配置映射的用默认值，默认值为pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;pageSizeZero=pageSizeZero。

更加详细的参数说明，可以参考官网说明

1.4 源代码

启动类

package com.geekmice.sbpagehelper;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @BelongsProject: spring-boot-scaffold
 * @BelongsPackage: PACKAGE_NAME
 * @Author: pingmingbo
 * @CreateTime: 2023-08-05  15:14
 * @Description: TODO
 * @Version: 1.0
 */
@MapperScan(value = "com.geekmice.sbpagehelper.dao")
@SpringBootApplication
public class SbPageHelperApplication {
    public static void main(String[] args) {
        SpringApplication.run(SbPageHelperApplication.class, args);
    }
}

实体类

package com.geekmice.sbpagehelper.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;

/**
 * (Teacher)实体类
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
@TableName("teacher")
@Data
public class Teacher implements Serializable {
    private static final long serialVersionUID = -82982716139385175L;
    /**
     * 教师号
     */
    @TableId
    @TableField(value = "teacher_no")
    private String teacherNo;

    /**
     * 教师名称
     */
    @TableField(value = "teacher_name")
    private String teacherName;

    /**
     * 部门编号
     */
    @TableField(value = "category_id")
    private Integer categoryId;


}

数据层

package com.geekmice.sbpagehelper.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.geekmice.sbpagehelper.domain.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;

/**
 * (Teacher)表数据库访问层
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
public interface TeacherDao extends BaseMapper<Teacher> {


}

xml映射文件

    <?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="com.geekmice.sbpagehelper.dao.TeacherDao">
    
        <resultMap type="com.geekmice.sbpagehelper.domain.Teacher" id="TeacherMap">
            <result property="teacherNo" column="teacher_no" jdbcType="VARCHAR"/>
            <result property="teacherName" column="teacher_name" jdbcType="VARCHAR"/>
            <result property="categoryId" column="category_id" jdbcType="INTEGER"/>
        </resultMap>
    

    
    </mapper>

业务层

package com.geekmice.sbpagehelper.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.geekmice.sbpagehelper.domain.Teacher;
import com.geekmice.sbpagehelper.dto.QueryDTO;
import com.github.pagehelper.PageInfo;

import java.util.List;

/**
 * (Teacher)表服务接口
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
public interface TeacherService extends IService<Teacher> {


    /**
     * @return 响应信息
     * @description 初次使用分页查询
     */
    List<Teacher> queryPage();

    /**
     * @param pageSize 当前页数
     * @param pageNum  每页条数
     * @return 返回信息
     * @description 分页参数查询
     */
    PageInfo<Teacher> queryPage(int pageNum, int pageSize);

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    PageInfo<Teacher> queryPage(QueryDTO queryDTO);
}

业务层实现类

在这里插入图片描述

package com.geekmice.sbpagehelper.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.geekmice.sbpagehelper.dao.TeacherDao;
import com.geekmice.sbpagehelper.domain.Teacher;
import com.geekmice.sbpagehelper.dto.QueryDTO;
import com.geekmice.sbpagehelper.service.TeacherService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * (Teacher)表服务实现类
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
@Service("teacherService")
@Slf4j
public class TeacherServiceImpl extends ServiceImpl<TeacherDao, Teacher> implements TeacherService {
    @Resource
    private TeacherDao teacherDao;

    /**
     * @return 响应信息
     * @description 初次使用分页查询
     */
    @Override
    public List<Teacher> queryPage() {
        PageHelper.startPage(1, 10);
        List<Teacher> result = teacherDao.selectList(null);
        return result;
    }

    /**
     * @param pageSize 当前页数
     * @param pageNum  每页条数
     * @return 返回信息
     * @description 分页参数查询
     */
    @Override
    public PageInfo<Teacher> queryPage(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        QueryWrapper<Teacher> teacherQueryWrapper = new QueryWrapper<>();
        teacherQueryWrapper.likeRight("teacher_name", "小");
        List<Teacher> teachers = teacherDao.selectList(teacherQueryWrapper);
        PageInfo<Teacher> teacherPageInfo = new PageInfo<>(teachers);
        return teacherPageInfo;
    }

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    @Override
    public PageInfo<Teacher> queryPage(QueryDTO queryDTO) {
        int pageNum = queryDTO.getPageNum();
        int pageSize = queryDTO.getPageSize();
        PageHelper.startPage(pageNum,pageSize);
        QueryWrapper<Teacher> teacherQueryWrapper = new QueryWrapper<>();
        teacherQueryWrapper.eq("teacher_name", queryDTO.getTeacherName());
        List<Teacher> teacherList = teacherDao.selectList(teacherQueryWrapper);
        PageInfo<Teacher> result = new PageInfo<>(teacherList);
        return result;
    }
}

控制层

在这里插入图片描述

package com.geekmice.sbpagehelper.controller;

import com.geekmice.common.utils.AjaxResult;
import com.geekmice.sbpagehelper.domain.Teacher;
import com.geekmice.sbpagehelper.dto.QueryDTO;
import com.geekmice.sbpagehelper.service.TeacherService;
import com.github.pagehelper.PageInfo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

/**
 * (Teacher)表控制层
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
@RestController
@RequestMapping("teacher")
@Api(tags = "0.分页查询模块")
public class TeacherController {
    /**
     * 服务对象
     */
    @Resource
    private TeacherService teacherService;

    /**
     * 通过主键查询单条数据
     *
     * @param id 主键
     * @return 单条数据
     */
    @ApiOperation(value = "查询单条数据")
    @GetMapping("selectOne")
    public Teacher selectOne(String id) {
        return null;
    }

    /**
     * @return 响应信息
     * @description 初次使用分页查询
     */
    @GetMapping(value = "queryPage")
    @ApiOperation(value = "分页查询")
    public List<Teacher> queryPage() {
        List<Teacher> result = teacherService.queryPage();
        return result;
    }

    /**
     * @param pageSize 当前页数
     * @param pageNum  每页条数
     * @return 返回信息
     * @description 分页参数查询
     */
    @GetMapping(value = "queryPageHavingParams")
    @ApiOperation(value = "分页查询带有参数")
    public AjaxResult queryPageHavingParams(int pageSize, int pageNum) {
        PageInfo<Teacher> result = teacherService.queryPage(pageNum, pageSize);
        return AjaxResult.success(result);
    }

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    @GetMapping(value = "queryPageByDTO")
    @ApiOperation(value = "多参数分页查询")
    public AjaxResult queryPageByDTO(QueryDTO queryDTO) {
        PageInfo<Teacher> result = teacherService.queryPage(queryDTO);
        return AjaxResult.success(result);
    }

}

接口配置swagger

package com.geekmice.sbpagehelper.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;

/**
 * @BelongsProject: spring-boot-scaffold
 * @BelongsPackage: com.geekmice.sbhelloworld.com.geekmice.sbpagehelper.config
 * @Author: pingmingbo
 * @CreateTime: 2023-07-30  15:45
 * @Description: TODO
 * @Version: 1.0
 */
@Configuration
public class Knife4jConfig {
    @Bean(value = "defaultApi2")
    public Docket customDocket() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.geekmice.sbpagehelper.controller"))
                .build();
    }

    /**
     * 构建 api文档的详细信息函数
     * @return
     */
    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("现货交易")
                .version("1.0.0")
                .description("现货交易详情")
                .contact(new Contact("geekmice","http://geekmice.cn","2437690868@qq.com"))
                .build();
    }
}

请求体

package com.geekmice.sbpagehelper.dto;

import com.github.pagehelper.PageInfo;
import lombok.Data;

/**
 * @BelongsProject: spring-boot-scaffold
 * @BelongsPackage: com.geekmice.sbpagehelper.dto
 * @Author: pingmingbo
 * @CreateTime: 2023-08-05  16:00
 * @Description: TODO
 * @Version: 1.0
 */
@Data
public class QueryDTO extends PageInfo {
    private String teacherName;
}

2 可能出现的疑问或者问题

2.1 关于total属性疑问

2.2 分页不生效问题

当我们调用pagehelper.startPage（）方法后下一条语句必须是你要调用的查询语句；
我们的PageInfo传入的结果集，必须是我们调用查询语句返回的结果集；

提前说明：入参是 pageNum:1 pageSize:2 userName:张模糊查询
正常情况，没有分页是有以张开头有三条数据，有分页的情况下张1，张2

第一种问题复现如下：
List userDomainList = userDao.selectList(userDomainQueryWrapper); // 位置1
PageHelper.startPage(pageNum, pageSize); // 位置2

        int pageNum = userVO.getPageNum();
        int pageSize = userVO.getPageSize();
        String userName = userVO.getUserName();
        QueryWrapper<UserDomain> userDomainQueryWrapper = new QueryWrapper<>();
        userDomainQueryWrapper.likeRight("user_name", userName);
        List<UserDomain> userDomainList = userDao.selectList(userDomainQueryWrapper); // 位置1
        PageInfo<UserDomain> userDomainPageInfo = new PageInfo<>(userDomainList);
        PageHelper.startPage(pageNum, pageSize); // 位置2
        return userDomainPageInfo;

返回结果有疑问:返回的是没有分页，只要是张开头的数据都返回了。

{
  "msg": "操作成功",
  "code": 200,
  "data": {
    "total": 3,
    "list": [
      {
        "id": 1,
        "userName": "张1",
        "birthday": "2023-08-09T16:00:00.000+00:00",
        "sex": "男",
        "address": "123@163.com"
      },
      {
        "id": 3,
        "userName": "张2",
        "birthday": "2023-08-09T16:00:00.000+00:00",
        "sex": "女",
        "address": "999@163.com"
      },
      {
        "id": 4,
        "userName": "张3",
        "birthday": "2023-08-09T16:00:00.000+00:00",
        "sex": "男",
        "address": "9994@qq.com"
      }
    ],
    "pageNum": 1,
    "pageSize": 4,
    "size": 3,
    "startRow": 1,
    "endRow": 3,
    "pages": 1,
    "prePage": 0,
    "nextPage": 0,
    "isFirstPage": true,
    "isLastPage": true,
    "hasPreviousPage": false,
    "hasNextPage": false,
    "navigatePages": 8,
    "navigatepageNums": [
      1
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 1
  }
}

3 案例说明

3.1 配置信息

添加依赖

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>

yml配置信息

pagehelper:
  reasonable: true
  support-methods-arguments: true
  params: countSql
  helperDialect: mysql

3.2 请求体

PageInfo这个api是Pagehelper原生的对象，涵盖基本分页信息
在这里插入图片描述

package com.geekmice.sbpagehelper.dto;

import com.github.pagehelper.PageInfo;
import lombok.Data;

/**
 * @BelongsProject: spring-boot-scaffold
 * @BelongsPackage: com.geekmice.sbpagehelper.dto
 * @Author: pingmingbo
 * @CreateTime: 2023-08-05  16:00
 * @Description: TODO
 * @Version: 1.0
 */
@Data
public class QueryDTO extends PageInfo {
    private String teacherName;
}

3.3 控制层

/**
 * (Teacher)表控制层
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
@RestController
@RequestMapping("teacher")
@Api(tags = "0.分页查询模块")
public class TeacherController {
    /**
     * 服务对象
     */
    @Resource
    private TeacherService teacherService;

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    @GetMapping(value = "queryPageByDTO")
    @ApiOperation(value = "多参数分页查询")
    public AjaxResult queryPageByDTO(QueryDTO queryDTO) {
        PageInfo<Teacher> result = teacherService.queryPage(queryDTO);
        return AjaxResult.success(result);
    }
}

3.5 业务层

package com.geekmice.sbpagehelper.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.geekmice.sbpagehelper.domain.Teacher;
import com.geekmice.sbpagehelper.dto.QueryDTO;
import com.github.pagehelper.PageInfo;

import java.util.List;

/**
 * (Teacher)表服务接口
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
public interface TeacherService extends IService<Teacher> {

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    PageInfo<Teacher> queryPage(QueryDTO queryDTO);
}

4.6 业务层实现类

package com.geekmice.sbpagehelper.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.geekmice.sbpagehelper.dao.TeacherDao;
import com.geekmice.sbpagehelper.domain.Teacher;
import com.geekmice.sbpagehelper.dto.QueryDTO;
import com.geekmice.sbpagehelper.service.TeacherService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.stream.Collectors;

/**
 * (Teacher)表服务实现类
 *
 * @author pingmingbo
 * @since 2023-08-05 15:19:26
 */
@Service("teacherService")
@Slf4j
public class TeacherServiceImpl extends ServiceImpl<TeacherDao, Teacher> implements TeacherService {
    @Resource
    private TeacherDao teacherDao;

    /**
     * @param queryDTO 请求体
     * @return 响应信息
     * @description 多参数分页查询
     */
    @Override
    public PageInfo<Teacher> queryPage(QueryDTO queryDTO) {
        int pageNum = queryDTO.getPageNum();
        int pageSize = queryDTO.getPageSize();
        QueryWrapper<Teacher> teacherQueryWrapper = new QueryWrapper<>();
        teacherQueryWrapper.eq("teacher_name", queryDTO.getTeacherName());
        PageHelper.startPage(pageNum,pageSize);
        List<Teacher> teacherList = teacherDao.selectList(teacherQueryWrapper);
        PageInfo<Teacher> result = new PageInfo<>(teacherList );
        return result;
    }
}

再次说明 PageHelper.startPage(pageNum,pageSize);使用顺序

4 关键问题：查询和录入操作使用一个dto出现强制分页情况【待定，问题未能复现，请稍等】

geekmice

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Spring Boot集成Mybatis Plus通过Pagehelper实现分页查询

Spring Boot 引入 starter 后自动生效，对分页插件进行配置时，在 Spring Boot 对应的配置文件 application.[properties|yaml] 中配置。配置的字段中取值，查找到合适的值时就会自动分页。方法，增加了该参数来配置参数映射，用于从对象中根据属性名取值，可以配置。正常情况，没有分页是有以张开头有三条数据，有分页的情况下张1，张2。返回结果有疑问:返回的是没有分页，只要是张开头的数据都返回了。，分页插件会从查询方法的参数值中，自动根据上面。
复制链接

扫一扫