高级搜索功能：实现逻辑与注意事项

业务背景介绍

在实现分页功能时，根据对象的某些字段进行高级搜索是一项常见的需求。比如下图所示，用户可以根据员工的政治面貌、民族、职位、入职日期区间等属性对查询结果进行筛选。

下文将以"江南一点雨"的微人事系统为例，简要介绍高级搜索功能的实现逻辑和注意事项。

一、controller层

controller层：

1. 页数与个数利用@RequestParam标签设置默认值；
2. 入职日期搜索区间用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层 :

1. 偏移量计算 ( 可优化 - > 对前端传回的页数与个数进行校验 ) ;
2. 调用mapper层获取查询结果与总数 ;
3. 生成响应类并赋值 ;

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配置文件 :

1. 查询实体语句getEmployeeByPage ;
2. 获取查询结果总数语句getTotal ;
3. 两部分的高级搜索语句重复, 可利用<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>