分页查询
分页查询类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条记录