本文已参与「新人创作礼」活动,一起开启掘金创作之路。
插件介绍
- PageHelper分页插件支持任何复杂的单表、多表分页。它提供了通用的分页查询功能,
- 使用它可以很方便的进行开发,可以节省开发人员大量的时间。
- 首先需要新建Springboot项目,下一步输入信息接着正常创建项目即可
Springboot集成mybatis的pagehelper插件yml配置
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
- pagehelper.reasonable: 分页合理化参数
- 默认值为false。默认false 时,直接根据参数进行查询
- 当该参数等于 true 时,pageNum<=0 时会查询第一页, pageNum>pages(即超过总数时),会查询最后一页。
- 配置helperDialect属性
- 分页插件会自动检测当前的数据库链接,自动选择合适的分页方式。
- 取值可为oracle,mysql,mariadb,sqlite,hsqldb,postgresql,db2,sqlserver,informix,h2,sqlserver2012,derby
- supportMethodsArguments:
- 支持通过 Mapper 接口参数来传递分页参数,默认值false,分页插件会从查询方法的参数值中,自动根据上面 params 配置的字段中取值,查找到合适的值时就会自动分页。
- 使用方法可以参考测试代码中的 com.github.pagehelper.test.basic 包下的 ArgumentsMapTest 和 ArgumentsObjTest。
- 特别注意:使用 SqlServer2012 数据库时,需要手动指定为 sqlserver2012,否则会使用 SqlServer2005 的方式进行分页。
maven引入
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>1.4.0</version>
</dependency>
SQL建表测试语句
/*
Navicat Premium Data Transfer
Source Server :
Source Server Type : MySQL
Source Server Version : 80024
Source Host :
Source Schema :
Target Server Type : MySQL
Target Server Version : 80024
File Encoding : 65001
Date: 03/01/2022 19:13:53
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for base_category1
-- ----------------------------
DROP TABLE IF EXISTS `base_category1`;
CREATE TABLE `base_category1` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分类名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '一级分类表' ROW_FORMAT = Compact;
-- ----------------------------
-- Records of base_category1
-- ----------------------------
INSERT INTO `base_category1` VALUES (1, '图书、音像、电子书刊');
INSERT INTO `base_category1` VALUES (2, '手机');
INSERT INTO `base_category1` VALUES (3, '家用电器');
INSERT INTO `base_category1` VALUES (4, '数码');
INSERT INTO `base_category1` VALUES (5, '家居家装');
INSERT INTO `base_category1` VALUES (6, '电脑办公');
INSERT INTO `base_category1` VALUES (7, '厨具');
INSERT INTO `base_category1` VALUES (8, '个护化妆');
INSERT INTO `base_category1` VALUES (9, '服饰内衣');
INSERT INTO `base_category1` VALUES (10, '钟表');
INSERT INTO `base_category1` VALUES (11, '鞋靴');
INSERT INTO `base_category1` VALUES (12, '母婴');
INSERT INTO `base_category1` VALUES (13, '礼品箱包');
INSERT INTO `base_category1` VALUES (14, '食品饮料、保健食品');
INSERT INTO `base_category1` VALUES (15, '珠宝');
INSERT INTO `base_category1` VALUES (16, '汽车用品');
INSERT INTO `base_category1` VALUES (17, '运动健康');
SET FOREIGN_KEY_CHECKS = 1;
application.yml连接数据库配置
- 连接方式一:Mysql5.x
spring:
datasource:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/base_category1?useUnicode=true&characterEncoding=utf8
#数据库账号密码
username: root
password: 123456
- 连接方式二:Mysql8
spring:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/base_category1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&failOverReadOnly=false
#数据库账号密码
username: root
password: 123456
创建IBaseCategory1Service与BaseCategory1Mapper接口类
- 在java文件夹下创建top文件夹,右键再新建interesting子文件夹,在子文件夹下新建service和mapper文件夹
- 接着创建IBaseCategory1Service与BaseCategory1Mapper接口类
public interface IBaseCategory1Service {
PageInfo<BaseCategory1> selectLists(Integer pageNum, Integer pageSize);
}
@Repository
public interface BaseCategory1Mapper extends BaseMapper<BaseCategory1> {
List<BaseCategory1> selectLists(Integer pageNum, Integer pageSize);
}
创建BaseCategory1Mapper.xml
- 在resources文件夹底下新建mapper文件夹,创建BaseCategory1Mapper.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="top.interesting.mapper.BaseCategory1Mapper">
<select id="selectLists" resultType="top.interesting.entity.BaseCategory1">
select * from `base_category1`
</select>
</mapper>
创建BaseCategory1ServiceImpl实现类
@Service
public class BaseCategory1ServiceImpl implements IBaseCategory1Service{
@Autowired
public BaseCategory1Mapper baseCategory1Mapper;
@Override
public PageInfo<BaseCategory1> selectLists(Integer pageNum, Integer pageSize){
PageHelper.startPage(pageNum,pageSize);
List<BaseCategory1> pageInfo = baseCategory1Mapper.selectLists(pageNum, pageSize);
return new PageInfo<>(pageInfo);
};
}
创建CategoryController控制层
@RestController
@RequestMapping("/product")
public class CategoryController {
@Autowired
private IBaseCategory1Service category1Service;
/**
*1.查询一级分类
*/
@GetMapping("/getCategory1")
public Result getCategory1(@RequestParam(value="pageNo",defaultValue="1")int pageNo,
@RequestParam(value="pageSize",defaultValue="10")int pageSize){
PageInfo<BaseCategory1> list = category1Service.selectLists(pageNo, pageSize);
//分页查询所有的一级分类
return Result.ok(list);
}
}
配置启动类启动项目即可
@SpringBootApplication
@MapperScan("top.interesting.mapper")
public class ProductApplication {
public static void main(String[] args) {
SpringApplication.run(ProductApplication.class, args);
}
}
- 启动后访问分页获取列表链接即可