推荐你最好在数据库中写过程来分页。那样效率高,移植性好!多写写sql语句对你的好处你会慢慢体会到的。 给你点代码: 1. DECLARE @pagenum AS INT, @pagesize AS INT 2. SET @pagenum = 2 3. SET @pagesize = 3 4. SELECT * 5. FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, 6. speaker, track, score 7. FROM SpeakerStats) AS D 8. WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 1. SELECT TOP (10) * FROM hy_hotel_info 2. WHERE hi_id >(SELECT MAX (hi_id) 3. from (select top (2*10) hi_id 4. from hy_hotel_info order by hi_id) as talTMP ) order by hi_id 5. 6. 以上结果是 查询每页十个 查询的是第三页 7. 8. 9. SELECT TOP (10) --查询每页显示的数量 10. * FROM hy_hotel_info 11. WHERE hi_id >(SELECT MAX (hi_id) 12. from (select top (2*10) --查询的页数*每页显示的数量 13. hi_id 14. from hy_hotel_info order by hi_id) as talTMP ) order by hi_id 15. 16. 17. 18. SELECT TOP (10) HY_Counter.cc_id, * FROM hy_hotel_info 19. inner join HY_Counter on CC_ID = HI_CounterId 20. WHERE hi_id >(SELECT MAX (hi_id) 21. from (select top 20 hi_id 22. from hy_hotel_info order by hi_id) as talTMP ) 23. order by HY_Counter.cc_id asc, hi_id desc
package com.vedeo.servlet;public class Page { /**
* 当前页号, 采用自然数计数 1,2,3,...
*/
private int num;
/**
* 页面大小
*/
private int size;
/**
* 数据总数
*/
private int rowCount;
/**
* 页面总数
*/
private int count;
/**
* 当前页面开始行, 第一页是0行
*/
private int startRow;
/**
* 第一页 页号
*/
private int first = 1;
/**
* 最后页 页号
*/
private int last;
/**
* 下一页 页号
*/
private int next;
/**
* 前页 页号
*/
private int prev;
/**
* 页号式导航, 起始页号
*/
private int start;
/**
* 页号式导航, 结束页号
*/
private int end;
/**
* 页号式导航, 显示页号数量
*/
private int numCount = 10; public Page(int size, int num, int rowCount) {
this.num = num;
this.size=size;
this.rowCount = rowCount;
this.count = (int) Math.ceil((double)rowCount/size);
this.num = Math.min(this.num, count);
this.num = Math.max(1, this.num);
this.startRow = (this.num-1) * size ;
this.last = this.count;
this.next = Math.min( this.count, this.num+1);
this.prev = Math.max(1 , this.num-1);
//计算page 控制
start = Math.max(this.num-numCount/2, first);
end = Math.min(start+numCount, last);
if(end-start < numCount){
start = Math.max(end-numCount, 1);
}
} public int getCount() {
return count;
} public void setCount(int count) {
this.count = count;
} public int getFirst() {
return first;
} public void setFirst(int first) {
this.first = first;
} public int getLast() {
return last;
} public void setLast(int last) {
this.last = last;
} public int getNext() {
return next;
} public void setNext(int next) {
this.next = next;
} public int getNum() {
return num;
} public void setNum(int num) {
this.num = num;
} public int getPrev() {
return prev;
} public void setPrev(int prev) {
this.prev = prev;
} public int getRowCount() {
return rowCount;
} public void setRowCount(int rowCount) {
this.rowCount = rowCount;
} public int getSize() {
return size;
} public void setSize(int size) {
this.size = size;
} public int getStartRow() {
return startRow;
} public void setStartRow(int startRow) {
this.startRow = startRow;
} public int getEnd() {
return end;
} public void setEnd(int end) {
this.end = end;
} public int getNumCount() {
return numCount;
} public int getStart() {
return start;
} public void setStart(int start) {
this.start = start;
}
}
这个在网一搜一大堆,给你个分页的类。package sqlbean;import java.sql.ResultSet;
import java.sql.SQLException;import datebase.JDBC;public class SpecialtySetupBeanPage {
int pagenow;
int pagecount;
int pagesize;
int rowcount; public SpecialtySetupBeanPage() {
pagenow = 1;
pagecount = 0;
pagesize = 20;
rowcount = 0;
} public int fpage(String spagenow) {
SpecialtySetupBeanPage pb = new SpecialtySetupBeanPage();
int pagecount = pb.countpage();
if (spagenow != null) {
pagenow = Integer.parseInt(spagenow);
if (pagenow > pagecount)
pagenow = pagecount;
} else {
pagenow = 1;
}
return pagenow;
} public int countpage() {
JDBC db = new JDBC();
String sql = "select count(*) from table1";
ResultSet rs = db.query(sql);
try {
while (rs.next())
rowcount = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
if (rowcount % pagesize == 0)
pagecount = rowcount / pagesize;
else
pagecount = rowcount / pagesize + 1;
return pagecount;
} public int rowpage() throws SQLException {
JDBC db = new JDBC();
String sql = "select count(*) from table1";
for (ResultSet rs = db.query(sql); rs.next();)
rowcount = rs.getInt(1); return rowcount;
}}
* 分页查询
* @param sql 查询语句
* @param page 当前页
* @param pageSize 每页多少记录
* @return map对象, 包含LIST结果,分页前总记录数。
* */
public Map queryPaging(String sql, int page, int pageSize) {
int resultSize = this.queryForList(sql).size();
int _page = page-1;
log.info("queryPaging:" + sql + " limit "+_page*pageSize + ", " + pageSize);
List list = this.queryForList(sql + " limit "+_page*pageSize + ", " + pageSize);
Map map = new HashMap();
map.put("list", list);
map.put("size", resultSize);
return map;
}
页面JS控件控制当前页, 和每页大小
/**
* 简单的数据库分页查询,对mmsql数据库来说,还只能对一张表进行分页查询,
* @param totalsql
* @param sql
* @param pagesize
* @return
* @throws Exception
*/
public List simplePageSelect(String totalsql,String sql,int pagesize) throws Exception{
if(super.dbtype.toLowerCase().equals("mssql")){
return this.findList4PageMssql(totalsql, sql, pagesize);
}else{ //mysql database
return this.findList4PageMysql(totalsql, sql, pagesize);
}
} /**
* mysql 分页查询
* @param totalsql
* @param sql
* @param pagesize
* @return
* @throws Exception
*/
private List findList4PageMysql(String totalsql,String sql,int pagesize) throws Exception {
HttpServletRequest request=ServletActionContext.getRequest();
int total=this.count(totalsql);
final Page page=Page.getPage(request, total, pagesize);
List list = null;
sql += " limit "+page.getStartLocation()+","+page.getPerPageSize();
list= this.find(sql);
request.setAttribute("page", page);
return list;
}
/**
* mssql分页查询
* @param totalsql
* @param sql
* @param pagesize
* @throws Exception
*
*/
private List findList4PageMssql(String totalsql,String sql,int pagesize) throws Exception {
HttpServletRequest request=ServletActionContext.getRequest();
int total=this.count(totalsql)
final Page page=Page.getPage(request, total, pagesize);
List list = null;
list= this.find(sql);
request.setAttribute("page", page);
int end=(page.getStartLocation()+page.getPerPageSize())>list.size()?list.size():(page.getStartLocation()+page.getPerPageSize());
return list.subList(page.getStartLocation(), end);
}public class Page {
private int perPageSize;
private int totalPageNum;
private int currentPage;
private int startLocation;
private int totalNum;
private Page(){
}
public int getPerPageSize() {
return perPageSize;
} public void setPerPageSize(int perPageSize) {
perPageSize = perPageSize;
}
public int getTotalPageNum() {
return totalPageNum;
} public void setTotalPageNum(int totalPageNum) {
this.totalPageNum = totalPageNum;
} public int getCurrentPage() {
return currentPage;
} public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getStartLocation() {
return startLocation;
} public void setStartLocation(int startLocation) {
this.startLocation = startLocation;
} public int getTotalNum() {
return totalNum;
} public void setTotalNum(int totalNum) {
this.totalNum = totalNum;
} public Page(int _totalNum,int _perPageSize) {
totalNum = _totalNum;
perPageSize = _perPageSize;
totalPageNum = totalNum / perPageSize;
int mod = totalNum % perPageSize;
if (mod > 0)
totalPageNum++;
currentPage = 1;
startLocation = 0;
}
public static Page getPage(HttpServletRequest request, int _totalNum ,int _PerPageSize){
Page page = new Page(_totalNum,_PerPageSize);
String currentPage = request.getParameter("currentPage");
String pagerMethod = request.getParameter("pageMethod");
if (currentPage != null && pagerMethod != null){
page.refresh(Integer.parseInt(currentPage));
if (pagerMethod.equals("first")) {
page.first();
} else if (pagerMethod.equals("previous")){
page.previous();
} else if (pagerMethod.equals("next")) {
page.next();
} else if (pagerMethod.equals("last")) {
page.last();
} else{
page.some();
}
}
return page;
}
public void first() {
currentPage = 1;
startLocation = 0;
} public void previous() {
if (currentPage == 1) {
return;
}
currentPage--;
startLocation = (currentPage - 1) * perPageSize;
}
public void next() {
if (currentPage < totalPageNum) {
currentPage++;
}
startLocation = (currentPage - 1) * perPageSize;
}
public void last() {
currentPage = totalPageNum;
startLocation = (currentPage - 1) * perPageSize;
}
public void some() {
currentPage=currentPage > totalPageNum?totalPageNum:currentPage;
startLocation = (currentPage - 1) * perPageSize;
} public void refresh(int _currentPage) {
currentPage = _currentPage;
if (currentPage > totalPageNum){
last();
}
}
}
然后你想用什么数据库就在配置文件里面配置就可以了。
页面就用类似<a href="/xxx.do?currentPage=${page.currentPage}&pageMethod=first" >首页
这种方式访问.
</a>
给你点代码:
1. DECLARE @pagenum AS INT, @pagesize AS INT
2. SET @pagenum = 2
3. SET @pagesize = 3
4. SELECT *
5. FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
6. speaker, track, score
7. FROM SpeakerStats) AS D
8. WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 1. SELECT TOP (10) * FROM hy_hotel_info
2. WHERE hi_id >(SELECT MAX (hi_id)
3. from (select top (2*10) hi_id
4. from hy_hotel_info order by hi_id) as talTMP ) order by hi_id
5.
6. 以上结果是 查询每页十个 查询的是第三页
7.
8.
9. SELECT TOP (10) --查询每页显示的数量
10. * FROM hy_hotel_info
11. WHERE hi_id >(SELECT MAX (hi_id)
12. from (select top (2*10) --查询的页数*每页显示的数量
13. hi_id
14. from hy_hotel_info order by hi_id) as talTMP ) order by hi_id
15.
16.
17.
18. SELECT TOP (10) HY_Counter.cc_id, * FROM hy_hotel_info
19. inner join HY_Counter on CC_ID = HI_CounterId
20. WHERE hi_id >(SELECT MAX (hi_id)
21. from (select top 20 hi_id
22. from hy_hotel_info order by hi_id) as talTMP )
23. order by HY_Counter.cc_id asc, hi_id desc