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分页插件