搭建项目分页
1.jsp页面
第${pager.currentPage}页
每页${pager.pageSize} 条记录
共${pager.totalPages}页
共${pager.totalRows} 条
2.拼接sql语句
SELECT * FROM USERS LEFT JOIN DEPT ON USERDEPT=DEPTNUM
SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM student) A WHERE ROWNUM <=15)WHERE RN >=11 ORDER BY ENTERDATE
分页sql:SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM USERS LEFT JOIN DEPT ON USERDEPT=DEPTNUM) A WHERE ROWNUM <=15)WHERE RN >=11 ORDER BY ENTERDATE
3.分页工具类导入到common包:Pager.java
在servlet调用:第一个条件:总条数 第二个条件:当前页数
String page = request.getParameter("page");
//分页调用开始--------------------------------
Pager pager = new Pager();
int totalRows = userService.getCountRows();
pager.setTotalRows(totalRows);
if(null != page){
pager.setCurrentPage(Integer.parseInt(page));
}else{
pager.setCurrentPage(1);
}
request.setAttribute("pager", pager);//给页面分页赋值
4.把pager工具类传递到dao,拼接执行动态分页sql语句
开始条数:pager.getStartRow()
结束条:pager.getCurrentPage()*pager.getPageSize()
String sql="SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM USERS LEFT JOIN DEPT ON USERDEPT=DEPTNUM) A WHERE ROWNUM <="+pager.getCurrentPage() *pager.getPageSize()+")WHERE RN >="+pager.getStartRow();//分页sql 1-3条
分页根本条件:
private int totalRows; //总行数
private int pageSize = 5; //每页显示的行数
private int currentPage=1; //当前页号
private int totalPages; //总页数
private int startRow; //当前页在数据库中的起始行
private int endRow; //数据库执行sql的结束行
totalPages算法:
if(totalRows<=pageSize){
totalPages=1;
}else{
totalPages=(totalRows+pageSize-1)/pageSize;
}
startRow; 算法:
startRow=(currentPage-1) * pageSize+1;
endRow; endRow=currentPage* pageSize;