1. 开发环境
1.1 准备环境
SpringBoot 2.3.5.RELEASE
Pagehelper 1.4.1
Jdk 1.8
MySQL 8.1.0
postman
2. 功能描述
前后端分离,此功能为:后端对商品信息的分页查询
后端服务:controller+service+mapper
3. 核心内容
3.1 数据库表结构与内容
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for food
-- ----------------------------
DROP TABLE IF EXISTS `food`;
CREATE TABLE `food` (
`foodId` int(0) NOT NULL AUTO_INCREMENT COMMENT '食品编号',
`foodName` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '食品名称',
`foodExplain` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '食品介绍',
`foodPrice` decimal(5, 2) NOT NULL COMMENT '食品价格',
`businessId` int(0) NOT NULL COMMENT '所属商家编号',
`remarks` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`foodId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of food
-- ----------------------------
INSERT INTO `food` VALUES (1, '纯肉鲜肉(水饺)', '纯肉馅饺子', 16.00, 10001, NULL);
INSERT INTO `food` VALUES (2, '玉米鲜肉(水饺)', '玉米清香', 15.00, 10001, NULL);
INSERT INTO `food` VALUES (3, '虾仁三鲜(蒸饺)', '三鲜馅饺子', 20.00, 10001, NULL);
INSERT INTO `food` VALUES (4, '素三鲜(蒸饺)', '韭菜鸡蛋虾仁', 15.00, 10001, NULL);
INSERT INTO `food` VALUES (5, '角瓜鸡蛋(蒸饺)', '角瓜清香', 15.00, 10001, NULL);
INSERT INTO `food` VALUES (6, '小白菜肉(水饺)', '小白菜鲜肉',16.00, 10001, NULL);
INSERT INTO `food` VALUES (7, '芹菜牛肉(水饺)', '芹菜牛肉', 22.00, 10001, NULL);
INSERT INTO `food` VALUES (8, '虾腰鲜肉(蒸饺)', '虾仁腰子', 25.00, 10001, NULL);
INSERT INTO `food` VALUES (9, '青椒鲜肉(蒸饺)', '青椒鲜肉',18.00, 10001, NULL);
INSERT INTO `food` VALUES (10, '蛋黄焗豆花', '蛋黄豆花', 8.00, 10002, NULL);
INSERT INTO `food` VALUES (11, '麻婆豆腐', '麻辣味', 14.00, 10002, NULL);
INSERT INTO `food` VALUES (12, '广式小豆腐', '广东风味', 12.00, 10002, NULL);
SET FOREIGN_KEY_CHECKS = 1;
3.2 pom.xml 文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.bobo</groupId>
<artifactId>pageboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>pageboot</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<!-- 分页查询插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.70</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.3 application.properties 文件
server.port=8080
server.servlet.context-path=/bobo
# 开启驼峰命名(数据库表中的字段使用下划线,而对应的实体类对象使用驼峰命名规则)
mybatis.configuration.mapUnderscoreToCamelCase=true
spring.datasource.username=root
spring.datasource.password=12345678
spring.datasource.url=jdbc:mysql://localhost:3306/bobo?characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.bobo.pageboot.pojo
#pagehelper配置
#指定数据库,不指定的话会默认自动检测数据库类型
pagehelper.helper-dialect=mysql
#是否启用分页合理化。如果启用,当pagenum<1时,会自动查询第一页的数据,当pagenum>pages时,自动查询最后一页数据;不启用的,以上两种情况都会返回空数据
pagehelper.reasonable=true
#默认值false,分页插件会从查询方法的参数值中,自动根据上面 params 配置的字段中取值,查找到合适的值时就会自动分页
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql
3.4 pojo 类
3.4.1 pojo类=》Food类
package com.bobo.pageboot.pojo;
import lombok.Data;
@Data
public class Food {
private int FoodId;
private String FoodName;
private String FoodExplain;
private Double FoodPrice;
private int BusinessId;
private String remarks;
}
3.4.2 pojo类=》PageRequest类
package com.bobo.pageboot.pojo;
import lombok.Data;
import java.io.Serializable;
@Data
public class PageRequest<T> implements Serializable {
// 当前页码
private int page;
// 每页显示的行数
private int size;
// 实体对象
private T obj;
}
3.4.3 pojo类=》PageResponse类
package com.bobo.pageboot.pojo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
@Data
public class PageResponse<T> implements Serializable {
// 当前页码
private int page;
// 每页显示的行数
private int size;
// 总条数
private Long total;
// 结果集合
private List<T> result;
}
3.5 controller控制层
package com.bobo.pageboot.controller;
import com.alibaba.fastjson.JSONObject;
import com.bobo.pageboot.pojo.Food;
import com.bobo.pageboot.pojo.PageRequest;
import com.bobo.pageboot.pojo.PageResponse;
import com.bobo.pageboot.service.FoodService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/food")
public class FoodController {
@Autowired
private FoodService foodService;
@RequestMapping("/listFoodPageInfo")
public Object listFoodPageInfo(@RequestBody PageRequest<Food> pageRequest){
JSONObject jsonObject = new JSONObject();
PageResponse<Food> pageResponse = foodService.listFoodPageInfo(pageRequest);
if(pageResponse.getResult()==null||pageResponse.getResult().size()==0){
jsonObject.put("code",500);
jsonObject.put("msg","无数据");
}else{
jsonObject.put("code",200);
jsonObject.put("msg","查询成功");
jsonObject.put("data",pageResponse);
}
return jsonObject;
}
}
3.6 service 层
3.6.1 service接口
package com.bobo.pageboot.service;
import com.bobo.pageboot.pojo.Food;
import com.bobo.pageboot.pojo.PageRequest;
import com.bobo.pageboot.pojo.PageResponse;
public interface FoodService {
public PageResponse<Food> listFoodPageInfo(PageRequest<Food> pageRequest);
}
3.6.2 service接口实现类
package com.bobo.pageboot.service.impl;
import com.bobo.pageboot.mapper.FoodMapper;
import com.bobo.pageboot.pojo.Food;
import com.bobo.pageboot.pojo.PageRequest;
import com.bobo.pageboot.pojo.PageResponse;
import com.bobo.pageboot.service.FoodService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class FoodServiceImpl implements FoodService {
@Autowired
private FoodMapper foodMapper;
@Override
public PageResponse<Food> listFoodPageInfo(PageRequest<Food> pageRequest) {
//-PageHelper此代码放到前面执行
PageHelper.startPage(pageRequest.getPage(), pageRequest.getSize());
List<Food> foodList = foodMapper.listFoodPageInfo(pageRequest.getObj());
PageInfo<Food> pageInfo = new PageInfo<>(foodList);
PageResponse<Food> pageResponse = new PageResponse<>();
pageResponse.setPage(pageRequest.getPage());
pageResponse.setSize(pageRequest.getSize());
pageResponse.setTotal(pageInfo.getTotal());
pageResponse.setResult(pageInfo.getList());
return pageResponse;
}
}
3.7 Mapper 层
package com.bobo.pageboot.mapper;
import com.bobo.pageboot.pojo.Food;
import com.bobo.pageboot.pojo.PageRequest;
import com.bobo.pageboot.pojo.PageResponse;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface FoodMapper {
public List<Food> listFoodPageInfo(Food food);
}
3.8 mapper映射文件:FoodMapper.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.bobo.pageboot.mapper.FoodMapper">
<select id="listFoodPageInfo" parameterType="Food" resultType="Food">
select * from food
<where>
<if test="businessId!=null">
businessId = #{businessId}
</if>
</where>
</select>
</mapper>
4. 项目测试
4.1 启动项目
4.2 使用PostMan进行测试
4.2.1 测试1 (发送请求包含:页码与每页显示的条数)
4.2.2 测试2(发送请求包含:页码、每页显示的条数 及 商品的筛选条件)
到此,分页查询整理完毕!