mybatis-plus集成pagehelper进行分页排序和返回查询总数

一、项目搭建

新建一个springboot项目

1.引入依赖

pom.xml

  <dependency>
    <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
 <!--  Druid 数据库连接池和监控的功能 -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.1.23</version>
 </dependency>

 <!-- mysql驱动 -->
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.17</version>
 </dependency>

 <!-- pagehelper 分页插件 -->
 <!-- 报错信息：Relying upon circular references is discouraged and they are prohibited by default.
  Update your application to remove the dependency cycle between beans. As a last resort,
  it may be possible to break the cycle automatically by setting spring.main.
  allow-circular-references to true.
 原因：SpringBoot与PageHelper的版本冲突导致的，使用SpringBoot2.6及以上版本，对应的PageHelper版本应该在1.4.1及以上
  -->
 <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper-spring-boot-starter</artifactId>
     <version>1.4.1</version>
 </dependency>

 <!-- Mybatis-plus -->
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
     <version>2.5.3</version>
 </dependency>
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.1.0</version>
 </dependency>
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-generator</artifactId>
     <version>3.2.0</version>
 </dependency>

 <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <optional>true</optional>
 </dependency>

注意：如果在项目启动时报错：

Relying upon circular references is discouraged and they are
prohibited by default. Update your application to remove the
dependency cycle between beans. As a last resort, it may be possible
to break the cycle automatically by setting spring.main.
allow-circular-references to true.

原因：SpringBoot与PageHelper的版本冲突导致的，使用SpringBoot2.6及以上版本，对应的PageHelper版本应该在1.4.1及以上

2.配置文件

application.yml

server:
  port: 8181

spring:
  datasource:
    # 驱动类
    driver-class-name: com.mysql.cj.jdbc.Driver
    # 连接，注意各个配置，尤其是要一次性执行多条 SQL 时，要 allowMultiQueries=true          useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&autoReconnect=true&useSSL=false
    url: jdbc:mysql://192.168.0.182:13307/platform?useUnicode=true&useSSL=false&allowMultiQueries=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&useAffectedRows=true&serverTimezone=GMT%2B8
    # 用户名
    username: root
    # 密码
    password: 123456

############ mybatis-plus配置 #############
mybatis-plus:
  type-enums-package: com.zhouquan.entity.enums*
  configuration:
    cache-enabled: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 关闭sql打印
    # log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl

############ 分页插件PageHelper配置 #############
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

项目结构

二、使用测试

因此 PageHelper.startPage(pageNum, pageSize, orderField);务必要写在需要执行的查询上面

1.普通的列表查询

服务的实现类只是简单的列表查询

postman调用后直接看控制台打印结果

http://192.168.0.146:8181/v1/book_data/list?pageNum=1&pageSize=10&orderField=publish_date asc

将查询出的list对象放入PageInfo的构造器中，即可获取到总数用于前端分页

返回结果：

{
    "total": 38,
    "recordList": [
        {
            "id": 25,
            "title": "野草",
            "author": "鲁迅",
            "publishDate": "1973-03-01"
        },
        {
            "id": 14,
            "title": "呐喊",
            "author": "鲁迅",
            "publishDate": "1973-03-01"
        }
    ...
}

2.自定义sql列表查询

controller.java



控制台打印

三、pagehelper的实现

官方文档写的更清晰
https://pagehelper.github.io/docs/interceptor/