jspページング技術はどのように実現したのか??
15997 ワード
title: JSP
summary:
author: evan_zhao
email: [email protected]
HttpSession bean , 。 : ; , , 。
, ResultSet ( rs.last();rs.getRow() , rs.absolute() )。 ( oracle) JDBC , 。
ResultSet 。 ResultSet Statement Connection , ResultSet 。
。 , , , 。 , 。
oracle ROWNUM ( 1 )。 select * from employee where rownum<10 10 。 rownum , employee birthday 100 120 :
[pre] select * from (
select my_table.*, rownum as my_rownum from (
select name, birthday from employee order by birthday
) my_table where rownum <120
) where my_rownum>=100
[/pre]
mySQL LIMIT :
select name, birthday from employee order by birthday LIMIT 99,20
DB2 rownumber() 。
SQL Server , :http://www.csdn.net/develop/article/18/18627.shtm
Web , 。 ,sql , 、 、 , 。 , 。 :
PagedStatement 、 、 、 , PreparedStatement 。
RowSetPage PetStore page by page iterator , RowSetPage ( OracleCachedRowSet , CachedRowSet JSP ) 、 、 , HTML 。
PagedStatement RowsetPage。
:
//DAO :
…
public RowSetPage getEmployee(String gender, int pageNo) throws Exception{
String sql="select emp_id, emp_code, user_name, real_name from employee where gender =?";
// Oracle , 5
PagedStatement pst =new PagedStatementOracleImpl(sql, pageNo, 5);
pst.setString(1, gender);
return pst.executeQuery();
}
//Servlet :
…
int pageNo;
try{
// pageno
pageNo = Integer.parseInt(request.getParameter("pageno") );
}catch(Exception ex){
//
pageNo=1;
}
String gender = request.getParameter("gender" );
request.setAttribute("empPage", myBean.getEmployee(gender, pageNo) );
…
//JSP
<%@ page import = "page.RowSetPage"%>
…
<script language="javascript">
function doQuery(){
form1.actionType.value="doQuery";
form1.submit();
}
</script>
…
<form name=form1 method=get>
<input type=hidden name=actionType>
:
<input type=text name=gender size=1 value="<%=request.getParameter("gender")%>">
<input type=button value=" " onclick="doQuery()">
<%
RowSetPage empPage = (RowSetPage)request.getAttribute("empPage");
if (empPage == null ) empPage = RowSetPage.EMPTY_PAGE;
%>
…
<table cellspacing="0" width="90%">
<tr> <td>ID</td> <td> </td> <td> </td> <td> </td> </tr>
<%
javax.sql.RowSet empRS = (javax.sql.RowSet) empPage.getRowSet();
if (empRS!=null) while (empRS.next() ) {
%>
<tr>
<td><%= empRS.getString("EMP_ID")%></td>
<td><%= empRS.getString("EMP_CODE")%></td>
<td><%= empRS.getString("USER_NAME")%></td>
<td><%= empRS.getString("REAL_NAME")%></td>
</tr>
<%
}// end while
%>
<tr>
<%
// (pageno) 。
// doQuery javascript , pageno
%>
<td colspan=4><%= empPage .getHTML("doQuery", "pageno")%></td>
</tr>
</table>
</form>
:
, javascript ( doQuery), RowSetPage.getHTML() javascript 。 , <input type=text name=gender size=1 value="<%=request.getParameter("gender")%>">。 , 。
URL, QueryString URL 。 , servlet, 。
RowSetPage.getHTML() ,RowSetPage getter ( 、 、 )。
jsp taglib, JSP , Java Code。
: , , 。
1.Page.java
2.RowSetPage.java(RowSetPage Page)
3.PagedStatement.java
4.PagedStatementOracleImpl.java(PagedStatementOracleImpl PagedStatement)
, author [email protected]
///////////////////////////////////
//
// Page.java
// author: [email protected]
//
///////////////////////////////////
package page;
import java.util.List;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
/**
* Title: <br>
* Description: <br>
* Page , ,
* ,<br>
* RowSetPage RowSet ,ListPage List <br>
* Copyright: Copyright (c) 2002 <br>
* @author [email protected] <br>
* @version 1.0
*/
public class Page implements java.io.Serializable {
public static final Page EMPTY_PAGE = new Page();
public static final int DEFAULT_PAGE_SIZE = 20;
public static final int MAX_PAGE_SIZE = 9999;
private int myPageSize = DEFAULT_PAGE_SIZE;
private int start;
private int avaCount,totalSize;
private Object data;
private int currentPageno;
private int totalPageCount;
/**
* ,
*/
protected Page(){
this.init(0,0,0,DEFAULT_PAGE_SIZE,new Object());
}
/**
* ,
* @param start
* @param avaCount
* @param totalSize
* @param pageSize
* @param data
*/
protected void init(int start, int avaCount, int totalSize, int pageSize, Object data){
this.avaCount =avaCount;
this.myPageSize = pageSize;
this.start = start;
this.totalSize = totalSize;
this.data=data;
//System.out.println("avaCount:"+avaCount);
//System.out.println("totalSize:"+totalSize);
if (avaCount>totalSize) {
//throw new RuntimeException(" ?!");
}
this.currentPageno = (start -1)/pageSize +1;
this.totalPageCount = (totalSize + pageSize -1) / pageSize;
if (totalSize==0 && avaCount==0){
this.currentPageno = 1;
this.totalPageCount = 1;
}
//System.out.println("Start Index to Page No: " + start + "-" + currentPageno);
}
public Object getData(){
return this.data;
}
/**
* ( )
* @return
*/
public int getPageSize(){
return this.myPageSize;
}
/**
*
* @return
*/
public boolean hasNextPage() {
/*
if (avaCount==0 && totalSize==0){
return false;
}
return (start + avaCount -1) < totalSize;
*/
return (this.getCurrentPageNo()<this.getTotalPageCount());
}
/**
*
* @return
*/
public boolean hasPreviousPage() {
/*
return start > 1;
*/
return (this.getCurrentPageNo()>1);
}
/**
*
* @return
*/
public int getStart(){
return start;
}
/**
*
* @return
*/
public int getEnd(){
int end = this.getStart() + this.getSize() -1;
if (end<0) {
end = 0;
}
return end;
}
/**
*
* @return rownum
*/
public int getStartOfPreviousPage() {
return Math.max(start-myPageSize, 1);
}
/**
*
* @return rownum
*/
public int getStartOfNextPage() {
return start + avaCount;
}
/**
* ,
* @param pageNo
* @return rownum
*/
public static int getStartOfAnyPage(int pageNo){
return getStartOfAnyPage(pageNo, DEFAULT_PAGE_SIZE);
}
/**
*
* @param pageNo
* @param pageSize
* @return rownum
*/
public static int getStartOfAnyPage(int pageNo, int pageSize){
int startIndex = (pageNo-1) * pageSize + 1;
if ( startIndex < 1) startIndex = 1;
//System.out.println("Page No to Start Index: " + pageNo + "-" + startIndex);
return startIndex;
}
/**
*
* @return
*/
public int getSize() {
return avaCount;
}
/**
*
* @return
*/
public int getTotalSize() {
return this.totalSize;
}
/**
*
* @return
*/
public int getCurrentPageNo(){
return this.currentPageno;
}
/**
*
* @return
*/
public int getTotalPageCount(){
return this.totalPageCount;
}
/**
*
* @param queryJSFunctionName JS ,
* @param pageNoParamName
* @return
*/
public String getHTML(String queryJSFunctionName, String pageNoParamName){
if (getTotalPageCount()<1){
return "<input type='hidden' name='"+pageNoParamName+"' value='1' >";
}
if (queryJSFunctionName == null || queryJSFunctionName.trim().length()<1) {
queryJSFunctionName = "gotoPage";
}
if (pageNoParamName == null || pageNoParamName.trim().length()<1){
pageNoParamName = "pageno";
}
String gotoPage = "_"+queryJSFunctionName;
StringBuffer html = new StringBuffer("
");
html.append("<script language=\"Javascript1.2\">
")
.append("function ").append(gotoPage).append("(pageNo){
")
.append( " var curPage=1;
")
.append( " try{ curPage = document.all[\"")
.append(pageNoParamName).append("\"].value;
")
.append( " document.all[\"").append(pageNoParamName)
.append("\"].value = pageNo;
")
.append( " ").append(queryJSFunctionName).append("(pageNo);
")
.append( " return true;
")
.append( " }catch(e){
")
// .append( " try{
")
// .append( " document.forms[0].submit();
")
// .append( " }catch(e){
")
.append( " alert(' :function ")
.append(queryJSFunctionName).append("()');
")
.append( " document.all[\"").append(pageNoParamName)
.append("\"].value = curPage;
")
.append( " return false;
")
// .append( " }
")
.append( " }
")
.append( "}")
.append( "</script>
")
.append( "");
html.append( "<table border=0 cellspacing=0 cellpadding=0 align=center width=80%>
")
.append( " <tr>
")
.append( " <td align=left><br>
");
html.append( " " ).append( getTotalPageCount() ).append( " ")
.append( " [") .append(getStart()).append("..").append(getEnd())
.append("/").append(this.getTotalSize()).append("]
")
.append( " </td>
")
.append( " <td align=right>
");
if (hasPreviousPage()){
html.append( "[<a href='javascript:").append(gotoPage)
.append("(") .append(getCurrentPageNo()-1)
.append( ")'> </a>]
");
}
html.append( " ")
.append( " <select name='")
.append(pageNoParamName).append("' onChange='javascript:")
.append(gotoPage).append("(this.value)'>
");
String selected = "selected";
for(int i=1;i<=getTotalPageCount();i++){
if( i == getCurrentPageNo() )
selected = "selected";
else selected = "";
html.append( " <option value='").append(i).append("' ")
.append(selected).append(">").append(i).append("</option>
");
}
if (getCurrentPageNo()>getTotalPageCount()){
html.append( " <option value='").append(getCurrentPageNo())
.append("' selected>").append(getCurrentPageNo())
.append("</option>
");
}
html.append( " </select>
");
if (hasNextPage()){
html.append( " [<a href='javascript:").append(gotoPage)
.append("(").append((getCurrentPageNo()+1))
.append( ")'> </a>]
");
}
html.append( "</td></tr></table>
");
return html.toString();
}
}
///////////////////////////////////
//
// RowSetPage.java
// author: [email protected]
//
///////////////////////////////////
package page;
import javax.sql.RowSet;
/**
* <p>Title: RowSetPage</p>
* <p>Description: RowSet </p>
* <p>Copyright: Copyright (c) 2003</p>
* @author [email protected]
* @version 1.0
*/
public class RowSetPage extends Page {
private javax.sql.RowSet rs;
/**
*
*/
public static final RowSetPage EMPTY_PAGE = new RowSetPage();
/**
* ,
*/
public RowSetPage(){
this(null, 0,0);
}
/**
*
*@param crs OracleCachedRowSet
*@param start
*@param totalSize
*/
public RowSetPage(RowSet crs, int start, int totalSize) {
this(crs,start,totalSize,Page.DEFAULT_PAGE_SIZE);
}
/**
*
*@param crs OracleCachedRowSet
*@param start
*@param totalSize
*@pageSize
*/
public RowSetPage(RowSet crs, int start, int totalSize, int pageSize) {
try{
int avaCount=0;
if (crs!=null) {
crs.beforeFirst();
if (crs.next()){
crs.last();
avaCount = crs.getRow();
}
crs.beforeFirst();
}
rs = crs;
super.init(start,avaCount,totalSize,pageSize,rs);
}catch(java.sql.SQLException sqle){
throw new RuntimeException(sqle.toString());
}
}
/**
*
*/
public javax.sql.RowSet getRowSet(){
return rs;
}
}
///////////////////////////////////
//
// PagedStatement.java
// author: [email protected]
//
///////////////////////////////////
package page;
import foo.DBUtil;
import java.math.BigDecimal;
import java.util.List;
import java.util.Iterator;
import java.util.Collections;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import javax.sql.RowSet;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2002</p>
* @author [email protected]
* @version 1.0
*/
public abstract class PagedStatement {
public final static int MAX_PAGE_SIZE = Page.MAX_PAGE_SIZE;
protected String countSQL, querySQL;
protected int pageNo,pageSize,startIndex,totalCount;
protected javax.sql.RowSet rowSet;
protected RowSetPage rowSetPage;
private List boundParams;
/**
* PageStatement
* @param sql query sql
*/
public PagedStatement(String sql){
this(sql,1,MAX_PAGE_SIZE);
}
/**
* PageStatement
* @param sql query sql
* @param pageNo
*/
public PagedStatement(String sql, int pageNo){
this(sql, pageNo, Page.DEFAULT_PAGE_SIZE);
}
/**
* PageStatement,
* @param sql query sql
* @param pageNo
* @param pageSize
*/
public PagedStatement(String sql, int pageNo, int pageSize){
this.pageNo = pageNo;
this.pageSize = pageSize;
this.startIndex = Page.getStartOfAnyPage(pageNo, pageSize);
this.boundParams = Collections.synchronizedList(new java.util.LinkedList());
this.countSQL = "select count(*) from ( " + sql +") ";
this.querySQL = intiQuerySQL(sql, this.startIndex, pageSize);
}
/**
* sql
*@param sql
*@startIndex
*@size
*/
protected abstract String intiQuerySQL(String sql, int startIndex, int size);
/**
*
*@param index 1, 2