Monodyee

这个屌丝很懒，什么也没留下！

热门标签

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

作者：Monodyee | 2024-06-05 14:54:29

踩

pagehelper mybatisplus

解决 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

实体类

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;


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

数据层

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> {


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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>
1
2
3
4
5
6
7
8
9
10
11
12
13

业务层

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);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

业务层实现类

在这里插入图片描述

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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

控制层

在这里插入图片描述

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);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

接口配置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();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

请求体

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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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;
1
2
3
4
5
6
7
8
9

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

{
  "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
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3 案例说明

3.1 配置信息

添加依赖

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>
1
2
3
4
5

yml配置信息

pagehelper:
  reasonable: true
  support-methods-arguments: true
  params: countSql
  helperDialect: mysql
1
2
3
4
5

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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/676964

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

文章目录

0 简要说明Pagehelper

1 搭建环境

1.1 项目目录

1.2 项目搭建需要的依赖

1.3 配置分页插件拦截器

1.4 源代码

启动类

实体类

数据层

xml映射文件

业务层

业务层实现类

控制层

接口配置swagger

请求体

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

2.1 关于total属性疑问

2.2 分页不生效问题

3 案例说明

3.1 配置信息

3.2 请求体

3.3 控制层

3.5 业务层

4.6 业务层实现类

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