简单分页查询

分页查询

分页查询类PageInfo.java

public class PageInfo<T> {

    private List<T> list;
    private Integer totalRows;//总行数
    private Integer pageRows = 10;//每一页的记录数
    private Integer pageNums = 1;//当前页码
    private Integer totalPage;//总页数

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

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

    public Integer getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(Integer totalRows) {
        this.totalRows = totalRows;
        this.setTotalPage(totalRows%pageRows==0?(totalRows/pageRows):(totalRows/pageRows + 1));
    }

    public Integer getPageRows() {
        return pageRows;
    }

    public void setPageRows(Integer pageRows) {
        this.pageRows = pageRows;
    }

    public Integer getPageNums() {
        return pageNums;
    }

    public void setPageNums(Integer pageNums) {
        this.pageNums = pageNums;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

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

    @Override
    public String toString() {
        return "PageInfo{" +
                "list=" + list +
                ", totalRows=" + totalRows +
                ", pageRows=" + pageRows +
                ", pageNums=" + pageNums +
                ", totalPage=" + totalPage +
                '}';
    }
}

Dao层查询所有方法

@Override
public PageInfo<Book> queryAllBooks(int pageNum, int pageRows) {
    PageInfo<Book> pageInfo = new PageInfo<Book>();
    pageInfo.setPageNums(pageNum);
    pageInfo.setPageRows(pageRows);

    List<Book> links = new ArrayList<Book>();
    //声明连接对象，语句命令对象，结果集对象
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    //定义sql语句
    String sql = "SELECT book.id,book.`name`,book.author,book.publish,book.pages,book.price,book.bookcaseid,book.abled,bookcase.`name` as bookCaseName\n" +
            "FROM book LEFT JOIN bookcase ON book.bookcaseid = bookcase.id LIMIT ?,?";
    try {
        //实例化上面的对象
        conn = DBUtils.getConnection();
        pstmt = conn.prepareStatement(sql);
        //给占位符赋值
        pstmt.setInt(1,(pageNum-1)*pageRows);
        pstmt.setInt(2,pageRows);
        //执行sql脚本，返回结果集
        rs = pstmt.executeQuery();
        //对结果集进行处理
        int i = 0;
        while (rs.next()){
            links.add(new Book(rs.getInt("id"),
                rs.getString("name"),
                rs.getString("author"),
                rs.getString("publish"),
                rs.getInt("pages"),
                rs.getFloat("price"),
                rs.getInt("bookcaseid"),
                rs.getInt("abled")));
            Bookcase bookcase = new Bookcase(rs.getInt("bookcaseid"),rs.getString("bookCaseName"));
            links.get(i++).setBookcase(bookcase);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBUtils.closeAll(conn,pstmt,rs);
    }
    System.out.println("links =" + links);
    pageInfo.setList(links);
    pageInfo.setTotalRows(getCount());
    return pageInfo;

sql语句解析

SELECT * from user order by id desc LIMIT ?,?;

limit后的两个参数，第一个参数是起始下标（从0开始），第二个参数是查询的记录条数

SELECT * from user order by id desc LIMIT 100,10;

该语句查询的是第101-110条的数据，实际上是查询了前110条数据，取后面10条，耗时是查询110条数据的时间，如果面临的数据量很大时，需要考虑效率的问题。所以我们可以考虑使用子查询来提高查询效率。

SELECT * FROM book WHERE id >= (SELECT id FROM book ORDER BY id LIMIT 10000,1) LIMIT 100;

取10000条记录后1条,然后取ID值作起始标识定位下100条记录