数据分页处理小陷阱

数据分页处理小陷阱

一、背景

很多时候，我们可能会遇到类似这样的一种需求。起一个定时，把符合某种状态的数据从数据表查出处理后更新回数据库表，同时因为数据量大需要做分页处理。

假设我们有这样一个表。
table1

id	name	amt	status
1	zhangsan	12	success
2	lisi1	13	init
3	lisi2	12	init
4	lisi2	16	success
5	lisi3	17	init
6	lisi4	19	success
7	lisi5	120	init
8	lisi6	1	success
9	lisi8	4	init
10	lisi0	123	init

我们会使用这样的sql分页处理：

//数据结果集总条数,依此进行分页
select count(*) from table1 where status = 'init' 

// mysql分页，按照每2条分页
select * from table1 where status = 'init' 
 limit 0,2

// db2  按照每2条分页
SELECT * FROM (
    select 
    ROW_NUMBER() OVER(ORDER BY P.ID DESC) AS ROWNUM,
    * from table1 where status = 'init' 
) A WHERE
    ROWNUM > 0
    AND ROWNUM <= 2

我们通过上面sql进行分页处理，处理完的每条数据，状态更新为success。实际上是有问题的。我们分析下，我们每处理完一条数据，sql结果集其实都发生了变化，我们的分页信息其实已经过时。

1. select * from table1 where status = 'init' 结果集为ID为2，3，5，7，9，10，

总条数：6
分页：
第一页行号边界：0 2    对应数据ID：2,3
第二页行号边界：2 4    对应数据ID：5，7
第三页行号边界：4 6    对应数据ID：9，10

1. 当我们处理完第一条ID为2的那条，状态变为了success，在不分页的情况下这条sql结果集变为了3，5，7，9，10。当我们查询第二页时行号2，4对应的记录为9，10。

第二次查询分页sql，实际得到的记录：
行号：2，4     数据记录ID：9，10  我们期望的数据记录是ID：5，7
第三次查询分页sql，没有查到任何记录。

总结：按照行号分页，行号对应的记录是动态变化的，查询到的数据是不全的，错乱的。比如上面例子，ID为5，7的记录并没有被查询到。

二、解决

1. 根据ID进行分页

通过上面的分页，我们知道使用行号分页，是不可行的。由于我们的ID是一般都是顺序自增（如果不是顺序的不能使用下面的方法）的我们使用ID进行分页是可以避免上面问题，因为ID是固定不变的。

总条数：6
分页：
第一页行号边界：0 2    对应数据ID：2,3
第二页行号边界：2 4    对应数据ID：5，7
第三页行号边界：4 6    对应数据ID：9，10

1. 第一页sql：
    select 
    * from table1 where status = 'init' 
    WHERE
    ID >  2 and ID <= 3
2. 第二页sql：
    select 
    * from table1 where status = 'init' 
    WHERE
    ID > 5 and ID <= 7
...

2. 如何获得分页ID的边界

使用ID分页，由于ID的值对应的记录是固定的，所以使用ID分页不会影响结果集。那么如何确定每一页的ID的边界值呢，首先符合条件的数据的ID的分布是不规则的，所以我们依然要通过行号确定分页的边界。

第一页行号边界：0 2    对应数据ID：2,3
第二页行号边界：2 4    对应数据ID：5，7
第三页行号边界：4 6    对应数据ID：9，10

我们只需要知道0,2,4,6四个行号对应的ID。参考如下sql：

SELECT ROWNUM FROM (
    select 
    ROW_NUMBER() OVER(ORDER BY P.ID DESC) AS ROWNUM,
    * from table1 where status = 'init' 
) A WHERE
    ROWNUM in (0,2,4,6)

有了每一页的边界的ID，我们就可以放心的一边分页查询，一边更新数据处理状态了。

三、分享一个分页的工具类

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;

/**
 * @description:分页通用类
 * @author: xiaobin.liu
 * @date: 17/12/19
 * @time: 上午11:48
 */
public class Pager implements Serializable {

    private static final long serialVersionUID = -4694628008139971984L;

    /**
     * 构造
     * @param recordTotal   总记录数
     */
    public Pager(int recordTotal) {
        this(1, 10, recordTotal);
    }

    /**
     * 构造
     * @param pageSize      每页数量
     * @param recordTotal   总记录数
     */
    public Pager( int pageSize, int recordTotal) {
        this(1, pageSize, recordTotal);
    }

    /**
     * 构造
     * @param currentPage   当前页
     * @param pageSize      每页数量
     * @param recordTotal   总记录数
     */
    public Pager(int currentPage, int pageSize, int recordTotal) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.recordTotal = recordTotal;
        otherAttr();
    }

    /**
     * currentPage 当前页
     */
    private int currentPage ;
    /**
     * pageSize 每页大小
     */
    private int pageSize ;
    /**
     * pageTotal 总页数
     */
    private int pageTotal;
    /**
     * recordTotal 总条数
     */
    private int recordTotal;
    /**
     * previousPage 前一页
     */
    private int previousPage;
    /**
     * nextPage 下一页
     */
    private int nextPage;
    /**
     * firstPage 第一页
     */
    private int firstPage = 1;
    /**
     * lastPage 最后一页
     */
    private int lastPage;
    /**
     * 当前页范围，下限，从0开始。SQL中应大于此值
     */
    private int lowRowNum;
    /**
     * 当前页范围，上限,最大recordTotal，SQL小于等于此值
     */
    private int highRowNum;

    /**
     * 设置其他参数
     *
     * @author kangxu
     *
     */
    private void otherAttr() {
        // 总页数
        this.pageTotal = this.recordTotal % this.pageSize > 0 ? this.recordTotal / this.pageSize + 1 : this.recordTotal / this.pageSize;
        // 第一页
        this.firstPage = 1;
        // 最后一页
        this.lastPage = this.pageTotal;
        refreshPager();
    }

    /**
     * 设置其他参数
     *
     * @author kangxu
     *
     */
    private void refreshPager() {
        // 前一页
        if (this.currentPage > 1) {
            this.previousPage = this.currentPage - 1;
        } else {
            this.previousPage = this.firstPage;
        }
        // 下一页
        if (this.currentPage < this.lastPage) {
            this.nextPage = this.currentPage + 1;
        } else {
            this.nextPage = this.lastPage;
        }
        //范围 根据当前页计算
        this.lowRowNum = this.pageSize * (this.currentPage - 1) ;
        this.highRowNum = this.currentPage * this.pageSize ;
        if (this.highRowNum > this.recordTotal) {
            this.highRowNum = this.recordTotal;
        }
    }

    // 放开私有属性
    public int getCurrentPage() {
        return currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public int getPageTotal() {
        return pageTotal;
    }

    public int getRecordTotal() {
        return recordTotal;
    }

    public int getPreviousPage() {
        return previousPage;
    }

    public int getNextPage() {
        return nextPage;
    }

    public int getFirstPage() {
        return firstPage;
    }

    public int getLastPage() {
        return lastPage;
    }

    public int getLowRowNum() {
        return lowRowNum;
    }

    public int getHighRowNum() {
        return highRowNum;
    }

    /**
     * 翻上一页
     */
    public Pager pageUp() {
        return goToPage(previousPage);
    }

    /**
     * 翻下一页
     */
    public Pager pageDown() {
        return goToPage(nextPage);
    }

    /**
     * 翻到第一页
     */
    public Pager gotoFirstPage() {
        return goToPage(firstPage);
    }

    /**
     * 翻到最后一页
     */
    public Pager gotoLastPage() {
        return goToPage(lastPage);
    }

    /**
     * 翻到指定页
     */
    public Pager goToPage(int pageNo) {
        if (pageNo <= 0) {
            this.currentPage = this.firstPage;
        } else if (pageNo > this.lastPage) {
            this.currentPage = this.lastPage;
        } else {
            this.currentPage = pageNo;
        }
        refreshPager();
        return this;
    }

    @Override
    public String toString() {
        return "Pager [currentPage=" + currentPage + ", pageSize=" + pageSize
                + ", pageTotal=" + pageTotal + ", recordTotal=" + recordTotal
                + ", previousPage=" + previousPage + ", nextPage=" + nextPage
                + ", firstPage=" + firstPage + ", lastPage=" + lastPage
                + ", lowRowNum=" + lowRowNum + ", highRowNum=" + highRowNum
                + "]";
    }

    public static void main(String[] args) {

        //创建一个分页
        Pager pager = new Pager(62);

        System.out.println(pager);
        System.out.println(pager.goToPage(4));
        System.out.println(pager.pageDown());
        System.out.println(pager.pageUp());
        System.out.println(pager.gotoFirstPage());
        System.out.println(pager.gotoLastPage());


        System.out.println(pager.goToPage(8));


        System.out.println(pager.gotoFirstPage());
        System.out.println("--------");
        for (int loopFlag = pager.getCurrentPage();loopFlag <= pager.getPageTotal() ;pager.pageDown(),loopFlag++) {
            System.out.println(pager);
        }

        // 遍历分页
        System.out.println("--------");
        Pager pager1 = new Pager(62);
        Set<Long> rownumSet = new HashSet<Long>();
        rownumSet.add(Long.valueOf(pager1.getLowRowNum()));
        for (int loopFlag = pager1.getCurrentPage();loopFlag <= pager1.getPageTotal() ;pager1.pageDown(),loopFlag++) {
            System.out.println(pager1);
            rownumSet.add(Long.valueOf(pager1.getHighRowNum()));
        }
        for (Long id : rownumSet) {
            System.out.println(id);
        }

    }

}