pagehelper分页查询

pagehelper分页查询

PageHelper是MyBatis框架中提供的分页插件，它支持Oracle、Mysql、MariaDB、SQLite、Hsqldb、PostgreSQL六种数据库。使用PageHelper可以方便地进行分页查询，只需在SQL查询前调用PageHelper.startPage方法并传入两个参数：page=页码，rows=每页显示的条数。例如，PageHelper.startPage(1, 20)表示请求第一页，每页显示20条数据。

1.pom文件

引入 pagehelper pom文件

		<dependency>
            <groupId>com.github.pagehelpergroupId>
            <artifactId>pagehelper-spring-boot-starterartifactId>
         dependency>
1
2
3
4

2.Controller

控制层接收到"/page"请求，传递到后台参数封装为了EmployeePageQueryDTO对象

调用service层

   /**
     * 员工分页查询
     * @param employeePageQueryDTO
     * @return
     */
    @GetMapping("/page")
    @ApiOperation("员工分页查询")
    public Result<PageResult> page(EmployeePageQueryDTO employeePageQueryDTO) {
        log.info("员工分页查询,参数为: {}", employeePageQueryDTO);
        PageResult pageResult = employeeService.pageQuery(employeePageQueryDTO);
        return Result.success(pageResult);
    } 
1
2
3
4
5
6
7
8
9
10
11
12

2.PageResult

封装的分页查询结果

/**
 * 封装分页查询结果
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult implements Serializable {
    private long total; //总记录数
    private List records; //当前页数据集合
}
1
2
3
4
5
6
7
8
9
10

3.EmployeePageQueryDTO

封装的前台传递过来的数据

@Data
public class EmployeePageQueryDTO implements Serializable {
    //员工姓名
    private String name;
    //页码
    private int page;
    //每页显示记录数
    private int pageSize;
}
1
2
3
4
5
6
7
8
9

4.Service

    /**
     * 分页查询
     * @param employeePageQueryDTO
     * @return
     */
    PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO);
1
2
3
4
5
6

5.ServiceImpl

调用PageHelper方法进行分页查询

需要两个参数 页码，和每一页展示的数量个数

   /**
     * 分页查询
     * @param employeePageQueryDTO
     * @return
     */
    public PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO) {
        //开始分页查询
        PageHelper.startPage(employeePageQueryDTO.getPage(), employeePageQueryDTO.getPageSize());
        
        Page<Employee> page = employeeMapper.pageQuery(employeePageQueryDTO);
        long total = page.getTotal();
        List<Employee> result = page.getResult();
        return new PageResult(total, result);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.Employee

员工的实体类

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long id;
    private String username;
    private String name;
    private String password;
    private String phone;
    private String sex;
    private String idNumber;
    private Integer status;
    //@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;
    //@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime updateTime;
    private Long createUser;
    private Long updateUser;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

7.mapper

   /**
     * 分页查询
     * @param employeePageQueryDTO
     * @return
     */
    Page<Employee> pageQuery(EmployeePageQueryDTO employeePageQueryDTO);
1
2
3
4
5
6

8.mapper.xml

PageHelper会自动的补全sql代码

<select id="pageQuery" resultType="com.sky.entity.Employee">
        select * from employee
        <where>
            <if test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            if>
        where>
        order by create_time desc
    select>
1
2
3
4
5
6
7
8
9

控制台输出记录

2023-10-10 22:37:55.033  INFO 18988 --- [nio-8080-exec-5] c.s.controller.admin.EmployeeController  : 员工分页查询,参数为: EmployeePageQueryDTO(name=null, page=1, pageSize=10)
2023-10-10 22:37:55.239 DEBUG 18988 --- [nio-8080-exec-5] c.s.m.EmployeeMapper.pageQuery_COUNT     : ==>  Preparing: SELECT count(0) FROM employee
2023-10-10 22:37:55.240 DEBUG 18988 --- [nio-8080-exec-5] c.s.m.EmployeeMapper.pageQuery_COUNT     : ==> Parameters: 
2023-10-10 22:37:55.246 DEBUG 18988 --- [nio-8080-exec-5] c.s.m.EmployeeMapper.pageQuery_COUNT     : <==      Total: 1
    
    
2023-10-10 22:37:55.250 DEBUG 18988 --- [nio-8080-exec-5] com.sky.mapper.EmployeeMapper.pageQuery  : ==>  Preparing: select * from employee order by create_time desc LIMIT ?
    //这里显示了 自动补全的代码LIMIT
    
2023-10-10 22:37:55.252 DEBUG 18988 --- [nio-8080-exec-5] com.sky.mapper.EmployeeMapper.pageQuery  : ==> Parameters: 10(Integer)
2023-10-10 22:37:55.256 DEBUG 18988 --- [nio-8080-exec-5] com.sky.mapper.EmployeeMapper.pageQuery  : <==      Total: 4
1
2
3
4
5
6
7
8
9
10
11
12
13