springboot使用PageHelper插件进行分页+模糊查询

springboot使用PageHelper插件进行分页查询

最近我在学习利用分页插件来实现分页查询数据，今天给搭建分享一下PageHelper分页插件的学习

环境搭建

springboot（父依赖version 2.0.7）+maven
首先导入pom.xml依赖

<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.1</version>
</dependency>
< --springboot必须依赖 -->

我这里是用的是1.4.1的版本，建议大家使用最新版本，因为使用旧版本可能会出现循环依赖的问题，还有就是旧版本可能与springboot版本冲突的问题（springboot2.6禁止循环依赖）
如下图使用1.3.0版本报错：

然后我去了解了一下循环依赖，大致就是说 假如bean A依赖于bean B时，bean B又依赖于bean A：bean A --> bean B --> bean A。当没有循环依赖时，bean A --> bean B --> bean C。
当Spring上下文加载所有bean时，它会尝试按照它们工作所需的顺序去创建bean。没有循环依赖时，Spring将创建bean C，然后创建bean B（并将bean C注入其中），然后创建bean A（并将bean B注入其中）。但当有循环依赖时，Spring无法决定应该首先创建哪个bean，因为它们彼此依赖。在这些情况下，Spring将在加载上下文时引发BeanCurrentlyInCreationException。
参考文章 : 什么是循环依赖以及解决方式

配置properties/yml文件

application.properties配置：

# pageHelper分页
# 指定数据库 默认自动检测数据库类型
pagehelper.helperDialect=mysql
# 是否启用分页合理化：当pageNum<1时，会自动查询第一页的数据，当pageNum>pages时，自动查询最后一页数据；
pagehelper.reasonable=true
# 默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页。
pagehelper.supportMethodsArguments=true
# 用于从对象中根据属性名取值
pagehelper.params=count: countSql

application.yml配置：

# 分页
# 指定数据库 默认自动检测数据库类型
pagehelper:
    helperDialect: mysql
# 是否启用分页合理化：当pageNum<1时，会自动查询第一页的数据，当pageNum>pages时，自动查询最后一页数据；
    reasonable: true
# 默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页。
    supportMethodsArguments: true
# 用于从对象中根据属性名取值
    params=count: countSql

代码部分

实体类pojo/entity

	@TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    private String name;
    private String address;

controller层

public PageInfo<Address> getListByPage(
            @RequestParam(value = "pageNo",defaultValue = "1") Integer pageNo,
            @RequestParam(value = "pageSize",defaultValue = "10") Integer pageSize,
            @RequestParam("searchValue") String searchValue){
        PageHelper.startPage(pageNo,pageSize);
        List<Address> addresses = this.addressService.getListByPage(searchValue);
        PageInfo<Address> addressPageInfo = new PageInfo<>(addresses);
        return addressPageInfo;
    }

传入参数 pageNo–当前页，pageSize–每页条数，searchValue–模糊查询

PageHelper.startPage(pageNum, pageSize);

pageNum是 当前页数，pageSize是 每页的条数。
数据查询完后使用PageInfo

PageInfo<实体类> pageInfo = new PageInfo<>(查询结果);

serviceI

List<Address> getListByPage(String searchValue);

serviceImpl

public List<Address> getListByPage(String searchValue) {
        List<Address> listByPage = this.addressMapper.getListByPage(searchValue);
        return listByPage;
}

mapper

List<Address> getListByPage(String searchValue);

mapper.xml

<select id="getListByPage" resultType="com.lee.testaddress.entity.Address">
        select
        id, name, address
        from
        t_address
        <where>
            <if test="searchValue != null and searchValue != ''">
               and name like concat ('%',#{searchValue},'%')
            </if>
        </where>
</select>

在这里pageHelper已经帮我们做好分页了，所以我们在sql语句中就不用再加分页limit了

pageInfo其它属性

pageNum：当前页码
pageSize：每页数据的数据条数
size：当前页有多少条数据
startRow：当前页码第一条数据的
endRow：当前页码的开始条
pages：当前页码结束条
navigateFirstPage：首页号
navigateLastPage：尾页号
prePage：上一页
nextPage：下一页
isFirstPage：是否为第一页
isLastPage：是否为最后一页
hasPreviousPage：是否有前一页
hasNextPage：是否有下一页
navigatePages：导航页码数
navigatepageNums：导航页码数 可以遍历

测试

通过Apifox或者Postman测试，我这里使用Apifox进行测试
我表中有40条数据

我先设置查询每页条数pageSize为10

得到数据，对照pageInfo其它属性获得变量含义

{
    "total": 40,
    "list": [
        {
            "id": 1,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 2,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 3,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 4,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 5,
            "name": "lisi",
            "address": "Shanghai"
        },
        {
            "id": 6,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 7,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 8,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 9,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 10,
            "name": "wangwu",
            "address": "Shenzhen"
        }
    ],
    "pageNum": 1,
    "pageSize": 10,
    "size": 10,
    "startRow": 1,
    "endRow": 10,
    "pages": 4,
    "prePage": 0,
    "nextPage": 2,
    "isFirstPage": true,
    "isLastPage": false,
    "hasPreviousPage": false,
    "hasNextPage": true,
    "navigatePages": 8,
    "navigatepageNums": [
        1,
        2,
        3,
        4
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 4
}

然后我设置每页查询50条数据，这样我表中的所有数据全部在第一页查出

{
    "total": 40,
    "list": [
        {
            "id": 1,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 2,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 3,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 4,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 5,
            "name": "lisi",
            "address": "Shanghai"
        },
        {
            "id": 6,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 7,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 8,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 9,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 10,
            "name": "wangwu",
            "address": "Shenzhen"
        },
        {
            "id": 11,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 12,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 13,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 14,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 15,
            "name": "zhaoliu",
            "address": "Nanjing"
        },
        {
            "id": 16,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 17,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 18,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 19,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 20,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 21,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 22,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 23,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 24,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 25,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 26,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 27,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 28,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 29,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 31,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 32,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 33,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 34,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 35,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 36,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 37,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 38,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 39,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 41,
            "name": "zhangsan",
            "address": "Beijing"
        },
        {
            "id": 42,
            "name": "zhangsan",
            "address": "Beijing"
        }
    ],
    "pageNum": 1,
    "pageSize": 50,
    "size": 40,
    "startRow": 1,
    "endRow": 40,
    "pages": 1,
    "prePage": 0,
    "nextPage": 0,
    "isFirstPage": true,
    "isLastPage": true,
    "hasPreviousPage": false,
    "hasNextPage": false,
    "navigatePages": 8,
    "navigatepageNums": [
        1
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 1
}

模糊查询

查询name中带有lisi的数据
idea打印sql日志

Apifox返回结果，对照数据库，查询结果正确

查询name中带有i的数据

Apifox返回结果，对照数据库，查询结果正确

结语

文章可能有点长，但是都是一点一滴学习的见证
刚进入开发行业还有我很多学习的地方，我很愿意和大家分享我以后学习的过程，感谢阅读！