Day10-09.案例-员工管理-分页查询条件

Day10-09.案例-员工管理-分页查询条件

1.条件分页查询-思路

EmpController：

1.接收参数(分页参数、查询条件)

2.调用Service进行分页条件查询

3.响应

EmpService：

1.使用PageHelper完成分页条件查询

2.封装PageBean对象，返回

EmpMapper：
    1.select * from emp where name like concat('%', ?, '%') and gender = ? and entrydate between ? and ? order by update_time desc;


==>  Preparing: select * from emp WHERE name like concat('%', ?, '%') order by update_time desc LIMIT ?
==> Parameters: (String), 5(Integer)
因为name是一个String类型的，前端如果没有传递，那么就是空字符串，所以判断name的时候还要判断name!=''
加上判断条件后就变成了下面这样
==>  Preparing: select * from emp order by update_time desc LIMIT ?
==> Parameters: 5(Integer)

EmpService
public interface EmpService{

    PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end);
}

EmpServiceImpl
 @Override
 public PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {

     //1.设置分页参数
     PageHelper.startPage(page, pageSize);

     //2.执行查询
     List<Emp> empList = empMapper.list(name, gender, begin, end);
     Page<Emp> p = (Page<Emp>)empList;

     //封装PageBean对象
     PageBean pageBean = new PageBean(p.getTotal(), p.getResult());
     return pageBean;
 }

EmpController
@Slf4j
@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

    @GetMapping("/emps")
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize,
                       String name, Short gender,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end
                       ){
        log.info("分页查询，参数：{}, {}, {}, {}, {}, {}", page, pageSize, name, gender, begin, end);
        //调用Service分页查询
        PageBean pageBean = empService.page(page, pageSize, name, gender, begin, end);

        return Result.success(pageBean);

    }
}

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

XML映射文件：
<?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 *
        from emp
        <where>
            <if test="name != null and name != ''">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>
</mapper>

2.小结：

条件分页查询：

​ 1.条件查询：动态SQL-XML映射文件

​ 2.分页查询：PageHelper分页插件