做了几个项目，发现在这几个项目里面，都实现了分页查询效果，所以就总结一下，方便学习
我们基于黑马程序员的苍穹外卖来讲解分页查询的要点
🛸什么是分页查询分页查询是指将大量数据按照固定大小的页进行切分，每次查询只返回一页数据，通过不断翻页来获取全部数据。
🌹代码实现Result.java
这是一个common类，好多方法都可以使用
package com.sky.result;import lombok.Data;import java.io.Serializable;/*** 后端统一返回结果* @param <T>*/@Datapublic class Result<T> implements Serializable {private Integer code; //编码：1成功，0和其它数字为失败private String msg; //错误信息private T data; //数据public static <T> Result<T> success() {Result<T> result = new Result<T>();result.code = 1;return result;}public static <T> Result<T> success(T object) {Result<T> result = new Result<T>();result.data = object;result.code = 1;return result;}public static <T> Result<T> error(String msg) {Result result = new Result();result.msg = msg;result.code = 0;return result;}}所有的分页查询，我们都统一封装为PageResult对象，来表示分页查询结果
PageResult.java
package com.sky.result;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.util.List;/*** 封装分页查询结果*/@Data@AllArgsConstructor@NoArgsConstructorpublic class PageResult implements Serializable {private long total; //总记录数private List records; //当前页数据集合}EmployeeController.java
我们查看接口文档，发现接口路径是GET方式，并且请求参数是query，不是json，那么就不需要使用注解@ResponseBody了
package com.sky.controller.admin;import com.sky.constant.JwtClaimsConstant;import com.sky.dto.EmployeeDTO;import com.sky.dto.EmployeeLoginDTO;import com.sky.dto.EmployeePageQueryDTO;import com.sky.entity.Employee;import com.sky.properties.JwtProperties;import com.sky.result.PageResult;import com.sky.result.Result;import com.sky.service.EmployeeService;import com.sky.utils.JwtUtil;import com.sky.vo.EmployeeLoginVO;import io.swagger.annotations.ApiOperation;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;import java.util.HashMap;import java.util.Map;/*** 员工管理*/@RestController@RequestMapping("/admin/employee")@Slf4jpublic class EmployeeController {@Autowiredprivate EmployeeService employeeService;// @Autowired// private JwtProperties jwtProperties;//// /**// * 登录// *// * @param employeeLoginDTO// * @return// */// @PostMapping("/login")// public Result<EmployeeLoginVO> login(@RequestBody EmployeeLoginDTO employeeLoginDTO) {// log.info("员工登录：{}", employeeLoginDTO);//// Employee employee = employeeService.login(employeeLoginDTO);//// //登录成功后，生成jwt令牌// Map<String, Object> claims = new HashMap<>();// claims.put(JwtClaimsConstant.EMP_ID, employee.getId());// String token = JwtUtil.createJWT(// jwtProperties.getAdminSecretKey(),// jwtProperties.getAdminTtl(),// claims);//// EmployeeLoginVO employeeLoginVO = EmployeeLoginVO.builder()// .id(employee.getId())// .userName(employee.getUsername())// .name(employee.getName())// .token(token)// .build();//// return Result.success(employeeLoginVO);// }//// /**// * 退出// *// * @return// */// @PostMapping("/logout")// public Result<String> logout() {// return Result.success();// }//// @PostMapping// @ApiOperation("新增员工")// public Result save(@RequestBody EmployeeDTO employeeDTO){// log.info("新增员工：{}",employeeDTO);// return Result.success();// }@GetMapping("/page")@ApiOperation("员工分页查询")public Result<PageResult> page(EmployeePageQueryDTO employeePageQueryDTO){log.info("员工分页查询，参数为{}",employeePageQueryDTO);PageResult pageResult = employeeService.pageQuery(employeePageQueryDTO);//返回给Result//返回的对象是pageResultreturn Result.success(pageResult);}}注意，结果return的是Result.success(pageResult); 结果返回的是pageResult对象
在service层扩展一下分页查询方法
这里pageQuery会报错，我们再一个分页查询接口EmployeeService中完善这个方法
EmployeeService.java
package com.sky.service;import com.sky.dto.EmployeeDTO;import com.sky.dto.EmployeeLoginDTO;import com.sky.dto.EmployeePageQueryDTO;import com.sky.entity.Employee;import com.sky.result.PageResult;public interface EmployeeService {//Employee login(EmployeeLoginDTO employeeLoginDTO);//void save(EmployeeDTO employeeDTO);//分页查询PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO);}实现这个接口
EmployeeServiceImpl.java
package com.sky.service.impl;import com.github.pagehelper.Page;import com.github.pagehelper.PageHelper;import com.sky.constant.MessageConstant;import com.sky.constant.PasswordConstant;import com.sky.constant.StatusConstant;import com.sky.context.BaseContext;import com.sky.dto.EmployeeDTO;import com.sky.dto.EmployeeLoginDTO;import com.sky.dto.EmployeePageQueryDTO;import com.sky.entity.Employee;import com.sky.exception.AccountLockedException;import com.sky.exception.AccountNotFoundException;import com.sky.exception.PasswordErrorException;import com.sky.mapper.EmployeeMapper;import com.sky.result.PageResult;import com.sky.service.EmployeeService;import org.springframework.beans.BeanUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.util.DigestUtils;import java.time.LocalDateTime;import java.util.List;@Servicepublic class EmployeeServiceImpl implements EmployeeService {@Autowiredprivate EmployeeMapper employeeMapper;/*** 员工登录** @param employeeLoginDTO* @return*/// public Employee login(EmployeeLoginDTO employeeLoginDTO) {// String username = employeeLoginDTO.getUsername();// String password = employeeLoginDTO.getPassword();//// //1、根据用户名查询数据库中的数据// Employee employee = employeeMapper.getByUsername(username);//// //2、处理各种异常情况（用户名不存在、密码不对、账号被锁定）// if (employee == null) {// //账号不存在// throw new AccountNotFoundException(MessageConstant.ACCOUNT_NOT_FOUND);// }//// //密码比对// // TODO 后期需要进行md5加密，然后再进行比对// if (!password.equals(employee.getPassword())) {// //密码错误// throw new PasswordErrorException(MessageConstant.PASSWORD_ERROR);// }//// if (employee.getStatus() == StatusConstant.DISABLE) {// //账号被锁定// throw new AccountLockedException(MessageConstant.ACCOUNT_LOCKED);// }//// //3、返回实体对象// return employee;// }//// @Override// public void save(EmployeeDTO employeeDTO) {// Employee employee=new Employee();//// //对象属性拷贝// BeanUtils.copyProperties(employeeDTO,employee);//// //设置账号状态，默认正常状态// employee.setStatus(StatusConstant.ENABLE);//// //设置密码// //默认密码为123456// employee.setPassword(DigestUtils.md5DigestAsHex(PasswordConstant.DEFAULT_PASSWORD.getBytes()));//// //设置当前记录的创建时间和修改时间// employee.setCreateTime(LocalDateTime.now());// employee.setUpdateTime(LocalDateTime.now());//// //设置当前记录创建人的id和修改人id// employee.setCreateUser(BaseContext.getCurrentId());// employee.setUpdateUser(BaseContext.getCurrentId());//// employeeMapper.insert(employee);// }//分页查询public PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO){PageHelper.startPage(employeePageQueryDTO.getPage(),employeePageQueryDTO.getPageSize());Page<Employee> page=employeeMapper.pageQuery(employeePageQueryDTO);long total=page.getTotal();List<Employee> records=page.getResult();return new PageResult(total,records);}}这里我们使用了Mybatis的pagehelper插件 要使用这个插件，实现我们在pox.xml文件中导入下面的依赖
<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId></dependency>使用这个插件，会把我们后面的sql语句进行动态拼接。类似于MySQL的动态sql，会动态的把limit关键字拼接进去，并且进行动态计算
同理，这里pageQuery会报错，我们在接口DishMapper中完善这个方法
package com.sky.mapper;import com.github.pagehelper.Page;import com.sky.annotation.AutoFill;import com.sky.dto.DishPageQueryDTO;import com.sky.entity.Dish;import com.sky.enumeration.OperationType;import com.sky.vo.DishVO;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Select;import org.springframework.beans.factory.annotation.Autowired;@Mapperpublic interface DishMapper {/*** 根据分类id查询菜品数量* @param * @return*/// @Select("select count(id) from dish where category_id = #{categoryId}")// Integer countByCategoryId(Long categoryId);////// //插入菜品数据// @AutoFill(value = OperationType.INSERT)// void insert(Dish dish);//菜品分页查询Page<DishVO> pageQuery(DishPageQueryDTO dishPageQueryDTO);}注意 这里我们传入的是DishPageQueryDTO，我们看一下下图
下面我们来编写动态sql 对于动态sql，使用注解进行开发是比较麻烦的(因为我们要使用到动态标签)，所以我们把这段sql写到xml映射文件中
EmployeeMapper.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.sky.mapper.EmployeeMapper"><select id="pageQuery" resultType="com.sky.entity.Employee">select * from employee<where><if test="name!=null and name!=''">-- 动态拼接and name like concat('%',#{name},'%')</if></where>order by create_time desc</select></mapper> ⭐问题我们发现，界面展示的时间不是我们想要的 年–月--日，而是一大串数字，我们应该怎么解决呢 使用下面的方法进行解决
🎄解决方法在WebMvcConfiguration中扩展SpringMvc的消息转换器，统一对日期类型进行格式化处理
WebMvcConfiguration.java
package com.sky.config;import com.sky.interceptor.JwtTokenAdminInterceptor;import com.sky.json.JacksonObjectMapper;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.http.converter.HttpMessageConverter;import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter;import org.springframework.web.servlet.config.annotation.InterceptorRegistry;import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;import org.springframework.web.servlet.config.annotation.WebMvcConfigurationSupport;import springfox.documentation.builders.ApiInfoBuilder;import springfox.documentation.builders.PathSelectors;import springfox.documentation.builders.RequestHandlerSelectors;import springfox.documentation.service.ApiInfo;import springfox.documentation.spi.DocumentationType;import springfox.documentation.spring.web.plugins.Docket;import java.util.List;/*** 配置类，注册web层相关组件*/@Configuration@Slf4jpublic class WebMvcConfiguration extends WebMvcConfigurationSupport {@Autowiredprivate JwtTokenAdminInterceptor jwtTokenAdminInterceptor;//// /**// * 注册自定义拦截器// *// * @param registry// */// protected void addInterceptors(InterceptorRegistry registry) {// log.info("开始注册自定义拦截器...");// registry.addInterceptor(jwtTokenAdminInterceptor)// .addPathPatterns("/admin/**")// .excludePathPatterns("/admin/employee/login");// }//// /**// * 通过knife4j生成接口文档// * @return// */// @Bean// public Docket docket() {// ApiInfo apiInfo = new ApiInfoBuilder()// .title("苍穹外卖项目接口文档")// .version("2.0")// .description("苍穹外卖项目接口文档")// .build();// Docket docket = new Docket(DocumentationType.SWAGGER_2)// .apiInfo(apiInfo)// .select()// .apis(RequestHandlerSelectors.basePackage("com.sky.controller"))// .paths(PathSelectors.any())// .build();// return docket;// }//// /**// * 设置静态资源映射// * @param registry// */// protected void addResourceHandlers(ResourceHandlerRegistry registry) {// registry.addResourceHandler("/doc.html").addResourceLocations("classpath:/META-INF/resources/");// registry.addResourceHandler("/webjars/**").addResourceLocations("classpath:/META-INF/resources/webjars/");// }// //扩展springmvc的消息转换器protected void extendMessageConverters(List<HttpMessageConverter<?>> converters){log.info("扩展消息转换器");//创建一个消息转换器对象MappingJackson2HttpMessageConverter converter=new MappingJackson2HttpMessageConverter();//需要为消息转换器设置一个对象转换器，对象转换器可以将Java对象转换为json数据converter.setObjectMapper(new JacksonObjectMapper());//将自己的消息转换器加入到容器里面converters.add(0,converter);}}序列化：从Java对象生成json的过程 反序列化：把json解析为Java对象的过程
在上一段代码中，我们使用了对象转换器，但是我们应该怎么创建一个对象转换器呢 创建对象转换器
JacksonObjectMapper.java 下面的代码都是比较固定的
package com.sky.json;import com.fasterxml.jackson.databind.DeserializationFeature;import com.fasterxml.jackson.databind.ObjectMapper;import com.fasterxml.jackson.databind.module.SimpleModule;import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer;import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;import com.fasterxml.jackson.datatype.jsr310.deser.LocalTimeDeserializer;import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer;import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;import com.fasterxml.jackson.datatype.jsr310.ser.LocalTimeSerializer;import java.time.LocalDate;import java.time.LocalDateTime;import java.time.LocalTime;import java.time.format.DateTimeFormatter;import static com.fasterxml.jackson.databind.DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES;/*** 对象映射器:基于jackson将Java对象转为json，或者将json转为Java对象* 将JSON解析为Java对象的过程称为 [从JSON反序列化Java对象]* 从Java对象生成JSON的过程称为 [序列化Java对象到JSON]*/public class JacksonObjectMapper extends ObjectMapper {public static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";//public static final String DEFAULT_DATE_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";public static final String DEFAULT_DATE_TIME_FORMAT = "yyyy-MM-dd HH:mm";public static final String DEFAULT_TIME_FORMAT = "HH:mm:ss";public JacksonObjectMapper() {super();//收到未知属性时不报异常this.configure(FAIL_ON_UNKNOWN_PROPERTIES, false);//反序列化时，属性不存在的兼容处理this.getDeserializationConfig().withoutFeatures(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES);SimpleModule simpleModule = new SimpleModule().addDeserializer(LocalDateTime.class, new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern(DEFAULT_DATE_TIME_FORMAT))).addDeserializer(LocalDate.class, new LocalDateDeserializer(DateTimeFormatter.ofPattern(DEFAULT_DATE_FORMAT))).addDeserializer(LocalTime.class, new LocalTimeDeserializer(DateTimeFormatter.ofPattern(DEFAULT_TIME_FORMAT))).addSerializer(LocalDateTime.class, new LocalDateTimeSerializer(DateTimeFormatter.ofPattern(DEFAULT_DATE_TIME_FORMAT))).addSerializer(LocalDate.class, new LocalDateSerializer(DateTimeFormatter.ofPattern(DEFAULT_DATE_FORMAT))).addSerializer(LocalTime.class, new LocalTimeSerializer(DateTimeFormatter.ofPattern(DEFAULT_TIME_FORMAT)));//注册功能模块 例如，可以添加自定义序列化器和反序列化器this.registerModule(simpleModule);}}这样子就实现了分页查询效果
