9实现分页查询(上)
1解决问题
2计算显示数据的总数量
newsdao添加方法
//获取新闻总数量
public int getTotalCount();
newsdaoimpl实现
/**
* 获取新闻总数量
* */
public int getTotalCount() {
int totalCount=0;
String sql="select count(*) from news_detail";
Object[] params={};
ResultSet rs=this.executeSQL(sql, params);
try {
while(rs.next()){
totalCount=rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeResource();
}
return totalCount;
}
3计算显示的页数
提取公共类到util下page类中
package com.pb.news.util;
public class Page {
//总页数
private int totalPageCount=1;
//页面大小,即每页显示记录数
private int pageSize=0;
//记录总数
private int recordCount=0;
public int getTotalPageCount() {
return totalPageCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize>0)
this.pageSize = pageSize;
}
public int getRecordCount() {
return recordCount;
}
public void setRecordCount(int recordCount) {
if(recordCount>0){
this.recordCount = recordCount;
}
}
//设置总页数
private void setTotalPageCountByRs(){
if(this.recordCount%this.pageSize==0)
this.totalPageCount=this.recordCount/this.pageSize;
else if(this.recordCount%this.pageSize>0)
this.totalPageCount=this.recordCount/this.pageSize+1;
else
this.totalPageCount=0;
}
}
4编写分页查询sql语句(等差数列)
SELECT * FROM news_detail;
--每页显示2条新闻信息,查看第1页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
--页码:currPageNo 1 每页显示的记录数:pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=1 AND a.rn<=2
--每页显示2条新闻信息,查看第2页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
--页码:currPageNo 2 每页显示的记录数:pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=3 AND a.rn<=4
--每页显示2条新闻信息,查看第3页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
--页码:currPageNo 3 每页显示的记录数:pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=5 AND a.rn<=6
--起始记录数和结束记录数有什么特点?和页码与每页显示的记录有什么关系?
--页码ÿ