一、选择分页算法
下面是颠倒TOP分页算法
pagesize： 每页显示记录数
cureentpage：当前页数
select * from ( select TOP pagesize * FROM ( SELECT TOP pagesize*cureentpage * from news ORDER BY addtime DESC ) as a ORDER BY addtime ASC ) as b ORDER BY addtime DESC
这样就可以把每页的数据返回，加上索引可以提高查询效率
select count(*) from news
返回总记录数
二、程序设计
Page.java
- package com.test.util
- public class Page(){
- private int pagesize;//一页的记录数
- private int currentpage;//当前页
- private int rowcount;//总记录数
- private int pagecount;//计算的总页数
- //变量初始化
- public Page(int currentpage, int rowcount){
- pagesize = 10;
- pagecount = rowcount/pagesize + 1;//计算总页数
- this.currentpage = currentpage;
- this.rowcount = rowcount;
- }
- //返回总记录
- public int getPageCount(){
- return pagecount;
- }
- //返回每页的记录数
- public int getPageSize(){
- return pagesize;
- }
- //返回判断状态，如果第一页，返回0，最后一页返回1，如果平常页返回2
- public int checkCurrent(){
- if(current == 1){
- return 0;
- }
- if(current == pagecount){
- return 1;
- }
- else{
- return 2;
- }
- }
- }
action中的调用
int currentpage = Integer.parseInt(request.getParameter("page"));
String sql = "select count(*) from news";
ResultSet rs = stms.executeQuery(sql);
rs.next();
int rowcount = rs.getInt(1);
rs.close();
Page p = new Page(currentpage,rowcount);
int pagecount = p.getPageCount();
int flag = p.checkCurrent();
int pagesize = p.getPageSize();
ResultSet rs1 = null;
if(flag == 0){
sql = "select top "+pagesize +" * from news order by addtime DESC";
rs1 = stms.executeQuery(sql);
....................
}
if(flag == 1){
int pagelast = rowcount - ((pagecount - 1) * pagesize);
sql = "select * from (select top "+pagelast +" * from news order by addtime ASC) as a order by addtime DESC";
rs1 = stms.executeQuery(sql);
....................
}
else{
int pagelast = rowcount - ((pagecount - 1) * pagesize);
sql = "select * from ( select TOP pagesize * FROM ( SELECT TOP pagesize*cureentpage * from news ORDER BY addtime DESC ) as a ORDER BY addtime ASC ) as b ORDER BY addtime DESC";
rs1 = stms.executeQuery(sql);
....................
}
.....................
以上是我对使用颠倒TOP法实现的分页功能，十分简单
在效率上，支持200万条数据应该没有问题
PS:如果把SQL查询运用到存储过程中,会不会更有效率呢?