利用数据库SQL Server2005如何实现分页? 我利用SQL Server2005数据库,要想现实JSP分页显示效果? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 http://icansoft.blog.51cto.com/268543/54310 <!-- 分页显示 --> <%final int PAGE_SIZE = 20;//每页显示的帖子数int pageNo = 1;//页号String strPageNo = request.getParameter("pageNo");if(strPageNo != null && !strPageNo.trim().equals("")) { try { pageNo = Integer.parseInt(strPageNo); } catch (NumberFormatException e) { pageNo = 1; } }if(pageNo <= 0) pageNo = 1;int totalPages = 0;//总的页数List<Article> articles = new ArrayList<Article>();Connection conn = DB.getConn();Statement stmtCount = DB.createStmt(conn);ResultSet rsCount = DB.executeQuery(stmtCount, "select count(*) from article where pid = 0");rsCount.next();int totalRecords = rsCount.getInt(1);totalPages = (totalRecords + PAGE_SIZE - 1)/PAGE_SIZE;if(pageNo > totalPages) pageNo = totalPages;Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);int startPos = (pageNo-1) * PAGE_SIZE; //String sql = "select * from article where pid = 0 order by pdate desc limit " + startPos + "," + PAGE_SIZE ;//int ss=PAGE_SIZE+startPos-1;//String sql ="SELECT * FROM ( SELECT TOP "+PAGE_SIZE+" * FROM (SELECT TOP "+ss+" * FROM article order by pdate desc) t1) t2 Order by pdate asc";//System.out.println(sql);String sql="select * from article where pid=0 order by pdate desc";ResultSet rs = DB.executeQuery(stmt, sql); if(totalPages>0){ //将记录指针定位到待显示页的第一条记录上 rs.absolute(startPos + 1); int i = 0; while(i<PAGE_SIZE && !rs.isAfterLast()){ Article a = new Article(); a.initFromRs(rs); articles.add(a); rs.next(); i++; } } DB.close(rsCount);DB.close(stmtCount);DB.close(rs);DB.close(stmt);DB.close(conn);%> SQL standard 2003 新带来的 SQL 窗口函数(Window function) ROW_NUMBER() OVER () , 数据库 SQL server 2005/DB2/Oracle 都支持,Oracle 与标准略为有点差别。 Mysql/PostgreSQL 则不支持 ROW_NUMBER()。可以看我的博客文章:原创 VelocityWeb 1.3 发布,增加 SQL 分页支持!!支持多种数据库!http://blog.csdn.net/jacklondon/archive/2008/08/23/2816824.aspx ssh 如何做到防止用户 重复登录? 紧急求SCJP 2009年最新题库 struts2 与 xml 的问题 急~~~~~~ 小弟初学EJB,请教下关于WebLogic的问题。。 请各位大虾帮忙给看看该如何来做??? 用过Tapestry的进来看一下,帮顶就有分 在struts中,如何验证用户输入的年龄不是整数? 在linux底下,tomcat的数据库连接池到底该怎么配? EJB入门级问题,求解。--早答早结单。 大家开发j2ee应用(企业信息系统),一般采用那个framework jaee struts2返回jQuery ajax请求异常信息
<!-- 分页显示 -->
<%
final int PAGE_SIZE = 20;//每页显示的帖子数
int pageNo = 1;//页号
String strPageNo = request.getParameter("pageNo");
if(strPageNo != null && !strPageNo.trim().equals("")) {
try {
pageNo = Integer.parseInt(strPageNo);
} catch (NumberFormatException e) {
pageNo = 1;
}
}
if(pageNo <= 0) pageNo = 1;
int totalPages = 0;//总的页数
List<Article> articles = new ArrayList<Article>();
Connection conn = DB.getConn();
Statement stmtCount = DB.createStmt(conn);
ResultSet rsCount = DB.executeQuery(stmtCount, "select count(*) from article where pid = 0");
rsCount.next();
int totalRecords = rsCount.getInt(1);
totalPages = (totalRecords + PAGE_SIZE - 1)/PAGE_SIZE;
if(pageNo > totalPages) pageNo = totalPages;
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
int startPos = (pageNo-1) * PAGE_SIZE;
//String sql = "select * from article where pid = 0 order by pdate desc limit " + startPos + "," + PAGE_SIZE ;
//int ss=PAGE_SIZE+startPos-1;
//String sql ="SELECT * FROM ( SELECT TOP "+PAGE_SIZE+" * FROM (SELECT TOP "+ss+" * FROM article order by pdate desc) t1) t2 Order by pdate asc";
//System.out.println(sql);
String sql="select * from article where pid=0 order by pdate desc";
ResultSet rs = DB.executeQuery(stmt, sql);
if(totalPages>0){
//将记录指针定位到待显示页的第一条记录上
rs.absolute(startPos + 1);
int i = 0;
while(i<PAGE_SIZE && !rs.isAfterLast()){
Article a = new Article();
a.initFromRs(rs);
articles.add(a);
rs.next();
i++;
}
}
DB.close(rsCount);
DB.close(stmtCount);
DB.close(rs);
DB.close(stmt);
DB.close(conn);
%>
可以看我的博客文章:
原创 VelocityWeb 1.3 发布,增加 SQL 分页支持!!支持多种数据库!
http://blog.csdn.net/jacklondon/archive/2008/08/23/2816824.aspx