MyBatis笔记（四）SpringBoot整合Mybatis+PageHelper实现分页查询

最新推荐文章于 2023-04-26 11:50:57 发布

嘉禾嘉宁papa

最新推荐文章于 2023-04-26 11:50:57 发布

阅读量5.8k

点赞数 4

分类专栏： MyBatis笔记文章标签： spring boot mybatis pagehelper 分页查询

本文链接：https://blog.csdn.net/Alian_1223/article/details/121170534

版权

MyBatis笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、背景

PageHelper 是一款好用的开源免费的 MyBatis 第三方物理分页插件，功能非常的强大，使用也非常的简单。本文的主要介绍 SpringBoot 整合 MyBatis + PageHelper 实现分页查询，如果对于 MyBatis 基本使用不了解的可以参考我之前的文章：

MyBatis笔记（一）Spring Boot整合MyBatis实现增删查改详解（入门版）
MyBatis笔记（二）MyBatis参数传递详解
 MyBatis笔记（三）MyBatis动态SQL详解

二、maven依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.alian</groupId>
    <artifactId>mybatis-pagehelper</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis-pagehelper</name>
    <description>Spring Boot整合MyBatis分页功能</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.5.2</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!--pagehelper分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
            <scope>runtime</scope>
        </dependency>

        <!--日志输出-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <!--spring boot test-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
            <version>2.5.2</version>
        </dependency>

        <!--spring test-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.8</version>
        </dependency>

        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

这里最重要的依赖就是下面这个，它就是实现分页的核心依赖。

 <!--pagehelper分页插件-->
 <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper-spring-boot-starter</artifactId>
     <version>1.4.0</version>
 </dependency>

三、配置

3.1、application.yml配置

application.yml

server:
  port: 8081
  servlet:
    context-path: /pagehelper

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    username: test
    password: Test!@34
    url: jdbc:mysql://192.168.0.139:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
    initialSize: 5
    minIdle: 5
    maxActive: 20

mybatis:
  config-location: classpath:config/mybatis.xml
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.alian.pagehelper.dto

我这里使用druid作为数据源， MyBatis 的整合不清楚的可以参考我开篇的提到的文章。

3.2、mybatis.xml

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
		<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
	<!--SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING-->
	<!--如果希望输出sql相关信息就配置logImpl，设置值为STDOUT_LOGGING-->
	<setting name="logImpl" value="STDOUT_LOGGING" />
	<setting name="cacheEnabled" value="false" />
	<setting name="lazyLoadingEnabled" value="false" />
	<!--使用驼峰命名方式-->
	<setting name="mapUnderscoreToCamelCase" value="true" />
	<setting name="callSettersOnNulls" value="true" />
	<!--可以使用{index}获取到参数，index下标从0开始-->
	<setting name="useActualParamName" value="false"/>
</settings>

</configuration>

因为是Spring Boot 项目整合的，所以我不用去加pagehelper插件：

	<plugins>
		<plugin interceptor="com.github.pagehelper.PageInterceptor">
			<!-- property属性可以不用配置，新版本能自动识别底层数据库 -->
			<property name="helperDialect" value="mysql"/>
			<!-- 分页合理化-->
			<property name="reasonable" value="true"/>
		</plugin>
	</plugins>

不然就会出现如下错误：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.RuntimeException: 在系统中发现了多个分页插件，请检查系统配置!
### Cause: java.lang.RuntimeException: 在系统中发现了多个分页插件，请检查系统配置!

当然你这时可以通过在主类上加上 @SpringBootApplication(exclude = PageHelperAutoConfiguration.class)进行过滤配置，但是 SpringBoot本身就是为了减少配置而来的，你这样加了一个又加一个，说白了还是不符合 SpringBoot 设计原则，所以那个plugin就不用配置了。

四、Mapper

4.1、EmployeeMapper.java

EmployeeMapper.java

package com.alian.pagehelper.mapper;

import com.alian.pagehelper.dto.EmployeeDto;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    /**
     * 根据部门进行分页查询员工信息
     */
    List<EmployeeDto> selectPageByDepartment(@Param("department") String department);
}

4.2、EmployeeMapper.xml

EmployeeMapper.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接口的路径-->
<mapper namespace="com.alian.pagehelper.mapper.EmployeeMapper">

    <!--根据部门名称分页查询-->
    <select id="selectPageByDepartment" resultType="EmployeeDto">
		SELECT
			e.id,
			e.emp_name as name,
			e.age,
			e.salary,
			e.department,
			e.hire_date as hireDate
		FROM
			tb_inf_employee e
		<where>
			<if test="department!=null and department!=''">
				e.department=#{department}
			</if>
		</where>
  	</select>
</mapper>

五、测试

为了方便我们就写个测试类测试下，我们分页查询研发部的员工信息，并且按照入职时间进行排序，具体如下：

package com.alian.pagehelper.service;

import com.alian.pagehelper.dto.EmployeeDto;
import com.alian.pagehelper.mapper.EmployeeMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.autoconfigure.PageHelperProperties;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestPageHelperService {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Test
    public void selectPageByDepartment() {
        PageHelperProperties properties = new PageHelperProperties();
        log.info("{}", properties);
        PageHelper.startPage(1, 4).setOrderBy("hire_date asc");
        //PageHelper.startPage(2, 4).setOrderBy("hire_date asc");
        final PageInfo<EmployeeDto> employeePageInfo = new PageInfo<>(this.employeeMapper.selectPageByDepartment("研发部"));
        log.info("总记录数：{}", employeePageInfo.getTotal());
        log.info("总页数：{}", employeePageInfo.getPages());
        log.info("每页记录数：{}", employeePageInfo.getPageSize());
        log.info("前一页：{}", employeePageInfo.getPrePage());
        log.info("后一页：{}", employeePageInfo.getNextPage());
        log.info("记录开始行号：{}", employeePageInfo.getStartRow());
        log.info("记录结束行号：{}", employeePageInfo.getEndRow());
        List<EmployeeDto> list = employeePageInfo.getList();
        log.info("获取到的结果：{}", list);
    }
}

我们数据库表的数据如下：
在这里插入图片描述
当我们代码里执行 PageHelper.startPage(1, 4).setOrderBy(“hire_date asc”);运行结果：

==>  Preparing: SELECT count(0) FROM tb_inf_employee e WHERE e.department = ?
==> Parameters: 研发部(String)
<==    Columns: count(0)
<==        Row: 6
<==      Total: 1
==>  Preparing: SELECT e.id, e.emp_name AS name, e.age, e.salary, e.department, e.hire_date AS hireDate FROM tb_inf_employee e WHERE e.department = ? order by hire_date asc LIMIT ?
==> Parameters: 研发部(String), 4(Integer)
<==    Columns: id, name, age, salary, department, hireDate
<==        Row: BAT10002, 唐鹏, 31, 25000.0, 研发部, 2015-03-01
<==        Row: BAT10008, 李焱林, 28, 18000.0, 研发部, 2015-07-01
<==        Row: BAT10003, 王林, 30, 22000.0, 研发部, 2015-08-01
<==        Row: BAT10010, 胡申涛, 34, 24000.0, 研发部, 2018-03-28
<==      Total: 4
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@14f060b8]
2021-11-08 14:03:42 191 [main] INFO selectPageByDepartment 31:总记录数：6
2021-11-08 14:03:42 191 [main] INFO selectPageByDepartment 32:总页数：2
2021-11-08 14:03:42 191 [main] INFO selectPageByDepartment 33:每页记录数：4
2021-11-08 14:03:42 191 [main] INFO selectPageByDepartment 34:前一页：0
2021-11-08 14:03:42 192 [main] INFO selectPageByDepartment 35:后一页：2
2021-11-08 14:03:42 192 [main] INFO selectPageByDepartment 36:记录开始行号：1
2021-11-08 14:03:42 192 [main] INFO selectPageByDepartment 37:记录结束行号：4
2021-11-08 14:03:42 192 [main] INFO selectPageByDepartment 39:获取到的结果：Page{count=true, pageNum=1, pageSize=4, startRow=0, endRow=4, total=6, pages=2, reasonable=true, pageSizeZero=false}[EmployeeDto(id=BAT10002, name=唐鹏, age=31, salary=25000.0, department=研发部, hireDate=2015-03-01), EmployeeDto(id=BAT10008, name=李焱林, age=28, salary=18000.0, department=研发部, hireDate=2015-07-01), EmployeeDto(id=BAT10003, name=王林, age=30, salary=22000.0, department=研发部, hireDate=2015-08-01), EmployeeDto(id=BAT10010, name=胡申涛, age=34, salary=24000.0, department=研发部, hireDate=2018-03-28)]

当我们代码里执行 PageHelper.startPage(2, 4).setOrderBy(“hire_date asc”);运行结果：

==>  Preparing: SELECT count(0) FROM tb_inf_employee e WHERE e.department = ?
==> Parameters: 研发部(String)
<==    Columns: count(0)
<==        Row: 6
<==      Total: 1
==>  Preparing: SELECT e.id, e.emp_name AS name, e.age, e.salary, e.department, e.hire_date AS hireDate FROM tb_inf_employee e WHERE e.department = ? order by hire_date asc LIMIT ?, ?
==> Parameters: 研发部(String), 4(Long), 4(Integer)
<==    Columns: id, name, age, salary, department, hireDate
<==        Row: BAT10001, 梁南生, 32, 31000.0, 研发部, 2020-05-20
<==        Row: BAT10009, 胡俊伟, 27, 23000.0, 研发部, 2020-07-01
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3002e397]
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 31:总记录数：6
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 32:总页数：2
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 33:每页记录数：4
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 34:前一页：1
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 35:后一页：0
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 36:记录开始行号：5
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 37:记录结束行号：6
2021-11-08 14:02:53 911 [main] INFO selectPageByDepartment 39:获取到的结果：Page{count=true, pageNum=2, pageSize=4, startRow=4, endRow=8, total=6, pages=2, reasonable=true, pageSizeZero=false}[EmployeeDto(id=BAT10001, name=梁南生, age=32, salary=31000.0, department=研发部, hireDate=2020-05-20), EmployeeDto(id=BAT10009, name=胡俊伟, age=27, salary=23000.0, department=研发部, hireDate=2020-07-01)]

研发部一共6条记录，第一页4条，第二页2条，符合我们查询的结果，并且结果也按我们的需求按照入职时间进行了排序。

嘉禾嘉宁papa

关注

4
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MyBatis笔记（四）SpringBoot整合Mybatis+PageHelper实现分页查询

目录一、背景一、背景本文的主要介绍 MyBatis 动态SQL，如果对于MyBatis基本使用不了解的可以参考我上一篇文章：MyBatis笔记（一）Spring Boot整合MyBatis实现增删查改详解（入门版）MyBatis笔记（二）MyBatis参数传递详解原符号<<=>>=&’"转义符号&lt;&lt;=&gt;&gt;=&amp;&apos;&quot
复制链接

扫一扫