分页SQL: a.使用mysql控制: select * from user order by Host limit m, n 结果返回的是第m+1行到第n行的数据集。 比如 select * from user order by Host limit 1, 5 返回的是第2行到第5行的数据集b.使用sqlserver SELECT * FROM (SELECT TOP m * FROM (SELECT TOP n * FROM Customers) A ORDER BY CustomerID DESC) B ORDER BY CustomerID 获得的结果集数据为第n-m+1行到第n行。 对整个过程的解释: 首先按照升序得到前n行的结果集A,然后按照降序从A中得到后m行的结果集B,最后按照升序对B进行重新排序,返回结果集。 其中CustomerID为主键比如: SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP 10 * FROM Customers) A ORDER BY CustomerID DESC) B ORDER BY CustomerID 的意思就是返回包含第6行到第10行的数据结果集。c.使用Oracle: select * from (select rownum r ,* from test) tt where tt.r > 50 and tt.r <= 100;
a.使用mysql控制:
select * from user
order by Host
limit m, n
结果返回的是第m+1行到第n行的数据集。
比如
select * from user
order by Host
limit 1, 5
返回的是第2行到第5行的数据集b.使用sqlserver
SELECT *
FROM (SELECT TOP m *
FROM (SELECT TOP n *
FROM Customers) A
ORDER BY CustomerID DESC) B
ORDER BY CustomerID
获得的结果集数据为第n-m+1行到第n行。
对整个过程的解释:
首先按照升序得到前n行的结果集A,然后按照降序从A中得到后m行的结果集B,最后按照升序对B进行重新排序,返回结果集。
其中CustomerID为主键比如:
SELECT *
FROM (SELECT TOP 5 *
FROM (SELECT TOP 10 *
FROM Customers) A
ORDER BY CustomerID DESC) B
ORDER BY CustomerID
的意思就是返回包含第6行到第10行的数据结果集。c.使用Oracle:
select * from (select rownum r ,* from test) tt
where tt.r > 50 and tt.r <= 100;
还在为为这个头疼呢,请各位哥哥姐姐帮帮我吧!!!
struts的标签功能有限
*
*/
package table;import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Vector;
/**
*
* <p>Title:分页的接口 </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 北京东方国信电子有限公司</p>
* @author 卢兆满 2006-11-1
* @version 1.0
*/
public interface Page { /**
* getCurPage:
*
* @return int 返回当前的页数
*/
public int getCurPage(); /**
* getPageSize:返回分页大小
*
* @return int
*/
public int getPageSize(); /**
* getRowsCount:返回总记录行数
*
* @return int
*/
public int getTotalRows(); /**
* getPageCount:返回总页数
*
* @return int
*/
public int getTotalPage(); /**
* 第一页
* @return int
*/
public int first(); /**
* 最后一页
* @return int
*/
public int last(); /**
* 上一页
* @return int
*/
public int previous(); /**
* 下一页
* @return int
*/
public int next(); /**
* 第一页
* @return boolean
*/
public boolean isFirst(); /**
* 最后一页
* @return boolean
*/
public boolean isLast(); /**
* 获取当前页数据
* @return List
*/
public List getData();
/**
* @param curPage
* @return void
*/
public void setCurPage(int curPage);
/**
* @param data
* @return void
*/
public void setData(List data);
/**
* @param pageSize
* @return void
*/
public void setPageSize(int pageSize);}
package table;import java.util.*;import javax.servlet.http.HttpServletRequest;/**
* <p>Title: 小数据量分页 </p>
* <p>Description: 由于一次性把数据都读到内存,所以适合的数据量不能太大 </p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 北京东方国信电子有限公司</p>
* @author 卢兆满 2006-11-1
* @version 1.0
*/
public class SmallPage implements Page{
/**
* 分页数据
*/
List data = null; /**
* 当前页
*/
int curPage; /**
* 每页显示的记录数
*/
int pageSize = 10; /**
* 记录行数
*/
int totalRows; /**
* 页数
*/
int totalPage; /**
* @param data
*/
public SmallPage(List data) {
this.data = data;
this.curPage = 1;
this.totalRows = data.size();
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
} /**
* @param data
* @param curPage
*/
public SmallPage(List data, int curPage) {
this.data = data;
this.curPage = curPage;
this.totalRows = data.size();
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
} /**
* @param data
* @param curPage
* @param pageSize
*/
public SmallPage(List data, int curPage, int pageSize) {
this.data = data;
this.curPage = curPage;
this.pageSize = pageSize;
this.totalRows = data.size();
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
}
/**
* @param request
* @param data
*/
public SmallPage(HttpServletRequest request,List data){
if(data !=null){
this.data = data;
this.curPage = request.getParameter("page")==null? 1: new Integer(request.getParameter("page")).intValue();
this.totalRows = data.size();
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
}
}
/**
* @param request
* @param data
* @param pageSize
*/
public SmallPage(HttpServletRequest request,List data ,int pageSize){
if(data !=null){
this.data = data;
this.curPage = request.getParameter("page")==null? 1: new Integer(request.getParameter("page")).intValue();
this.pageSize = pageSize;
this.totalRows = data.size();
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
}
} /**
* getCurPage:
*
* @return int 返回当前的页数
*/
public int getCurPage() {
return curPage;
} /**
* getPageSize:返回分页大小
*
* @return int
*/ public int getPageSize() {
return pageSize;
} /**
* gettotalRows:返回总记录行数
*
* @return int
*/ public int getTotalRows() {
return totalRows;
} /**
* gettotalPage:返回总页数
*
* @return int
*/ public int getTotalPage() {
return totalPage;
} /**
* 第一页
* @return int
*/ public int first() {
return 1;
} /**
* 最后一页
* @return int
*/ public int last() {
return totalPage;
} /**
* 上一页
* @return int
*/ public int previous() {
return (curPage - 1 < 1) ? 1 : curPage - 1;
} /**
* 下一页
* @return int
*/ public int next() {
return (curPage + 1 > totalPage) ? totalPage : curPage + 1;
} /**
* 第一页
* @return boolean
*/ public boolean isFirst() {
return (curPage == 1) ? true : false;
} /**
* 最后一页
* @return boolean
*/
public boolean isLast() {
return (curPage == totalPage) ? true : false;
} /**
* 获取当前页数据
* @return List
*/
public List getData() {
ArrayList curData = null;
if (data != null) {
curData = new ArrayList();
int start = (curPage - 1) * pageSize;
int end = 0;
if (start + pageSize > totalRows)
end = totalRows;
else
end = start + pageSize;
for (int i = start; i < end; i++) {
//System.out.println(this.data.get(i)+"\tstart\\end:"+start+"\\"+end);
curData.add(this.data.get(i));
}
}
return curData;
} public void setCurPage(int curPage) {
this.curPage = curPage;
} public void setData(List data) {
this.data = data;
} /* (non-Javadoc)
* @see table.Page#setPageSize(int)
*/
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
this.totalPage = (int) Math.ceil((double) totalRows / pageSize);
}
}
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 北京东方国信电子有限公司</p>
* @author 卢兆满 2006-11-1
* @version 1.0
*/
public interface PageBar {
/**
* param @return
*/
public int getCurrentPage();
/**
* param @return
*/
public String getHTML(); /**
* param @return
*/
public int getTotalPages();
/**
* param @return
*/
public int getTotalRows();}
*
*/
package table;import javax.servlet.http.HttpServletRequest;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2006</p>
* <p>Company: 北京东方国信电子有限公司</p>
* @author 卢兆满 2006-11-1
* @version 1.0
*/
public class PageBarOne implements PageBar {
/** */
String url; /** */
int currentPage; /** */
int totalPages; /** */
int totalRows; /** */
private HttpServletRequest request; /**
* @param httpservletrequest
* @param pages
*/
public PageBarOne(HttpServletRequest httpservletrequest, Page pages){
request = httpservletrequest;
totalPages = pages.getTotalPage();
totalRows = pages.getTotalRows();
init();
} /* (non-Javadoc)
* @see table.PageBar#getCurrentPage()
*/
public int getCurrentPage() {
return currentPage;
} /* (non-Javadoc)
* @see table.PageBar#getHTML()
*/
public String getHTML() {
if (currentPage > totalPages)
currentPage = totalPages;
if (currentPage < 1)
currentPage = 1;
StringBuffer html = new StringBuffer("共");
html.append(totalRows);
html.append("项 第");
html.append(currentPage);
html.append("/");
html.append(totalPages);
html.append("页 "); if (currentPage == 1) {
html.append("<font color=#999999>首页 上页 </font>");
} else {
html.append("<a href=\"");
html.append(url);
html.append("page=1\">首页</a> <a href=\"");
html.append(url);
html.append("page=");
html.append(currentPage - 1);
html.append("\">上页</a> ");
} if (currentPage >= totalPages) {
html.append("<font color=#999999>下页 末页</font>");
} else {
html.append("<a href=\"");
html.append(url);
html.append("page=");
html.append(currentPage + 1);
html.append("\">下页</a> <a href=\"");
html.append(url);
html.append("page=");
html.append(totalPages);
html.append("\">末页</a>");
}
html.append(" <input name=\"pageNo\" type=\"text\" id=\"pageNo\" size=\"3\" onKeyPress=\"return handleEnterOnPageNo();\"> "
+ "<input name=\"goto\" type=\"button\" id=\"goto\" value=\"go\" onClick=\"forward();\">");
html.append("<script type = 'text/javaScript'>\r\n");
html.append("function forward()");
html.append("{");
html.append(" if(!(/^([-]){0,1}([0-9]){1,}$/.test(document.all.pageNo.value)))");
html.append("{");
html.append(" alert(\"请输入合法的页号!\");");
html.append(" document.all.pageNo.focus();");
html.append(" document.all.pageNo.select();");
html.append(" }");
html.append(" else");
html.append(" window.location.href=\"");
html.append(url);
html.append("page=\"+ document.all.pageNo.value;"); html.append("}");
html.append("function handleEnterOnPageNo()"); html.append("{");
html.append(" if(event.keyCode == 13)");
html.append(" {");
html.append(" forward();"); html.append(" return false;"); html.append(" }");
html.append(" return true;");
html.append("}");
html.append("</script>");
return html.toString();
} /* (non-Javadoc)
* @see table.PageBar#getTotalPages()
*/
public int getTotalPages() {
return totalPages;
} /* (non-Javadoc)
* @see table.PageBar#getTotalRows()
*/
public int getTotalRows() {
return totalRows;
} /**
* param @return
*/
public String getUrl() {
return url;
} /**
* param
*/
public void init() {
currentPage = request.getParameter("page")==null? 1: new Integer(request.getParameter("page")) .intValue();
String as[] = new String[] {"page", "totalrows"};
url = Common.getRequestURL(request, as);
//如果url 中没有? 就在末尾加上?
if(url.indexOf("?")==-1)
url+="?";
//如果url中的? 在末尾就不加& 不在末尾就加&
if(url.indexOf("?")==url.length()-1)
url = url + new StringBuffer("totalrows=").append(totalRows).append("&").toString();
else{
url = url + new StringBuffer("&totalrows=").append(totalRows).append("&").toString();
}
} /**
* param @param i
*/
public void setCurrentPage(int i) {
currentPage = i;
} /**
* param @param i
*/
public void setTotalPages(int i) {
totalPages = i;
} /**
* param @param i
*/
public void setTotalRows(int i) {
totalRows = i;
} /**
* param @param s
*/
public void setUrl(String s) {
url = s;
}
}package table;import java.io.IOException;
import java.io.Writer;
import javax.servlet.ServletRequest;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.TagSupport;public class PageBarTag extends TagSupport { /** */
private PageBar pageBar; /** */
private String pageBarName; /**
*
*/
public PageBarTag() {
pageBar = null;
} /* (non-Javadoc)
* @see javax.servlet.jsp.tagext.TagSupport#doEndTag()
*/
public int doEndTag() throws JspException {
String s = pageBar.getHTML();
try {
pageContext.getOut().write(s);
} catch (IOException e) {
throw new JspException("构造页面导航是IOerror:" + e.getMessage());
}
return 6;
} /* (non-Javadoc)
* @see javax.servlet.jsp.tagext.TagSupport#doStartTag()
*/
public int doStartTag() throws JspException {
if (pageBar != null) {
return super.EVAL_BODY_INCLUDE;
} else
throw new JspException("页面导航对象不能为空");
} /**
* param @return
*/
public String getPageBarName() {
return pageBarName;
} /**
* param @param pageBarName
*/
public void setPageBarName(String pageBarName) {
this.pageBarName = pageBarName;
this.pageBar = (PageBar) pageContext.getRequest().getAttribute(
this.pageBarName);
}}
test-table.tld<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE taglib PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.1//EN" "http://java.sun.com/j2ee/dtds/web-jsptaglibrary_1_1.dtd">
<taglib>
<tlibversion>1.0</tlibversion>
<jspversion>1.1</jspversion>
<shortname>test</shortname>
<uri>/WEB-INF/test-table</uri> <tag>
<name>pageBar</name>
<tagclass>table.PageBarTag</tagclass>
<bodycontent>JSP</bodycontent>
<attribute>
<name>pageBarName</name>
<required>true</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
</tag>
</taglib>
web.xml <taglib>
<taglib-uri>/WEB-INF/test_table.tld</taglib-uri>
<taglib-location>/WEB-INF/test_table.tld</taglib-location>
</taglib>
PageBar pageBar = new PageBarOne(request, page);
List outList = page.getData() // 这个是此叶输出的Listrequest.setAttribute("pageBar", pageBar);
request.setAttribute("outList ", outList );jsp
<%@taglib uri="/WEB-INF/test_table.tld" prefix="test"%>
//循环输出outList <test:pageBar pageBarName="pageBar"></test:pageBar>
where tt.r > 50 and tt.r <= 100;/**
*
*/
package table;import javax.servlet.http.HttpServletRequest;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2006</p>
*
* @version 1.0
*/
public class PageBarOne implements PageBar {
/** */
String url; /** */
int currentPage; /** */
int totalPages; /** */
int totalRows; /** */
private HttpServletRequest request; /**
* @param httpservletrequest
* @param pages
*/
public PageBarOne(HttpServletRequest httpservletrequest, Page pages){
request = httpservletrequest;
totalPages = pages.getTotalPage();
totalRows = pages.getTotalRows();
init();
} /* (non-Javadoc)
* @see table.PageBar#getCurrentPage()
*/
public int getCurrentPage() {
return currentPage;
} /* (non-Javadoc)
* @see table.PageBar#getHTML()
*/
public String getHTML() {
if (currentPage > totalPages)
currentPage = totalPages;
if (currentPage < 1)
currentPage = 1;
StringBuffer html = new StringBuffer("共");
html.append(totalRows);
html.append("项 第");
html.append(currentPage);
html.append("/");
html.append(totalPages);
html.append("页 "); if (currentPage == 1) {
html.append("<font color=#999999>首页 上页 </font>");
} else {
html.append("<a href=\"");
html.append(url);
html.append("page=1\">首页</a> <a href=\"");
html.append(url);
html.append("page=");
html.append(currentPage - 1);
html.append("\">上页</a> ");
} if (currentPage >= totalPages) {
html.append("<font color=#999999>下页 末页</font>");
} else {
html.append("<a href=\"");
html.append(url);
html.append("page=");
html.append(currentPage + 1);
html.append("\">下页</a> <a href=\"");
html.append(url);
html.append("page=");
html.append(totalPages);
html.append("\">末页</a>");
}
html.append(" <input name=\"pageNo\" type=\"text\" id=\"pageNo\" size=\"3\" onKeyPress=\"return handleEnterOnPageNo();\"> "
+ "<input name=\"goto\" type=\"button\" id=\"goto\" value=\"go\" onClick=\"forward();\">");
html.append("<script type = 'text/javaScript'>\r\n");
html.append("function forward()");
html.append("{");
html.append(" if(!(/^([-]){0,1}([0-9]){1,}$/.test(document.all.pageNo.value)))");
html.append("{");
html.append(" alert(\"请输入合法的页号!\");");
html.append(" document.all.pageNo.focus();");
html.append(" document.all.pageNo.select();");
html.append(" }");
html.append(" else");
html.append(" window.location.href=\"");
html.append(url);
html.append("page=\"+ document.all.pageNo.value;"); html.append("}");
html.append("function handleEnterOnPageNo()"); html.append("{");
html.append(" if(event.keyCode == 13)");
html.append(" {");
html.append(" forward();"); html.append(" return false;"); html.append(" }");
html.append(" return true;");
html.append("}");
html.append("</script>");
return html.toString();
} /* (non-Javadoc)
* @see table.PageBar#getTotalPages()
*/
public int getTotalPages() {
return totalPages;
} /* (non-Javadoc)
* @see table.PageBar#getTotalRows()
*/
public int getTotalRows() {
return totalRows;
} /**
* param @return
*/
public String getUrl() {
return url;
} /**
* param
*/
public void init() {
currentPage = request.getParameter("page")==null? 1: new Integer(request.getParameter("page")) .intValue();
String as[] = new String[] {"page", "totalrows"};
url = Common.getRequestURL(request, as);
//如果url 中没有? 就在末尾加上?
if(url.indexOf("?")==-1)
url+="?";
//如果url中的? 在末尾就不加& 不在末尾就加&
if(url.indexOf("?")==url.length()-1)
url = url + new StringBuffer("totalrows=").append(totalRows).append("&").toString();
else{
url = url + new StringBuffer("&totalrows=").append(totalRows).append("&").toString();
}
} /**
* param @param i
*/
public void setCurrentPage(int i) {
currentPage = i;
} /**
* param @param i
*/
public void setTotalPages(int i) {
totalPages = i;
} /**
* param @param i
*/
public void setTotalRows(int i) {
totalRows = i;
} /**
* param @param s
*/
public void setUrl(String s) {
url = s;
}
}package table;import java.io.IOException;
import java.io.Writer;
import javax.servlet.ServletRequest;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.TagSupport;public class PageBarTag extends TagSupport { /** */
private PageBar pageBar; /** */
private String pageBarName; /**
*
*/
public PageBarTag() {
pageBar = null;
} /* (non-Javadoc)
* @see javax.servlet.jsp.tagext.TagSupport#doEndTag()
*/
public int doEndTag() throws JspException {
String s = pageBar.getHTML();
try {
pageContext.getOut().write(s);
} catch (IOException e) {
throw new JspException("构造页面导航是IOerror:" + e.getMessage());
}
return 6;
} /* (non-Javadoc)
* @see javax.servlet.jsp.tagext.TagSupport#doStartTag()
*/
public int doStartTag() throws JspException {
if (pageBar != null) {
return super.EVAL_BODY_INCLUDE;
} else
throw new JspException("页面导航对象不能为空");
} /**
* param @return
*/
public String getPageBarName() {
return pageBarName;
} /**
* param @param pageBarName
*/
public void setPageBarName(String pageBarName) {
this.pageBarName = pageBarName;
this.pageBar = (PageBar) pageContext.getRequest().getAttribute(
this.pageBarName);
}}
test-table.tld<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE taglib PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.1//EN" "http://java.sun.com/j2ee/dtds/web-jsptaglibrary_1_1.dtd">
<taglib>
<tlibversion>1.0</tlibversion>
<jspversion>1.1</jspversion>
<shortname>test</shortname>
<uri>/WEB-INF/test-table</uri> <tag>
<name>pageBar</name>
<tagclass>table.PageBarTag</tagclass>
<bodycontent>JSP</bodycontent>
<attribute>
<name>pageBarName</name>
<required>true</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
</tag>
</taglib>
web.xml <taglib>
<taglib-uri>/WEB-INF/test_table.tld</taglib-uri>
<taglib-location>/WEB-INF/test_table.tld</taglib-location>
</taglib>