在web应用中,对页面数据进行分页显示是常常用到的。各种数据其对于分页的思想是差不多的。实现方法有两种,一种是从数据库查询出所有数据,在查询结果中进行分。另一种,在数据库中查询指定的记录。现在oracle数据库为例,用第二种方法现实一个分页的例子。
核心sql 有两语句 如下
1、查询出数据中的记录的总数
SELECT COUNT(rownum) FROM 表名;
通过数据库记录总数来计算,分页页面的总页数。
2、查询指定的记录,
SELECT 要反回的字段 FROM
(
SELECT rownum rn,其它字段 FROM 表名 WHERE ROWNUM <= 30
) 表别名
WHERE 表别名.RN >= 15;
从中可以看出每次查询,只要改变30,15这个两数,就可以得到我们想要的结果,就此在dao层写一个方法套上这句sql行了(接受两个参数是必然的)。
分页类 用来存储每次要变的页码
/**sql 分页*/
package util;
public class SqlPage {
private int cruuPage; //当前页(默认第一页)
private int onePageNum; //每页显示记录数
private int dbStarCount; //每页开始记录
private int dbEndCount; //每页结束记录
private int dbTotalCount; //数据库记录总数
private int totalPage; //分页总页数;
private String urlString; //分页导航显示
//public SqlPage() {} //无参构造
//有参构造
public SqlPage(int onePageNum,int dbTotalConut,String urlString) {
super();
this.cruuPage = 1;
this.onePageNum = onePageNum;
this.dbStarCount = 0;
this.dbEndCount = this.onePageNum;
this.dbTotalCount = dbTotalConut;
this.totalPage = ((this.dbTotalCount%this.onePageNum == 0)
? (this.dbTotalCount/this.onePageNum)
: (this.dbTotalCount/this.onePageNum+1)) ;
this.urlString = urlString;
}
public int getCruuPage() {
return cruuPage;
}
public void setCruuPage(int cruuPage) {
this.cruuPage = cruuPage;
}
public int getOnePageNum() {
return onePageNum;
}
public void setOnePageNum(int onePageNum) {
this.onePageNum = onePageNum;
}
public int getDbStarCount() {
return dbStarCount;
}
public void setDbStarCount(int dbStarCount) {
this.dbStarCount = dbStarCount;
}
public int getDbEndCount() {
return dbEndCount;
}
public void setDbEndCount(int dbEndCount) {
this.dbEndCount = dbEndCount;
}
public int getDbTotalCount() {
return dbTotalCount;
}
public void setDbTotalCount(int dbTotalCount) {
this.dbTotalCount = dbTotalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public String getUrlString() {
return urlString;
}
public void setUrlString(String urlString) {
this.urlString = urlString;
}
//根据页面请示,更换页码 设置开始,结束查询 记录数
public void setPageNum(String pf){
if(pf.equals("nextPage") && (this.getCruuPage() != this.getTotalPage())){ //下一页
this.setCruuPage(this.getCruuPage()+1);
this.setDbStarCount(this.getDbStarCount()+this.getOnePageNum());
this.setDbEndCount(this.getDbEndCount()+this.getOnePageNum());
}
if(pf.equals("upPage") && (this.cruuPage != 1)){ //上一页
this.cruuPage--;
this.dbStarCount -= this.onePageNum;
this.dbEndCount -= this.onePageNum;
}
if(pf.equals("endPage")){ //最后一页
this.cruuPage = this.totalPage;
this.dbStarCount = this.onePageNum*this.totalPage-this.onePageNum;
this.dbEndCount = this.dbTotalCount;
}
}
//页面显示分页导航字符串
public String dispPageNav(){
if(this.totalPage > 1){
return "<a href="+this.getUrlString()+">首页</a> " +
"<a href="+this.getUrlString()+"?page=nextPage>下一页</a> " +
"<a href="+this.getUrlString()+"?page=upPage>上一页</a>" +
" <a href="+this.getUrlString()+"?page=endPage>尾页</a>" +
" 当前第 "+this.getCruuPage()+" 页 共 "+this.getTotalPage()+" 页";
}else{
return "<font color=#696969>首页</font> " +
"<font color=#696969>下一页</font> " +
"<font color=#696969>上一页</font> " +
"<font color=#696969>尾页</font>" +
"<font color=#696969> 当前第 "+this.cruuPage+" 页 共 "+this.totalPage+" 页</font>";
}
}
}
以下是我在实际项目中的应用
String pageInfo = request.getParameter("page");
SqlPage sp = null;
Connection conn = null;
NoteSellCommDao nscdao = null;
List<NoteSellComm> list = null;
int dbTotalCount;
try{
conn = GetConn.getConnection();
nscdao = new NoteSellCommDao();
if(pageInfo != null ){
sp = (SqlPage)request.getSession(true).getAttribute("sppp");
sp.setPageNum(pageInfo);//更换页
}else{
dbTotalCount = nscdao.getAllNum(); //查询出数据库记录总条数
sp = new SqlPage(20,dbTotalCount,"selectsellcomm.jsp"); //初始化分页类(每页记录显示条数,数据库总记录,本页网页文件名)
}
list = nscdao.getAllPageStor(sp.getDbStarCount(),sp.getDbEndCount(),cmd);
request.setAttribute("nsc",list);
request.setAttribute("spnav",sp.dispPageNav()); //获取页面分页导航显示
request.getSession(true).setAttribute("sppp",sp);
}catch(Exception e){
e.printStackTrace();
}finally{
GetConn.release(null,null,conn);
}