目录
方式1
三、借助MyBatis提供的第三方PageHelper分页插件2
五、借助MyBabtis提供的RowBounds进行分页查询
方式1
一、准备工作
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依赖
<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/*.xml5. 创建公用的实体类
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 +
'}';
}
}项目结构
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层
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中配置了分页拦截器（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. 项目整体结构
1. 创建controller层
Mapper文件中, SQL不用增加 limit分页指令,需要什么直接查就可以,Pagehelper 可以自动实现分页
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层
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层
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>三、借助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=true3. 实现分页功能
①首页超链接
<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>6. 使用postman进行测试，测试结果如下
2641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
