mysql 大量数据时分页查询

1. 分页查询

• 写一个实体类 Paging

public class Paging implements Serializable {

    private static final long serialVersionUID = 1L;

    private int startNum;
    private int endNum;

    public int getStartNum() {
        return startNum;
    }

    public void setStartNum(int startNum) {
        this.startNum = startNum;
    }

    public int getEndNum() {
        return endNum;
    }

    public void setEndNum(int endNum) {
        this.endNum = endNum;
    }

}

• 实现方法

private final static Integer PAGE_SIZE = 1000;
Paging paging = new Paging();
paging.setEndNum(PAGE_SIZE);
int page = -1;
for (;;) {
    page++;
    paging.setStartNum(page * PAGE_SIZE);
    // 查询方法 (需要根据实际情况修改)
    List<T> entityList = servicice.listT(paging);
    if ( entityList  == null || entityList .isEmpty();) {
        break;
    }
   // 处理查询出来的数据
   ..........
   // 为了防止避免当前页已经查询完了，无需再多查询一次
   if (entityList .size() < PAGE_SIZE ) {
       break;
   }
}

// xml 的写法: LIMIT ${startNum},${endNum}

2. 分页查询并做数据更新处理, 影响分页条件

• 写一个Paging实体类

public class Paging implements Serializable {

    private static final long serialVersionUID = 1L;

    private int id;
    private int startNum;
    private int endNum;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getStartNum() {
        return startNum;
    }

    public void setStartNum(int startNum) {
        this.startNum = startNum;
    }

    public int getEndNum() {
        return endNum;
    }

    public void setEndNum(int endNum) {
        this.endNum = endNum;
    }

}

• 实现方法

private final static Integer PAGE_SIZE = 1000;
Paging paging = new Paging();
paging.setStartNum(0);
paging.setEndNum(PAGE_SIZE);
for (;;) {
    // 查询方法 (需要根据实际情况修改)
    List<T> entityList = servicice.listT(paging);
    if ( entityList  == null || entityList .isEmpty();) {
        break;
    }
   // 处理查询出来的数据
   ..........
   // 为了防止避免当前页已经查询完了，无需再多查询一次
   if (entityList .size() < PAGE_SIZE ) {
       break;
   }
   // 把entityList的最后一个实体类的id设置进去
   paging.setId(entityList.get(entityList .size() -1).getId());
}

// xml 的写法: WHERE id > #{id,jdbcType=INTEGER} ORDER BY id ASC  LIMIT ${startNum},${endNum}