MySQL limit分页运用

Limit关键字使用

在Mysql中，如果是数据量小的场景，可以通过select * from t limti offset,rows进行分页查询。offset偏移量，默认从0开始；rows：返回的行数。比如select * from t limit 2,10，表示从全表的第三行开始，返回10条。

Java分页的运用

基于前面提到的limit，我们实际可以通过接收前端传来的分页参数即可实现分页逻辑。前提是我们需要自己分装一个分页工具类:

@ApiModel(value = "分页对象")
@NoArgsConstructor
public class PageDTO<T> {

    @ApiModelProperty(value = "数据列表")
    private List<T> list;

    @ApiModelProperty(value = "总页数")
    private Integer totalPage;

    @ApiModelProperty(value = "当前数")
    private Integer currentPage;

    @ApiModelProperty(value = "每页显示行数")
    private Integer pageSize;

    @ApiModelProperty(value = "是否第一页")
    private Boolean isFirstPage;

    @ApiModelProperty(value = "是否最后一页")
    private Boolean isLastPage;

    @ApiModelProperty(value = "数据总量")
    private Long totalQty;


    public PageDTO(Integer page, Long totalQty, Integer pageSize, List<T> list) {
        this.currentPage = countCurrentPage(page);
        if (null == pageSize) {
            this.totalPage = 1;
        } else {
            this.totalPage = countTotalPage(pageSize, totalQty.intValue());
        }
        this.list = list;
        this.isFirstPage = isFirstPage();
        this.isLastPage = isLastPage();
        this.totalQty = totalQty;
        this.pageSize = pageSize;
    }

    public Long getTotalQty() {
        return totalQty;
    }

    public void setTotalQty(Long totalQty) {
        this.totalQty = totalQty;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public Boolean isFirstPage() {
        return currentPage == 1;
    }

    public Boolean isLastPage() {
        return currentPage.equals(totalPage);
    }

    public static Integer countTotalPage(Integer pageSize, Integer allRow) {
        Integer totalPage = allRow % pageSize == 0 ? allRow / pageSize : allRow
                / pageSize + 1;
        return totalPage;
    }

    public static Integer countBegin(Integer pageSize, Integer currentPage) {
        final Integer begin = pageSize * (currentPage - 1);
        return begin;
    }

    public static Integer countCurrentPage(Integer page) {
        final Integer curPage = ((null == page || page == 0) ? 1 : page);
        return curPage;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public Boolean getIsFirstPage() {
        return isFirstPage;
    }

    public Boolean getIsLastPage() {
        return isLastPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

}

Service实现

    @Override
    public PageDTO<List<PmsBrand>> listBrand(int currentPage, int pageSize) {

        Integer begin = PageDTO.countBegin(pageSize, currentPage);
        List<PmsBrand>brandList=brandMapper.selectBrandPage(begin,pageSize);
        Long total=brandMapper.totalCount();
        return new PageDTO(currentPage,total,pageSize,brandList);
    }

通过前端传来的当前页和每页显示大小即可。这样，我们基于MySQL LIMIT关键字就能实现一个分页功能