SpringBoot整合MyBatis四种常用的分页方式（详细总结）

书启秋枫

已于 2023-02-15 18:04:43 修改

阅读量2w

点赞数 33

分类专栏： # SpringBoot Java拦路虎文章标签： mybatis spring boot java

于 2022-11-25 23:25:27 首次发布

本文链接：https://blog.csdn.net/qq_45037155/article/details/128046032

版权

SpringBoot 同时被 2 个专栏收录

47 篇文章

订阅专栏

Java拦路虎

47 篇文章

订阅专栏

一、准备工作

1. 创建表结构

CREATE TABLE `order_info`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `info` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2. 导入表数据

INSERT INTO `order_info` VALUES (1, '购买了手机', '2022-08-25 05:34:17');
INSERT INTO `order_info` VALUES (2, '购买了电脑', '2022-08-25 07:30:39');
INSERT INTO `order_info` VALUES (3, '购买了护手霜', '2022-08-17 22:35:07');
INSERT INTO `order_info` VALUES (4, '购买了泡面', '2022-08-23 08:35:36');
INSERT INTO `order_info` VALUES (5, '购买了纸巾', '2022-07-21 15:26:06');
INSERT INTO `order_info` VALUES (6, '购买了自热米饭', '2021-06-20 13:21:06');
INSERT INTO `order_info` VALUES (7, '购买了移动硬盘', '2022-06-11 11:22:03');
INSERT INTO `order_info` VALUES (8, '购买了狗粮', '2022-05-10 11:21:02');
INSERT INTO `order_info` VALUES (9, '购买了猫粮', '2022-04-10 09:11:02');
INSERT INTO `order_info` VALUES (10, '购买了遥控器', '2022-08-22 22:35:07');
INSERT INTO `order_info` VALUES (11, '购买了裤子', '2022-08-15 08:35:36');
INSERT INTO `order_info` VALUES (12, '购买了鞋子', '2022-08-21 08:35:36');
INSERT INTO `order_info` VALUES (13, '购买了水杯', '2022-08-26 19:39:19');

3. 导入pom.xml依赖

创建新的工程的步骤我这里就省略了，所以废话不多说，直接进入正题！

注：请不要重复导入MyBatis的依赖，这里为了方便展示写到一起了，实际运用的时候，请不要混合在一起导入，不然可能会导致jar包的冲突，PageHelper和MyBatis-plus的jar包可能会产生冲突的。

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
        <!--Mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
 
        <!--PageHelper依赖-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.3</version>
        </dependency>
 
        <!--Mybatis-Plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
    </dependencies>

4. 配置application.yml文件

server:
  port: 8080
 
spring:
  datasource:
    username: 你的mysql用户名
    password: 你的mysql密码
    url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
 
mybatis:
  mapper-locations: classpath:mapping/*.xml

5. 创建公用的实体类

package com.ithuang.demo.bean;
 
import java.util.Date;
 
 
public class OrderInfo {
 
    private int id;
    private String info;
    private Date time;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getInfo() {
        return info;
    }
 
    public void setInfo(String info) {
        this.info = info;
    }
 
    public Date getTime() {
        return time;
    }
 
    public void setTime(Date time) {
        this.time = time;
    }
 
    @Override
    public String toString() {
        return "OrderInfo{" +
                "id=" + id +
                ", info='" + info + '\'' +
                ", time=" + time +
                '}';
    }
 
 
}

二、使用原生Limit关键字进行分页

1. 项目整体结构

2. 创建controller层

package com.ithuang.demo.controller;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.service.OrderInfoService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
 
import javax.annotation.Resource;
import java.util.List;
 
@RestController
public class OrderInfoController {
 
    @Resource
    private OrderInfoService orderInfoService;
 
    @GetMapping("/getOrderInfoList")
    public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow,
                                            @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){
 
        return orderInfoService.getOrderInfoList(pageNow,pageSize);
    }
}

3. 创建service层

package com.ithuang.demo.service;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.mapper.OrderInfoMapper;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import java.util.List;
 
@Service
public class OrderInfoService {
 
    @Resource
    private OrderInfoMapper orderInfoMapper;
 
    public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
        if(pageNow == 1){
            pageNow = 0;
        }
        return orderInfoMapper.getOrderInfoList(pageNow,pageSize);
    }
}

4. 创建mapper层

package com.ithuang.demo.mapper;
 
import com.ithuang.demo.bean.OrderInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
 
import java.util.List;
 
@Mapper
public interface OrderInfoMapper {
 
    List<OrderInfo> getOrderInfoList(@Param("pageNow") int pageNow,@Param("pageSize") int pageSize);
}

5. 创建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.ithuang.demo.mapper.OrderInfoMapper">
    <select id="getOrderInfoList" parameterType="int" resultType="com.ithuang.demo.bean.OrderInfo">
        SELECT * FROM order_info limit #{pageNow},#{pageSize}
    </select>
</mapper>

6. 使用postman进行测试，测试结果如下

三、借助MyBatis提供的第三方PageHelper分页插件1

在MyBatis中配置了分页拦截器（PageInterceptor），就是在执行相关的Sql之前会做一些拦截的操作，这里通过调用startPage的方法，其实就是在查询getOrderInfoList之前会自动加上limit；这里通过setLocalPage方法，将分页信息保存在当前之后线程当中，查询方法与之处于同一个线程，共享ThreadLocal当中的数据，最后将getOrderInfoList查询好的数据结果放到PageInfo当中即可。

扩展：PageHelper.startPage(int PageNum,int PageSize):用来设置页面的位置和展示的数据条目数，我们设置每页展示5条数据。PageInfo用来封装页面信息，返回给前台界面。PageInfo中的一些我们需要用到的参数如下表：

PageInfo.list 结果集
PageInfo.pageNum 当前页码
PageInfo.pageSize 当前页面显示的数据条目
PageInfo.pages 总页数
PageInfo.total 数据的总条目数
PageInfo.prePage 上一页
PageInfo.nextPage 下一页
PageInfo.isFirstPage 是否为第一页
PageInfo.isLastPage 是否为最后一页
PageInfo.hasPreviousPage 是否有上一页
PageHelper.hasNextPage 是否有下一页

（1）引入pom.xml

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

（2）打开application.properties，添加如下几行配置信息

#分页插件
pagehelper.helper-dialect=mysql
pagehelper.params=count=countSql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true

1. 项目整体结构

2. 创建controller层

Mapper文件中, SQL不用增加 limit分页指令,需要什么直接查就可以,Pagehelper 可以自动实现分页!

package com.ithuang.demo.controller;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.service.OrderInfoService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
 
import javax.annotation.Resource;
import java.util.List;
 
@RestController
public class OrderInfoController {
 
    @Resource
    private OrderInfoService orderInfoService;
 
    @GetMapping("/getOrderInfoList")
    public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow,
                                            @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){
 
        return orderInfoService.getOrderInfoList(pageNow,pageSize);
    }
}

2. 创建Service层

package com.ithuang.demo.service;
 
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.mapper.OrderInfoMapper;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import java.util.List;
 
@Service
public class OrderInfoService {
 
    @Resource
    private OrderInfoMapper orderInfoMapper;
 
    public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
        PageHelper.startPage(pageNow,pageSize);
        List<OrderInfo> orderInfoList = orderInfoMapper.getOrderInfoList();
        PageInfo<OrderInfo> userPageInfo = new PageInfo<>(orderInfoList);
        return userPageInfo.getList();
    }
}

3. 创建Mapper层

package com.ithuang.demo.mapper;
 
import com.ithuang.demo.bean.OrderInfo;
import org.apache.ibatis.annotations.Mapper;
 
import java.util.List;
 
@Mapper
public interface OrderInfoMapper {
 
    List<OrderInfo> getOrderInfoList();
}

5. 编写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.ithuang.demo.mapper.OrderInfoMapper">
    <select id="getOrderInfoList" parameterType="int" resultType="com.ithuang.demo.bean.OrderInfo">
        SELECT * FROM order_info
    </select>
</mapper>

6. 使用postman进行测试，测试结果如下

三、借助MyBatis提供的第三方PageHelper分页插件2

1. 依赖pom.xml

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

2. 配置application.properties

#分页插件
pagehelper.helper-dialect=mysql
pagehelper.params=count=countSql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true

3. 实现分页功能

①首页超链接

<a th:href="@{/get/page/1}">显示分页数据</a>

②controller方法

@RequestMapping("/get/page/{pageNo}")
public String getPage(
        @PathVariable("pageNo") Integer pageNo, 
        Model model) {
 
    // PageInfo 对象封装了和分页相关的所有信息
    PageInfo<Emp> pageInfo = empService.getPageInfo(pageNo);
    
    // 将 PageInfo 对象存入模型
    model.addAttribute("pageInfo", pageInfo);
    
    return "emp-page";
}

③service 方法

@Override
public PageInfo<Emp> getPageInfo(Integer pageNo) {
 
    // 1、确定每页显示数据的条数
    int pageSize = 5;
 
    // 2、设定分页数据：开启分页功能。开启后，后面执行的 SELECT 语句会自动被附加 LIMIT 子句，
    // 而且会自动查询总记录数
    PageHelper.startPage(pageNo, pageSize);
 
    // 3、正常执行查询
    List<Emp> empList = empMapper.selectAll();
 
    // 4、封装为 PageInfo 对象返回
    return new PageInfo<>(empList);
}

④页面展示

<tr>
    <td colspan="5">
 
        <span th:each="targetNum : ${pageInfo.navigatepageNums}">
 
            <!-- 不是当前页显示为超链接 -->
            <a th:if="${targetNum != pageInfo.pageNum}"
               th:href="@{/employee/page/}+${targetNum}"
               th:text="'['+${targetNum}+']'">目标页面的页码</a>
 
            <!-- 是当前页不需要显示为超链接 -->
            <span th:if="${targetNum == pageInfo.pageNum}" th:text="'['+${targetNum}+']'">当前页页码</span>
 
        </span>
    </td>
</tr>
<tr>
    <td colspan="5">
        <span th:if="${pageInfo.hasPreviousPage}">
            <a th:href="@{/employee/page/1}">首页</a>
            <a th:href="@{/employee/page/}+${pageInfo.prePage}">上一页</a>
        </span>
 
        [[${pageInfo.pageNum}]]/[[${pageInfo.pages}]]
        [[${pageInfo.pageNum}+'/'+${pageInfo.pages}]]
 
        <span th:if="${pageInfo.hasNextPage}">
            <a th:href="@{/employee/page/}+${pageInfo.nextPage}">下一页</a>
            <a th:href="@{/employee/page/}+${pageInfo.pages}">末页</a>
        </span>
    </td>
</tr>
<tr>
    <td colspan="5">
        <!-- 绑定值改变事件响应函数 -->
        <input id="jumpToPageNumInput" type="text" name="jumpToPageNum" placeholder="请输入您想直接跳转的页码" />
    </td>
</tr>

四、借助MyBatis-Plus拦截器进行分页查询

1. 项目整体结构

2. 编写主配置类

package com.ithuang.demo.config;
 
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
@Configuration
public class MybatisPlusConfig {
 
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

3. 创建controller层

package com.ithuang.demo.controller;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.service.OrderInfoService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
 
import javax.annotation.Resource;
import java.util.List;
 
@RestController
public class OrderInfoController {
 
    @Resource
    private OrderInfoService orderInfoService;
 
    @GetMapping("/getOrderInfoList")
    public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow,
                                            @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){
 
        return orderInfoService.getOrderInfoList(pageNow,pageSize);
    }
}

4. 创建Service层

package com.ithuang.demo.service;
 
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.mapper.OrderInfoMapper;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import java.util.List;
 
@Service
public class OrderInfoService {
 
    @Resource
    private OrderInfoMapper orderInfoMapper;
 
    public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
        Page<OrderInfo> page= new Page<>(pageNow,pageSize);
        IPage<OrderInfo> iPage = orderInfoMapper.selectPage(page,null);
        return iPage.getRecords();
    }
}

5. 创建Mapper层

package com.ithuang.demo.mapper;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ithuang.demo.bean.OrderInfo;
import org.apache.ibatis.annotations.Mapper;
 
 
@Mapper
public interface OrderInfoMapper extends BaseMapper<OrderInfo> {
 
}

6. 使用postman进行测试，测试结果如下

五、借助MyBabtis提供的RowBounds进行分页查询

RowBounds它是在SQL执行的结果进行截取分页的，所以不适合大量数据的截取和分页，它适合在查询较少的结果集当中使用。

1. 项目整体结构

2. 创建controller层

package com.ithuang.demo.controller;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.service.OrderInfoService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
 
import javax.annotation.Resource;
import java.util.List;
 
@RestController
public class OrderInfoController {
 
    @Resource
    private OrderInfoService orderInfoService;
 
    @GetMapping("/getOrderInfoList")
    public List<OrderInfo> getOrderInfoList(@RequestParam(value = "pageNow",defaultValue = "1") int pageNow,
                                            @RequestParam(value = "pageSize",defaultValue = "3") int pageSize){
 
        return orderInfoService.getOrderInfoList(pageNow,pageSize);
    }
}

3. 创建Service层

package com.ithuang.demo.service;
 
import com.ithuang.demo.bean.OrderInfo;
import com.ithuang.demo.mapper.OrderInfoMapper;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import java.util.List;
 
@Service
public class OrderInfoService {
 
    @Resource
    private OrderInfoMapper orderInfoMapper;
 
    public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
        RowBounds rowBounds = new RowBounds(pageNow,pageSize);
        return orderInfoMapper.getOrderInfoList(rowBounds);
    }
}

4. 创建Mapper层

package com.ithuang.demo.mapper;
 
import com.ithuang.demo.bean.OrderInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.session.RowBounds;
 
import java.util.List;
 
@Mapper
public interface OrderInfoMapper {
 
    List<OrderInfo> getOrderInfoList(RowBounds rowBounds);
}

5. 创建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.ithuang.demo.mapper.OrderInfoMapper">
    <select id="getOrderInfoList" resultType="com.ithuang.demo.bean.OrderInfo">
        SELECT * FROM order_info
    </select>
</mapper>

PageInfo.list	结果集
PageInfo.pageNum	当前页码
PageInfo.pageSize	当前页面显示的数据条目
PageInfo.pages	总页数
PageInfo.total	数据的总条目数
PageInfo.prePage	上一页
PageInfo.nextPage	下一页
PageInfo.isFirstPage	是否为第一页
PageInfo.isLastPage	是否为最后一页
PageInfo.hasPreviousPage	是否有上一页
PageHelper.hasNextPage	是否有下一页