条件分页查询

controller:

@DataTimeFormat是指定前端穿过来的日期形式

@GetMapping public Result page(@RequestParam(defaultValue = "1") Integer page, @RequestParam (defaultValue = "10") Integer pageSize, String name, Integer gender, //日期格式封装到LocalDate需要指定前端传递过来的日期时间格式 @DateTimeFormat(pattern = "yyyy-MM-dd")LocalDate begin, @DateTimeFormat(pattern = "yyyy-MM-dd")LocalDate end ){ log.info("分页查询:{},{},{},{},{},{}",page,pageSize,name,gender,begin,end); PageResult<Emp> pageResult = empService.page(page,pageSize,name,gender,begin,end); return Result.success(pageResult); }

Service:

@Override public PageResult<Emp> page(Integer page, Integer pageSize,String name, Integer gender, LocalDate begin, LocalDate end) { //设置分页参数(Pagehelper) PageHelper.startPage(page, pageSize); //执行查询 List<Emp> empList = empMapper.list(name,gender,begin, end); //解析查询结果，并封装 Page<Emp> p = (Page<Emp>) empList; return new PageResult<>(p.getTotal(),p.getResult()); }

EmpMapper:

public List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);

EmpMapper.xml：#{name}必须在concat里面写，不然会在字符串内识别不出

<?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.itheima.mapper.EmpMapper"> <select id="list" resultType="com.itheima.pojo.Emp"> select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.name like concat('%',#{name},'%') and e.gender = #{gender} and e.entry_date between #{begin} and #{end} order by e.update_time </select> </mapper>

优化一：

package com.itheima.pojo; import lombok.Data; import org.springframework.format.annotation.DateTimeFormat; import java.time.LocalDate; @Data public class EmpQueryParam { private Integer page = 1; //页码 private Integer pageSize = 10; //每页展示记录数 private String name; //姓名 private Integer gender; //性别 @DateTimeFormat(pattern = "yyyy-MM-dd") private LocalDate begin; //入职开始时间 @DateTimeFormat(pattern = "yyyy-MM-dd") private LocalDate end; //入职结束时间 }

@GetMapping public Result page(EmpQueryParam empQueryParam) { log.info("查询请求参数： {}", empQueryParam); PageResult pageResult = empService.page(empQueryParam); return Result.success(pageResult); }

public interface EmpService { /** * 分页查询 */ //PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end); PageResult page(EmpQueryParam empQueryParam); }

@Service public class EmpServiceImpl implements EmpService { @Autowired private EmpMapper empMapper; /*@Override public PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end) { //1. 设置PageHelper分页参数 PageHelper.startPage(page, pageSize); //2. 执行查询 List<Emp> empList = empMapper.list(name, gender, begin, end); //3. 封装分页结果 Page<Emp> p = (Page<Emp>) empList; return new PageResult(p.getTotal(), p.getResult()); }*/ public PageResult page(EmpQueryParam empQueryParam) { //1. 设置PageHelper分页参数 PageHelper.startPage(empQueryParam.getPage(), empQueryParam.getPageSize()); //2. 执行查询 List<Emp> empList = empMapper.list(empQueryParam); //3. 封装分页结果 Page<Emp> p = (Page<Emp>)empList; return new PageResult(p.getTotal(), p.getResult()); } }

@Mapper public interface EmpMapper { /** * 查询所有的员工及其对应的部门名称 */ // @Select("select e.*, d.name as deptName from emp e left join dept d on e.dept_id = d.id") // public List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end); /** * 根据查询条件查询员工 */ List<Emp> list(EmpQueryParam empQueryParam); }

EmpMapper.xml中代码无需修改。

动态SQL：