业务背景介绍
在实现分页功能时,根据对象的某些字段进行高级搜索是一项常见的需求。比如下图所示,用户可以根据员工的政治面貌、民族、职位、入职日期区间等属性对查询结果进行筛选。
下文将以"江南一点雨"的微人事系统为例,简要介绍高级搜索功能的实现逻辑和注意事项。
一、controller层
controller层:
- 页数与个数利用@RequestParam标签设置默认值;
- 入职日期搜索区间用Date格式的数组(Date[] beginDateScope)进行接收;
@GetMapping("/")
public RespPageBean getEmployeeByPage(@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10") Integer size,
Employee employee,
Date[] beginDateScope) {
return employeeService.getEmployeeByPage(page, size, employee,beginDateScope);
}
分页结果查询通用响应参数:
@Data
public class RespPageBean {
private Long total; // 总个数
private List<?> data; // 响应实体数据
}
二、service层
service层 :
- 偏移量计算 ( 可优化 - > 对前端传回的页数与个数进行校验 ) ;
- 调用mapper层获取查询结果与总数 ;
- 生成响应类并赋值 ;
public RespPageBean getEmployeeByPage(Integer page,
Integer size,
Employee employee,
Date[] beginDateScope) {
if (page != null && size != null) {
page = (page - 1) * size;
}
List<Employee> data = employeeMapper.getEmployeeByPage(page, size, employee, beginDateScope);
Long total = employeeMapper.getTotal(employee, beginDateScope);
RespPageBean bean = new RespPageBean();
bean.setData(data);
bean.setTotal(total);
return bean;
}
三、mapper层
List<Employee> getEmployeeByPage(@Param("page") Integer page,
@Param("size") Integer size,
@Param("emp") Employee employee,
@Param("beginDateScope") Date[] beginDateScope);
Long getTotal(@Param("emp") Employee employee,
@Param("beginDateScope") Date[] beginDateScope);
四、XML层
mapper配置文件 :
- 查询实体语句getEmployeeByPage ;
- 获取查询结果总数语句getTotal ;
- 两部分的高级搜索语句重复, 可利用<sql>配合<include>标签实现语句复用 ;
<!--查询实体类-->
<select id="getEmployeeByPage" resultMap="AllEmployeeInfo">
SELECT
e.*,
p.`id` AS pid,
p.`name` AS pname,
n.`id` AS nid,
n.`name` AS nname,
d.`id` AS did,
d.`name` AS dname,
j.`id` AS jid,
j.`name` AS jname,
pos.`id` AS posid,
pos.`name` AS posname
FROM
employee e,
nation n,
politicsstatus p,
department d,
joblevel j,
position pos
WHERE
e.`nationId`= n.`id`
AND e.`politicId`= p.`id`
AND e.`departmentId`=d.`id`
AND e.`jobLevelId`=j.`id`
AND e.`posId`=pos.`id`
<include refid="base_where">
<if test="page != null and size!= null">
LIMIT #{page},#{size}
</if>
</select>
<!--查询总个数-->
<select id="getTotal" resultType="java.lang.Long">
SELECT count(*) FROM employee e
<where>
<include refid="base_where">
</where>
</select>
<sql id="base_where">
<if test="emp!=null">
<if test="emp.name !=null and emp.name!=''">
AND e.name LIKE concat('%',#{emp.name},'%')
</if>
<if test="emp.politicId !=null">
AND e.politicId = #{emp.politicId}
</if>
<if test="emp.nationId !=null">
AND e.nationId = #{emp.nationId}
</if>
<if test="emp.jobLevelId !=null">
AND e.jobLevelId = #{emp.jobLevelId}
</if>
<if test="emp.departmentId !=null">
AND e.departmentId = #{emp.departmentId}
</if>
<if test="emp.engageForm !=null and emp.engageForm!=''">
AND e.engageForm = #{emp.engageForm}
</if>
<if test="emp.posId !=null">
AND e.posId = #{emp.posId}
</if>
</if>
<if test="beginDateScope !=null">
AND e.beginDate BETWEEN #{beginDateScope[0]} AND #{beginDateScope[1]}
</if>
</sql>