实现分页查询
目录一、分页查询二、PageHelper分页查询三、条件分页查询一、分页查询前端传递page(页数)pageSize(每页大小)后端接收后查询返回结果。实体类total是总数ListT rows是查询到的数据数据肯定不止一条用List接收Data AllArgsConstructor NoArgsConstructor public class PageResultT { private Long total; private ListT rows; }controller层接收page和pageSize设置默认值GetMapping public Result page(RequestParam(defaultValue 1) Integer page,RequestParam(defaultValue 10) Integer pageSize) { log.info(分页查询, 参数: {}, {}, page, pageSize); PageResultEmp pageResult empService.page(page, pageSize); return Result.success(pageResult); }service层Override public PageResultEmp page(Integer page, Integer pageSize) { Long total empMapper.count(); Integer start (page - 1) * pageSize; ListEmp rows empMapper.list(start, pageSize); return new PageResultEmp(total, rows); }mapper层/** * 查询总记录数 * return */ Select(select count(*) from emp e left join dept d on e.dept_id d.id) public Long count(); /** * 分页查询 * return */ Select(select e.*, d.name deptName from emp e left join dept d on e.dept_id d.id order by e.update_time desc limit #{start}, #{pageSize}) public ListEmp list(Integer start, Integer pageSize);二、PageHelper分页查询使用mybatis插件来实现分页查询。添加依赖dependency groupIdcom.github.pagehelper/groupId artifactIdpagehelper-spring-boot-starter/artifactId version1.4.7/version /dependency实体类和controller层与上面一致。service层需要注意定义的SQL语句结尾不能加;因为PageHelper在运行分页查询的时候会在我们写的SQL语句后面直接添加limit加;会报错PageHelper只会对后面的第一个查询语句进行分页处理Override public PageResultEmp page(Integer page, Integer pageSize) { PageHelper.startPage(page, pageSize); ListEmp empList empMapper.list(); PageEmp pageInfo (PageEmp) empList; return new PageResult(pageInfo.getTotal(), pageInfo.getResult()); }mapper层Select(select e.*, d.name deptName from emp e left join dept d on e.dept_id d.id order by e.update_time desc) public ListEmp list();三、条件分页查询在原本的程序基础上接收条件并使用查询。实体类为了方便传参写一个实体类Data AllArgsConstructor NoArgsConstructor 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; }controller层GetMapping public Result page(EmpQueryParam empQueryParam) { log.info(分页查询, 参数: {}, empQueryParam); PageResultEmp pageResult empService.page(empQueryParam); return Result.success(pageResult); }service层Override public PageResultEmp page(EmpQueryParam empQueryParam) { PageHelper.startPage(empQueryParam.getPage(), empQueryParam.getPageSize()); ListEmp empList empMapper.list(empQueryParam); PageEmp pageInfo (PageEmp) empList; return new PageResult(pageInfo.getTotal(), pageInfo.getResult()); }mapper层语法比较复杂所以在xml文件中写更加方便public ListEmp list(EmpQueryParam empQueryParam);select idlist resultTypeyixiao.tlias.pojo.Emp select e.*, d.name deptName from emp e left join dept d on e.dept_id d.id where if testname ! null and name ! e.name like concat(%, #{name}, %) /if if testgender ! null and e.gender #{gender} /if if testbegin ! null and end ! null and e.entry_date between #{begin} and #{end} /if /where order by e.update_time desc /select
)
