看请假管理栏目的页面截图，很多人就放心了，因为页面还是以表格为主，所以前端代码比较简单，容易理解。其实请假管理栏目并不简单。虽说页面排版不复杂，但是归档请假单的时候，要把文件上传到腾讯云存储里面，这个过程还是挺复杂的。所以这一章跟以前其他章节有相似的地方，但是也有新技术等着我们挑战。
查询分页数据的时序图跟其他模块类似，这块业务我们非常的熟悉。
一、熟悉请假表
写后端代码之前，我们先要了解请假表的结构，然后才知道写SQL语句从这张表中提取哪些字段。tb_leave数据表的字段并不多，结构很简单。
二、编写持久层代码
在TbLeaveDao.xml文件中，定义SQL语句。因为普通用户只能看到自己的请假记录，而管理者能看到所有员工的请假记录，所以SQL语句中我设置了if条件判断。
<select id="searchLeaveByPage" parameterType="HashMap" resultType="HashMap">
SELECT l.id,
u.id AS userId,
u.`name`,
d.dept_name AS deptName,
DATE_FORMAT(l.`start`,'%Y-%m-%d %H:%i') AS `start`,
DATE_FORMAT(l.`end`,'%Y-%m-%d %H:%i') AS `end`,
l.type,
l.`status`,
l.reason,
l.instance_id AS instanceId,
IF(u.id = #{myId},TRUE,FALSE) AS mine
FROM tb_leave l
JOIN tb_user u ON l.user_id = u.id
LEFT JOIN tb_dept d ON u.dept_id = d.id
WHERE 1=1
<if test="userId != null">
AND u.id=#{userId}
</if>
<if test="name != null">
AND u.name LIKE '%${name}%'
</if>
<if test="deptId !=null">
AND d.id = #{deptId}
</if>
<if test="date !=null">
AND #{date} BETWEEN DATE_FORMAT(l.`start`,'%Y-%m-%d') AND DATE_FORMAT(l.`end`,'%Y-%m-%d')
</if>
<if test="status !=null">
AND l.status = #{status}
</if>
<if test="type !=null">
AND l.type = #{type}
</if>
ORDER BY l.id DESC
LIMIT #{start}, #{length}
</select>
<select id="searchLeaveCount" parameterType="HashMap" resultType="long">
SELECT COUNT(*)
FROM
tb_leave l
JOIN tb_user u ON l.user_id = u.id
LEFT JOIN tb_dept d ON u.dept_id = d.id
WHERE 1=1
<if test="userId != null">
AND u.id=#{userId}
</if>
<if test="name != null">
AND u.name LIKE '%${name}%'
</if>
<if test="deptId !=null">
AND d.id = #{deptId}
</if>
<if test="date !=null">
AND #{date} BETWEEN DATE_FORMAT(l.`start`,'%Y-%m-%d') AND DATE_FORMAT(l.`end`,'%Y-%m-%d')
</if>
<if test="status !=null">
AND l.status = #{status}
</if>
<if test="type !=null">
AND l.type = #{type}
</if>
</select>
在TbLeaveDao.java接口中，定义DAO方法。
public interface TbLeaveDao {
……
public ArrayList<HashMap> searchLeaveByPage(HashMap param);
public long searchLeaveCount(HashMap param);
}
二、编写业务层代码
创建LeaveService.java接口，声明抽象方法。
public interface LeaveService {
public PageUtils searchLeaveByPage(HashMap param);
}
创建LeaveServiceImpl.java类，实现抽象方法。
@Service
public class LeaveServiceImpl implements LeaveService {
@Autowired
private TbLeaveDao leaveDao;
@Override
public PageUtils searchLeaveByPage(HashMap param) {
ArrayList<HashMap> list = leaveDao.searchLeaveByPage(param);
long count = leaveDao.searchLeaveCount(param);
int start = (Integer) param.get("start");
int length = (Integer) param.get("length");
PageUtils pageUtils = new PageUtils(list, count, start, length);
return pageUtils;
}
}
三、编写Web层代码
创建SearchLeaveByPageForm.java类，封装Ajax提交的数据。
@Data
@Schema(description = "查询请假分页表单")
public class SearchLeaveByPageForm {
@NotNull(message = "page不能为空")
@Min(value = 1, message = "page不能小于1")
@Schema(description = "页数")
private Integer page;
@NotNull(message = "length不能为空")
@Range(min = 10, max = 50, message = "length必须在10~50之间")
@Schema(description = "每页记录数")
private Integer length;
@Pattern(regexp = "^[\\u4e00-\\u9fa5]{1,10}$", message = "name内容不正确")
@Schema(description = "姓名")
private String name;
@Min(value = 1, message = "deptId不能小于1")
@Schema(description = "部门编号")
private String deptId;
@Pattern(regexp = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$", message = "date内容不正确")
@Schema(description = "日期")
private String date;
@Range(min = 1, max = 2, message = "type只能是1或者2")
@Schema(description = "类型")
private Byte type;
@Range(min = 1, max = 3, message = "状态只能是1~3范围")
@Schema(description = "状态")
private Byte status;
}
创建LeaveController.java类，定义Web方法。
@RestController
@RequestMapping("/leave")
@Tag(name = "LeaveController", description = "员工请假Web接口")
public class LeaveController {
@Autowired
private LeaveService leaveService;
@PostMapping("/searchLeaveByPage")
@Operation(summary = "查询请假分页数据")
@SaCheckLogin
public R searchLeaveByPage(@Valid @RequestBody SearchLeaveByPageForm form) {
int page = form.getPage();
int length = form.getLength();
int start = (page - 1) * length;
HashMap param = JSONUtil.parse(form).toBean(HashMap.class);
param.put("start", start);
param.put("myId", StpUtil.getLoginIdAsInt());
if (!(StpUtil.hasPermission("LEAVE:SELECT")||StpUtil.hasPermission("ROOT"))) {
param.put("userId", StpUtil.getLoginIdAsInt());
}
PageUtils pageUtils = leaveService.searchLeaveByPage(param);
return R.ok().put("page", pageUtils);
}
}