今天记录下 Mybatis 分页插件 pageHelper 的使用。
背景
有一个员工表(employee),现在要使用 pageHelper 插件实现员工的分页查询。
员工表
create table employee
(
id bigint auto_increment comment '主键'
primary key,
name varchar(32) not null comment '姓名',
username varchar(32) not null comment '用户名',
password varchar(64) not null comment '密码',
phone varchar(11) not null comment '手机号',
sex varchar(2) not null comment '性别',
id_number varchar(18) not null comment '身份证号',
status int default 1 not null comment '状态 0:禁用,1:启用',
create_time datetime null comment '创建时间',
update_time datetime null comment '更新时间',
create_user bigint null comment '创建人',
update_user bigint null comment '修改人',
constraint idx_username
unique (username)
)
comment '员工信息' collate = utf8_bin;
实体类
Employee
employee类定义代码如下
package com.sky.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.time.LocalDateTime;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String username;
private String name;
private String password;
private String phone;
private String sex;
private String idNumber;
private Integer status;
//@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
//@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime;
private Long createUser;
private Long updateUser;
}
EmployeeDTO
EmployeeDTO定义代码如下
package com.sky.dto;
import lombok.Data;
import java.io.Serializable;
@Data
public class EmployeeDTO implements Serializable {
private Long id;
private String username;
private String name;
private String phone;
private String sex;
private String idNumber;
}
封装类
PageResult
最后返回给前端的数据封装在 PageResult 类中, PageHelper 类定义如下:
/**
* 封装分页查询结果
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult implements Serializable {
//总记录数
private long total;
//当前页数据集合
private List records;
}
EmployeePageQueryDTO
分页查询类
@Data
public class EmployeePageQueryDTO implements Serializable {
//员工姓名
private String name;
//页码
private int page;
//每页显示记录数
private int pageSize;
}
Result
Result 类为后端统一返回给前端的类型,定义如下:
package com.sky.result;
import lombok.Data;
import java.io.Serializable;
/**
* 后端统一返回结果
* @param <T>
*/
@Data
public 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;
}
}
业务实现
Controller 层
定义 EmployeeController
/**
* 员工管理
*/
@RestController
@RequestMapping("/admin/employee")
@Slf4j
@Api(tags = "员工相关接口")
public class EmployeeController {
@Autowired
private EmployeeService employeeService;
/**
* @Description ToDo 员工分页查询
* @param employeePageQueryDTO
* @return Result<PageResult>
*/
@GetMapping("/page")
@ApiOperation("员工分页查询")
public Result<PageResult> page(EmployeePageQueryDTO employeePageQueryDTO){
PageResult pageResult = employeeService.pageQuery(employeePageQueryDTO);
return Result.success(pageResult);
}
}
Service 层
@Service
public class EmployeeServiceImpl implements EmployeeService {
@Autowired
private EmployeeMapper employeeMapper;
@Override
public PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO) {
/**
select * from employee limit start, pageSize
传统方法时需要手动计算 start 和 pageSize 的值,这里可以直接通过startPage方法很方便的实现该功能。
*/
PageHelper.startPage(employeePageQueryDTO.getPage(), employeePageQueryDTO.getPageSize());
// 这里要返回 Page 类,泛型类型是对应的要返回的实体类,这里是 Employee,包含数据总条数和所有数据
Page<Employee> page = employeeMapper.pageQuery(employeePageQueryDTO);
// 将查询到的数据封装到 PageResult 类中
PageResult pageResult = new PageResult(page.getTotal(), page.getResult());
return pageResult;
}
}
Mapper 层
EmployeeMapper
@Mapper
public interface EmployeeMapper {
/**
* 员工信息分页查询
* @param employeePageQueryDTO
* @return PageResult
*/
Page<Employee> pageQuery(EmployeePageQueryDTO employeePageQueryDTO);
}
SQL语句编写
因为这里的 sql 语句较为麻烦,因此使用 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>
效果
分页查询效果