SpringBoot整合PageHelper

2020-09-15

PageHelper是一款开源的Mybatis第三方物理分页插件。支持MySQL,Oracle,MariaDB,SQLite,DB2,PostgreSQL,SqlServer 等数据库。

1. 引入依赖

pom.xml

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

2. 配置文件中配置

application.properties

###############PageHelper ##################
# 数据库类型
pagehelper.helper-dialect=mysql
# 启用合理化时，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页
# 默认不启用，如果pageNum<1或pageNum>pages会返回空数据
pagehelper.reasonable=true
# 支持通过 Mapper 接口参数来传递分页参数
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql
# 设置为true时，如果pageSize = 0 就会查出所有的记录，但是返回结果仍为page类型
pagehelper.page-size-zero=true

3. Dao层

@Repository
public interface UserDao {
    List<User> getAllUsers();
}

4. mapper.xml

<select id="getAllUsers" resultMap="BaseResultMap">
    select id,user_name,password
    from t_user
    order by id desc
</select>

5. Service层

在这里查询的时候，设置分页

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    /**
     * @param page 页码
     * @param size 每页记录数
     */
    public PageInfo<User> getAllUsers(Integer page, Integer size) {
        //开启分页查询，写在查询语句上方
        //只有紧跟在PageHelper.startPage方法后的第一个Mybatis的查询（Select）方法会被分页。
        PageHelper.startPage(page, size);
        List<User> users = userDao.getAllUsers();
        PageInfo<User> pageInfo = new PageInfo<>(users);
        return pageInfo;
    }
}

6. Controller层

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/all")
    public PageInfo<User> getAllUsers(@RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "0") Integer size) {
        return userService.getAllUsers(page, size);
    }

}

7. 数据库

创建了一个名为springboot_pagehelper的数据库，插入了20条记录

-- 创建数据库
create database IF NOT EXISTS `springboot_pagehelper` default character set utf8mb4 collate utf8mb4_general_ci;

use springboot_pagehelper;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 'LiMing', '123');
INSERT INTO `t_user` VALUES (2, 'ZhangHong', '456');
INSERT INTO `t_user` VALUES (3, '小刚', '789');
INSERT INTO `t_user` VALUES (4, 'Srammy', '@12#34$');
INSERT INTO `t_user` VALUES (5, 'cammy', '@12#34$');
INSERT INTO `t_user` VALUES (6, 'Lili', 'asd');
INSERT INTO `t_user` VALUES (7, 'Luke', 'qwe');
INSERT INTO `t_user` VALUES (8, 'Liya', '4rf');
INSERT INTO `t_user` VALUES (9, 'c3po', 'Rds$#');
INSERT INTO `t_user` VALUES (10, 'r2d2', 'dhjshs');
INSERT INTO `t_user` VALUES (11, 'obiwang', '3434324bnbmn');
INSERT INTO `t_user` VALUES (12, 'kim', 'ioioio*74$%');
INSERT INTO `t_user` VALUES (13, 'sith', 'oopp');
INSERT INTO `t_user` VALUES (14, 'yoda', 'Link');
INSERT INTO `t_user` VALUES (15, 'roya', 'zelda4334');
INSERT INTO `t_user` VALUES (16, 'rick', 'zelda$%');
INSERT INTO `t_user` VALUES (17, 'morty', 'llklkk54');
INSERT INTO `t_user` VALUES (18, 'light', 'yahahawewe333');
INSERT INTO `t_user` VALUES (19, 'pinkmim', '43473847384');
INSERT INTO `t_user` VALUES (20, 'mario', 'falsh3434f#@$');
SET FOREIGN_KEY_CHECKS = 1;

7. 启动，请求

在postman地址栏中输入localhost:8089/all?page=4&size=3，请求方式为Get。请求第4页的数据，一共三条记录(每页三条记录)。

发起请求后，返回

{
    "total": 20, // 一共有20条记录
    "list": [
        {
            "userId": 11,
            "username": "obiwang",
            "password": "3434324bnbmn"
        },
        {
            "userId": 10,
            "username": "r2d2",
            "password": "dhjshs"
        },
        {
            "userId": 9,
            "username": "c3po",
            "password": "Rds$#"
        }
    ],
    "pageNum": 4,
    "pageSize": 3,
    "size": 3,
    "startRow": 10,
    "endRow": 12,
    "pages": 7,   // 一共7页
    "prePage": 3,
    "nextPage": 5,
    "isFirstPage": false,
    "isLastPage": false,
    "hasPreviousPage": true,
    "hasNextPage": true,
    "navigatePages": 8,
    "navigatepageNums": [
        1,
        2,
        3,
        4,
        5,
        6,
        7
    ],
    "navigateFirstPage": 1,
    "navigateLastPage": 7,
    "firstPage": 1,
    "lastPage": 7
}

参考：