(带界面)SpringBoot整合PageHelper实现分页

东边的大西瓜

发布于 2022-05-05 12:24:39

7070

发布于 2022-05-05 12:24:39

(带界面)SpringBoot整合PageHelper实现分页

背景

在我们的业务开发中，查询出的数据可能成千上万条，如果将大量数据一次性全部展示给客户，不仅会照成性能问题，也会会造成很不好的用户体验，界面延迟展示、界面过长等问题。而且用户大概率也不会想一次性得到全部的数据，在这种情况下我们就应该使用分页来分批次展示数据了。主流数据库也为我们提供了相应的分页功能，比如mysql的limit。

认识PageHelper

PageHelper是一款免费开源的MyBatis第三方物理分页插件，也是MyBatis官方推荐的分页插件。推荐一波，PageHepler的作者是资深Java工程师刘增辉老师，我就是通过拜读他所书写的《MyBatis入门到精通一书》来进行的MyBatis学习，很不错的一本书，钱包宽裕的同学可以get一本看看。

项目说明

1.项目目录总览

2.环境配置

JDK 8
Mysql 8
IDEA 2019.3.5
SpringBoot 2.3.7
MyBatis_SpringBoot 2.1.4 (对应MyBatis 3.5.6)
PageHelper_SpringBoot 1.2.3 (对应PageHelper 5.1.2)
Thymeleaf 模板驱动
Semantic UI 前端框架

3.SQL建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 18);
INSERT INTO `user` VALUES (2, '李四', 19);
INSERT INTO `user` VALUES (3, '王五', 20);
INSERT INTO `user` VALUES (4, '小明', 13);
INSERT INTO `user` VALUES (5, '小红', 14);
INSERT INTO `user` VALUES (6, '小刚', 15);
INSERT INTO `user` VALUES (7, '小天', 16);
INSERT INTO `user` VALUES (8, '王刚', 18);

SET FOREIGN_KEY_CHECKS = 1;

实战SpringBoot整合PageHelper实现分页

1. 引入MyBatis和PageHelper

<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>
<!--pageHelper-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.3</version>
</dependency>

2. 创建User实体类

package com.zzxkj.demo.entity;
import lombok.Data;
@Data
public class User {
    private Long id;
    private String username;
    private int age;
}

3. 配置MyBatis和PageHelper

#mybatis的配置
mybatis.mapper-locations=classpath:mapper/*Mapper.xml 
mybatis.type-aliases-package=com.zzxkj.demo.entity

#pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

4. 创建UserMapper.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.zzxkj.demo.mapper.UserMapper">
    <resultMap id="UserMap" type="com.zzxkj.demo.entity.User">
        <id property="id" column="user_id"></id>
        <result property="username" column="username"></result>
        <result property="age" column="age"></result>
    </resultMap>
    <select id="listUsers" resultMap="UserMap">
        select *from user
    </select>
</mapper>

5. 创建UserMapper接口

package com.zzxkj.demo.mapper;
import com.zzxkj.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
import java.util.List;
@Mapper
@Component
public interface UserMapper {
    List<User> listUsers();
}

6. 创建UserController

package com.zzxkj.demo.controller;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zzxkj.demo.entity.User;
import com.zzxkj.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import java.util.List;
@Controller
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @GetMapping("/user")
    public String getUsers(@RequestParam(value = "pageNum",defaultValue = "1")Integer pageNum, Model model){
        PageHelper.startPage(pageNum,3); 
        List<User> users = userMapper.listUsers(); //响应的查询语句要紧跟在PageHelper分页语句后
        PageInfo<User> pageInfo = new PageInfo<>(users);//使用PageInfo包装类
        model.addAttribute("pageInfo",pageInfo); 
        return "user";
    }
}

7.创建user.html测试界面

<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" type="text/css" href="../static/css/semantic.min.css" th:href="@{/css/semantic.min.css}">
</head>
<body>
                 <!--遍历输出内容-->
                 <div class="segment"  align="center" th:each="user : ${pageInfo.list}">
                     <span th:text="${user.id}"></span>
                     <span th:text="${user.username}"></span>
                     <span th:text="${user.age}"></span>
                 </div>
                 <!--分页跳转-->
                 <div class="ui bottom attached segment">
                     <div class="ui middle aligned grid">
                         <div class="five wide column"></div>
                         <div class="two wide column" align="center">
                             <a class="item" th:href="@{/user/(pageNum=${pageInfo.hasPreviousPage}?${pageInfo.prePage}:1)}" th:unless="${pageInfo.isFirstPage}">上一页</a>
                         </div>

                         <div class="two wide column" align="center">
                             <p> <span th:text="${pageInfo.pageNum}"></span> / <span th:text="${pageInfo.pages}"></span> </p>
                         </div>

                         <div class="tow wide column" align="center">
                             <a class="item" th:href="@{/user/(pageNum=${pageInfo.hasNextPage}?${pageInfo.nextPage}:${pageInfo.pages})}" th:unless="${pageInfo.isLastPage}">下一页</a>
                         </div>
                         <div class="five wide column"></div>
                     </div>
                 </div>

<script src="../static/js/jquery-3.2.1.min.js" th:src="@{/js/jquery-3.2.1.min.js}"></script>
<script src="../static/js/semantic.min.js" th:src="@{/js/semantic.min.js}"></script>
</body>
</html>