Spring Boot与MyBatis整合实现分页查询
使用Spring Boot框架和MyBatis持久层框架结合，通过PageHelper插件来实现数据库数据的分页查询功能。这样可以在应用中方便地处理大量数据的分页显示，提升查询效率和用户体验。
准备数据表
t_goods表
CREATE TABLE `t_goods`
(
`goods_id` mediumint unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
`cat_id` int unsigned NOT NULL DEFAULT '0' COMMENT '分类id',
`extend_cat_id` int DEFAULT '0' COMMENT '扩展分类id',
`goods_sn` varchar(60) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '商品编号',
`goods_name` varchar(120) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '商品名称',
`click_count` int unsigned NOT NULL DEFAULT '0' COMMENT '点击数',
`brand_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '品牌id',
`store_count` smallint unsigned NOT NULL DEFAULT '10' COMMENT '库存数量',
`comment_count` smallint DEFAULT '0' COMMENT '商品评论数',
`weight` int unsigned NOT NULL DEFAULT '0' COMMENT '商品重量克为单位',
`market_price` decimal(10, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT '市场价',
`shop_price` decimal(10, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT '本店价',
`cost_price` decimal(10, 2) DEFAULT '0.00' COMMENT '商品成本价',
`keywords` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '商品关键词',
`goods_remark` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '商品简单描述',
`goods_content` text CHARACTER SET utf8 COMMENT '商品详细描述',
`original_img` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '商品上传原始图',
`is_real` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否为实物',
`is_on_sale` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否上架',
`is_free_shipping` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否包邮0否1是',
`on_time` int unsigned NOT NULL DEFAULT '0' COMMENT '商品上架时间',
`sort` smallint unsigned NOT NULL DEFAULT '50' COMMENT '商品排序',
`is_recommend` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否推荐',
`is_new` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否新品',
`is_hot` tinyint(1) DEFAULT '0' COMMENT '是否热卖',
`last_update` int unsigned NOT NULL DEFAULT '0' COMMENT '最后更新时间',
`goods_type` smallint unsigned NOT NULL DEFAULT '0' COMMENT '商品所属类型id，取值表goods_type的cat_id',
`spec_type` smallint DEFAULT '0' COMMENT '商品规格类型，取值表goods_type的cat_id',
`give_integral` mediumint DEFAULT '0' COMMENT '购买商品赠送积分',
`exchange_integral` int NOT NULL DEFAULT '0' COMMENT '积分兑换：0不参与积分兑换，积分和现金的兑换比例见后台配置',
`suppliers_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '供货商ID',
`sales_sum` int DEFAULT '0' COMMENT '商品销量',
`prom_type` tinyint(1) DEFAULT '0' COMMENT '0 普通订单,1 限时抢购, 2 团购 , 3 促销优惠',
`prom_id` int DEFAULT '0' COMMENT '优惠活动id',
`commission` decimal(10, 2) DEFAULT '0.00' COMMENT '佣金用于分销分成',
`spu` varchar(128) CHARACTER SET utf8 DEFAULT '' COMMENT 'SPU',
`sku` varchar(128) CHARACTER SET utf8 DEFAULT '' COMMENT 'SKU'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
创建springboot项目
创建springboot项目，结构如下：
引入maven依赖
pom.xml
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.12.RELEASE</spring-boot.version>
</properties>
<dependencies>
<!-- Spring Boot 基础启动器，提供核心功能 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<!-- Spring Boot Web 启动器，支持 web 开发 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<!-- Spring Boot 数据 JPA 启动器，支持 JPA 数据库操作 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<!-- MyBatis Spring Boot 启动器，支持 MyBatis 框架 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!-- SLF4J API，日志框架的通用接口 -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.30</version>
</dependency>
<!-- Logback 实现，SLF4J API 的具体实现 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!-- MySQL 数据库连接器 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<!-- PageHelper 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<!-- Thymeleaf 模板引擎，用于视图渲染 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<!-- Lombok 依赖，简化 Java Bean 的编写 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
<!-- Spring Boot 测试启动器，提供测试支持 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${spring-boot.version}</version>
</dependency>
</dependencies>
application配置文件
application.properties
# Spring Boot 应用程序名称
spring.application.name=SpringbootMybatisPagehelperDemoApplication
# mvc配置
server.port=8081
server.servlet.context-path=/
# 数据源配置
spring.datasource.url=jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root1234!
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# MyBatis 配置
mybatis.mapper-locations=classpath:mapper/**/*.xml
mybatis.type-aliases-package=com.mpd.pojo
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.default-fetch-size=100
mybatis.configuration.default-statement-timeout=30
# PageHelper分页插件 配置
pagehelper.helperDialect=mysql
pagehelper.reasonable=false
pagehelper.supportMethodsArguments=true
pagehelper.params.count=countSql
# Thymeleaf模板引擎 配置
thymeleaf.prefix=classpath:/templates/
thymeleaf.suffix=.html
thymeleaf.mode=HTML
thymeleaf.encoding=utf-8
thymeleaf.cache=false
spring.thymeleaf.date-format=yyyy-MM-dd HH:mm:ss
# 日志配置
logging.level.org.mybatis=DEBUG
logging.level.jdbc.sqltiming=DEBUG
# Spring Boot 日志配置
logging.level.root=INFO
logging.level.com.mpd=DEBUG
编写实体类
Goods.java
package com.mpd.pojo;
import jdk.nashorn.internal.objects.annotations.Setter;
import lombok.Getter;
import lombok.ToString;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.*;
import java.math.BigDecimal;
@Getter
@ToString
@Entity
@Table(name = "t_goods")
public class Goods {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "goods_id")
private Integer goodsId; // 商品ID，主键，自增
@Column(name = "cat_id", nullable = false)
private Integer catId; // 商品分类ID，不能为空
@Column(name = "extend_cat_id")
private Integer extendCatId; // 扩展分类ID，允许为空
@Column(name = "goods_sn", nullable = false, length = 60)
private String goodsSn; // 商品编号，不能为空，长度最大为60
@Column(name = "goods_name", nullable = false, length = 120)
private String goodsName; // 商品名称，不能为空，长度最大为120
@Column(name = "click_count", nullable = false)
private Integer clickCount; // 点击次数，不能为空
@Column(name = "brand_id", nullable = false)
private Short brandId; // 品牌ID，不能为空
@Column(name = "store_count", nullable = false)
private Short storeCount; // 库存数量，不能为空
@Column(name = "comment_count")
private Short commentCount; // 评论数量，允许为空
@Column(name = "weight", nullable = false)
private Integer weight; // 商品重量，不能为空
@Column(name = "market_price", nullable = false)
private BigDecimal marketPrice; // 市场价格，不能为空
@Column(name = "shop_price", nullable = false)
private BigDecimal shopPrice; // 店铺价格，不能为空
@Column(name = "cost_price")
private BigDecimal costPrice; // 成本价格，允许为空
@Column(name = "keywords", nullable = false, length = 255)
private String keywords; // 商品关键词，不能为空，长度最大为255
@Column(name = "goods_remark", nullable = false, length = 255)
private String goodsRemark; // 商品备注，不能为空，长度最大为255
@Column(name = "goods_content", columnDefinition = "TEXT")
private String goodsContent; // 商品详细内容，允许为空，使用TEXT类型
@Column(name = "original_img", nullable = false, length = 255)
private String originalImg; // 原始图片URL，不能为空，长度最大为255
@Column(name = "is_real", nullable = false)
private Boolean isReal; // 是否为实物商品，不能为空
@Column(name = "is_on_sale", nullable = false)
private Boolean isOnSale; // 是否在售，不能为空
@Column(name = "is_free_shipping", nullable = false)
private Boolean isFreeShipping; // 是否包邮，不能为空
@Column(name = "on_time", nullable = false)
private Integer onTime; // 上架时间，不能为空
@Column(name = "sort", nullable = false)
private Short sort; // 排序值，不能为空
@Column(name = "is_recommend", nullable = false)
private Boolean isRecommend; // 是否推荐，不能为空
@Column(name = "is_new", nullable = false)
private Boolean isNew; // 是否新品，不能为空
@Column(name = "is_hot")
private Boolean isHot; // 是否热销，允许为空
@Column(name = "last_update", nullable = false)
private Integer lastUpdate; // 最后更新时间，不能为空
@Column(name = "goods_type", nullable = false)
private Short goodsType; // 商品类型，不能为空
@Column(name = "spec_type")
private Short specType; // 规格类型，允许为空
@Column(name = "give_integral")
private Integer giveIntegral; // 赠送积分，允许为空
@Column(name = "exchange_integral", nullable = false)
private Integer exchangeIntegral; // 兑换积分，不能为空
@Column(name = "suppliers_id", nullable = false)
private Short suppliersId; // 供应商ID，不能为空
@Column(name = "sales_sum")
private Integer salesSum; // 销售总数，允许为空
@Column(name = "prom_type")
private Boolean promType; // 促销类型，允许为空
@Column(name = "prom_id")
private Integer promId; // 促销ID，允许为空
@Column(name = "commission")
private BigDecimal commission; // 佣金，允许为空
@Column(name = "spu", length = 128)
private String spu; // 商品SPU，长度最大为128，允许为空
@Column(name = "sku", length = 128)
private String sku; // 商品SKU，长度最大为128，允许为空
}
编写Mapper接口
GoodsMapper.java
package com.mpd.mapper;
import com.mpd.pojo.Goods;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface GoodsMapper {
// 查询所有商品的集合
List<Goods> selectAllGoods();
}
GoodsMapper.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.mpd.mapper.GoodsMapper">
<!-- 结果映射，定义了数据库字段和 Java 对象属性的对应关系 -->
<resultMap id="GoodsResultMap" type="com.mpd.pojo.Goods">
<id column="goods_id" property="goodsId"/>
<result column="cat_id" property="catId"/>
<result column="extend_cat_id" property="extendCatId"/>
<result column="goods_sn" property="goodsSn"/>
<result column="goods_name" property="goodsName"/>
<result column="click_count" property="clickCount"/>
<result column="brand_id" property="brandId"/>
<result column="store_count" property="storeCount"/>
<result column="comment_count" property="commentCount"/>
<result column="weight" property="weight"/>
<result column="market_price" property="marketPrice"/>
<result column="shop_price" property="shopPrice"/>
<result column="cost_price" property="costPrice"/>
<result column="keywords" property="keywords"/>
<result column="goods_remark" property="goodsRemark"/>
<result column="goods_content" property="goodsContent"/>
<result column="original_img" property="originalImg"/>
<result column="is_real" property="isReal"/>
<result column="is_on_sale" property="isOnSale"/>
<result column="is_free_shipping" property="isFreeShipping"/>
<result column="on_time" property="onTime"/>
<result column="sort" property="sort"/>
<result column="is_recommend" property="isRecommend"/>
<result column="is_new" property="isNew"/>
<result column="is_hot" property="isHot"/>
<result column="last_update" property="lastUpdate"/>
<result column="goods_type" property="goodsType"/>
<result column="spec_type" property="specType"/>
<result column="give_integral" property="giveIntegral"/>
<result column="exchange_integral" property="exchangeIntegral"/>
<result column="suppliers_id" property="suppliersId"/>
<result column="sales_sum" property="salesSum"/>
<result column="prom_type" property="promType"/>
<result column="prom_id" property="promId"/>
<result column="commission" property="commission"/>
<result column="spu" property="spu"/>
<result column="sku" property="sku"/>
</resultMap>
<!-- 定义可重用的 SQL 片段 -->
<sql id="goodsColumns">
goods_id
, cat_id, extend_cat_id, goods_sn, goods_name, click_count,
brand_id, store_count, comment_count, weight, market_price,
shop_price, cost_price, keywords, goods_remark, goods_content,
original_img, is_real, is_on_sale, is_free_shipping,
on_time, sort, is_recommend, is_new, is_hot,
last_update, goods_type, spec_type, give_integral,
exchange_integral, suppliers_id, sales_sum, prom_type,
prom_id, commission, spu, sku
</sql>
<!--查询所有商品集合-->
<select id="selectAllGoods" resultMap="GoodsResultMap">
select
<include refid="goodsColumns"></include>
from t_goods
</select>
</mapper>
编写Controller
查询比较简单，省略了service层，直接使用controller即可。
GoodsController.java
@Controller
public class GoodsController {
@Autowired
private GoodsMapper goodsMapper;
@RequestMapping("goodsList")
public String goodsList(
@RequestParam(defaultValue = "1") int pageNum, // 当前页码，默认值为 1
@RequestParam(defaultValue = "10") int pageSize, // 每页显示的记录数，默认值为 10
Model model) { // Spring MVC 的 Model，用于传递数据到视图
// 设置分页参数
// PageHelper.startPage(pageNum, pageSize) 会根据传入的页码和每页大小
// 在执行查询时自动计算出需要查询的记录范围
// 例如，如果 pageNum 为 1，pageSize 为 10，则查询的记录是第1页，这一页有10条数据
PageHelper.startPage(pageNum, pageSize);
// 查询所有商品数据
// 这里的 goodsMapper.selectAllGoods() 将会在 PageHelper 的上下文中执行
// 这意味着它会根据之前设置的分页参数来限制查询结果
List<Goods> goodsList = goodsMapper.selectAllGoods();
// 创建 PageInfo 对象，用于封装分页信息
// PageInfo 会根据查询结果自动计算出总记录数、总页数、当前页的记录等信息
// 这里的 goodsList 是从数据库中查询到的记录列表
PageInfo<Goods> pageInfo = new PageInfo<>(goodsList);
// 将分页信息添加到 Model 中，供视图使用
model.addAttribute("pageInfo", pageInfo);
// 返回视图名称，Spring MVC 会根据视图解析器找到对应的模板
return "goods-list";// thymeleaf模板
}
}
说明
- PageHelper.startPage(pageNum, pageSize):这是 PageHelper 提供的一个静态方法，用于设置当前的分页参数。它会在执行查询前记录当前请求的页码和每页的大小。当调用
PageHelper.startPage(pageNum, pageSize)后，PageHelper 会在当前线程中保存这些参数，以便后续的 SQL 查询可以使用这些信息。 - goodsMapper.selectAllGoods():当执行这个查询方法时，PageHelper 会自动拦截这个查询，并根据之前设置的分页参数生成相应的 SQL 语句。例如，如果当前页码是 1，每页大小是 10，PageHelper 会将查询转换为 SQL 语句，限制结果集为前 10 条记录。
- PageInfo pageInfo = new PageInfo<>(goodsList):
PageInfo是一个用于封装分页信息的类。在构造函数中传入goodsList，它会根据查询结果自动计算出总记录数、总页数、当前页的记录等信息。PageInfo还提供了一些方法，方便在视图中获取分页信息，比如获取当前页码、总页数、是否有上一页和下一页等。
编写thymeleaf模板文件
goods-list.html
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" lang="en">
<head>
<title>Goods List</title>
<style>
body {
font-family: Arial, sans-serif;
background-color: #f4f4f4;
margin: 0;
padding: 20px;
}
.container {
max-width: 1500px;
margin: 0 auto;
}
h1 {
text-align: center;
color: #333;
}
.table-wrapper {
max-height: 500px; /* Set a maximum height for the table */
overflow-y: auto; /* Enable vertical scrolling */
}
table {
width: 100%;
border-collapse: collapse;
background-color: white;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
}
thead th {
position: sticky;
top: 0;
background-color: #4CAF50;
color: white;
padding: 12px;
text-align: left;
z-index: 1;
}
tbody tr:nth-child(even) {
background-color: #f9f9f9;
}
tbody tr:hover {
background-color: #f1f1f1;
}
th, td {
padding: 12px;
text-align: left;
border-bottom: 1px solid #ddd;
}
.pagination {
display: flex;
justify-content: center;
margin-top: 20px;
}
.pagination a {
color: #4CAF50;
padding: 10px 15px;
text-decoration: none;
margin: 0 5px;
border: 1px solid #4CAF50;
border-radius: 5px;
transition: background-color 0.3s, color 0.3s;
}
.pagination a.active {
background-color: #4CAF50;
color: white;
}
.pagination a:hover:not(.active) {
background-color: #ddd;
}
</style>
</head>
<body>
<div class="container">
<h1>商品列表</h1>
<div class="table-wrapper">
<table>
<thead>
<tr>
<th>商品ID</th>
<th>商品编号</th>
<th>商品名称</th>
<th>库存数量</th>
<th>重量</th>
<th>市场价格</th>
<th>店铺价格</th>
<th>成本价格</th>
<th>是否在售</th>
<th>是否包邮</th>
<th>上架时间</th>
<th>是否推荐</th>
<th>是否新品</th>
<th>是否热销</th>
<th>销售总数</th>
<th>促销类型</th>
</tr>
</thead>
<tbody>
<tr th:each="goods : ${pageInfo.list}">
<td th:text="${goods.goodsId}"></td>
<td th:text="${goods.goodsSn}"></td>
<td th:text="${goods.goodsName}"></td>
<td th:text="${goods.storeCount}"></td>
<td th:text="${goods.weight}"></td>
<td th:text="${goods.marketPrice}"></td>
<td th:text="${goods.shopPrice}"></td>
<td th:text="${goods.costPrice}"></td>
<td th:text="${goods.isOnSale ? '是' : '否'}">是</td>
<td th:text="${goods.isFreeShipping ? '是' : '否'}">否</td>
<td th:text="${#dates.format(new java.util.Date(goods.onTime * 1000L), 'yyyy/MM/dd HH:mm:ss')}"></td>
<td th:text="${goods.isRecommend ? '是' : '否'}">是</td>
<td th:text="${goods.isNew ? '是' : '否'}">是</td>
<td th:text="${goods.isHot ? '是' : '否'}">否</td>
<td th:text="${goods.salesSum}"></td>
<td th:text="${goods.promType ? '是' : '否'}"></td>
</tr>
</tbody>
</table>
</div>
<div class="pagination">
<!-- First Page -->
<a th:href="@{/goodsList(pageNum=1, pageSize=${pageInfo.pageSize})}" th:if="${pageInfo.hasPreviousPage}" class="first-page">首页</a>
<!-- Previous Page -->
<a th:href="@{/goodsList(pageNum=${pageInfo.pageNum - 1}, pageSize=${pageInfo.pageSize})}" th:if="${pageInfo.hasPreviousPage}">上一页</a>
<!-- Page Numbers -->
<span th:each="page : ${pageInfo.navigatepageNums}">
<a th:href="@{/goodsList(pageNum=${page}, pageSize=${pageInfo.pageSize})}" th:text="${page}" th:classappend="${page == pageInfo.pageNum} ? 'active' : ''"></a>
</span>
<!-- Next Page -->
<a th:href="@{/goodsList(pageNum=${pageInfo.pageNum + 1}, pageSize=${pageInfo.pageSize})}" th:if="${pageInfo.hasNextPage}">下一页</a>
<!-- Last Page -->
<a th:href="@{/goodsList(pageNum=${pageInfo.pages}, pageSize=${pageInfo.pageSize})}" th:if="${pageInfo.hasNextPage}" class="last-page">末页</a>
</div>
</div>
</body>
</html>
测试
上面的步骤执行完毕之后，项目的结构如下：
启动项目，浏览器中输入http://localhost:8081/goodsList，显示效果如下：
此时，当我们点击第3页的时候，效果显示如下：
demo源码下载：
springboot-mybatis-pagehelper-demo
参考：
1167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
