spring boot 案例---分页查询

开发规范---restful

 分页查询

 

分页查询sql语法
参数1：起始索引=（页码-1）*每页展示记录数
参数2：查询返回记录数=每页展示记录数
查询第一页，每页五条
select * from emp limit 0,5;
查询第二页
select * from emp limit 5,5;

@RequestParam(defaultValue = "1")Integer page,
@RequestParam(defaultValue = "10") Integer pageSize

@ReqParam可以为page和pagesize利用defaultValue属性来设置默认值

条件分页查询

pojo层
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
    private Long total;//总记录数
    private List rows;//数据列表
}

controller层
 @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);
        PageBean pageBean=empService.pagebean(page,pageSize,name, gender, begin, end);
        return Result.success(pageBean);
    }
service层
    PageBean pagebean(Integer start, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end);//接口
//实现类
    @Override
    public PageBean pagebean(Integer page, Integer pageSize,String name, Short gender, LocalDate begin, LocalDate end ) {
        Long count=empMapper.count();

        Integer start=(page-1)*pageSize;

        List<Emp> list=empMapper.page(start,pageSize, name, gender, begin,end);
        PageBean pageBean=new PageBean(count,list);
        return pageBean;
    }
mapper层
    public List<Emp> page(Integer start, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end);
//xml
   <select id="page" resultMap="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>
        limit #{start},#{pageSize}
    </select>

 

 分页插件PageHelper

依赖