1. Controller
@ApiOperation("分页查询")
@GetMapping("/page")
public Result<PageResult> pageResultResult(EmployeePageQueryDTO employeePageQueryDTO) {
System.out.println(employeePageQueryDTO.toString());
PageResult pageResult = employeeService.pageQuery(employeePageQueryDTO);
return Result.success(pageResult);
}
2. Service
这里使用PageHelper协助完成分页查询操作。PageHelper基于ThreadLocal实现,ThreadLocal会保存page和pageSize,在执行sql语句时候自动填补相应的limit语句。
@Override
public PageResult pageQuery(EmployeePageQueryDTO employeePageQueryDTO) {
PageHelper.startPage(employeePageQueryDTO.getPage(), employeePageQueryDTO.getPageSize());
Page<Employee> employeePage = employeeMapper.pageQuery(employeePageQueryDTO);
val total = employeePage.getTotal();
val result = employeePage.getResult();
return new PageResult(total, result);
}
3. Mapper
这里使用 where 标签设置查询条件,使用 if 标签判断 name 字段是否为空。这里对于 name 字段使用模糊查询进行匹配,使用 concat 进行连接。
<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>