你用什么数据库?数据库不同,分页语句不同的。
总纪录数totalRecords=select count(*) from tableName;
总页数totalPages=totalRecords/pageNum(每页纪录数);
if(totalRecords%pageNum!=0)
totalPages = totalRecords/pageNum+1;
当前页应该是你给出的参数:pageNo
根据pageNo和pageNum计算当前显示的纪录起始号和结束号
public int getPageBegin(pageNo)
{
return pageNum*(pageNo-1);
}public int getPageEnd(pageNo)
{
int endNo = getPageBegin(pageNo)+pageNum;
if(endNo>totalRecords)
endNo = totalRecords;
return endNo;
}
//Oracle:
public String getPageSQL(String sourceSQL)
{
String newTargetSQL = "";
newTargetSQL = "select * from (select rownum as numrow,c.* from ";
newTargetSQL += "("+sourceSQL+") c)where numrow>'" + getPageBegin() +"'and numrow<='" + getPageEnd()+"'";
return newTargetSQL;
}//MYSql:
public String getPageSQL(String sourceSQL)
{
String newTargetSQL = "";
newTargetSQL = sourceSQL + " limit " + getPageBegin() + ", " + getPerPageNo();
return newTargetSQL;
}
总纪录数totalRecords=select count(*) from tableName;
总页数totalPages=totalRecords/pageNum(每页纪录数);
if(totalRecords%pageNum!=0)
totalPages = totalRecords/pageNum+1;
当前页应该是你给出的参数:pageNo
根据pageNo和pageNum计算当前显示的纪录起始号和结束号
public int getPageBegin(pageNo)
{
return pageNum*(pageNo-1);
}public int getPageEnd(pageNo)
{
int endNo = getPageBegin(pageNo)+pageNum;
if(endNo>totalRecords)
endNo = totalRecords;
return endNo;
}
//Oracle:
public String getPageSQL(String sourceSQL)
{
String newTargetSQL = "";
newTargetSQL = "select * from (select rownum as numrow,c.* from ";
newTargetSQL += "("+sourceSQL+") c)where numrow>'" + getPageBegin() +"'and numrow<='" + getPageEnd()+"'";
return newTargetSQL;
}//MYSql:
public String getPageSQL(String sourceSQL)
{
String newTargetSQL = "";
newTargetSQL = sourceSQL + " limit " + getPageBegin() + ", " + getPerPageNo();
return newTargetSQL;
}
ResultSet rs = state.executeQuery(_sql);
首先要建立可以滚动的有标rs.absolute(n); //要跳到的第几条纪录
我的每一页要显示多少要用程序控制,因为我尝试过其他方法好象不行。记录数:
rs.last();
rs_count = rs.getRow(); //获取记录条数
按照这种方法你的sql语句不用特标构造
如果你有100万条数据,也不能都显示给用户看呀.
用户看文件也是有目的呀.
如:
SQL Server 语法:
Create proc sp_Page(@whichPage int,@PageSize int,@RsCount int output,@pageCount int output)
AS
BEGIN
SELECT @RsCount=Count(*) FROM myTable
SET @PageCount=@RsCount/@PageSize
SELECT TOP @PageSize * FROM (SELECT TOP @PAGESZIE*@WhichPage * FROM myTable ORDER BY ID DESC)
END
OK!一切就搞定了
/**
* <p>Title: DataBaseQuery</p>
* <p>Description: 用于数据库翻页查询操作</p>
* <p>Copyright: 厦门一方软件公司版权所有Copyright (c) 2002</p>
* <p>Company: 厦门一方软件公司</p>
* @author 小唐蔡
* @version 1.0
*/
import java.sql.*;
import javax.servlet.http.*;
import java.util.*;
import mshtang.StringAction;
public class DataBaseQuery
{ private HttpServletRequest request;
private StringAction S;
private String sql;
private String userPara;
private String[][] resultArray;
private String[] columnNameArray;
private String[] columnTypeArray;
private int pageSize;
private int columnCount;
private int currentPageNum;
private int currentPageRecordNum;
private int totalPages;
private int pageStartRecord;
private int totalRecord;
private static boolean initSuccessful;
private String currentJSPPageName;
private String displayMessage; public DataBaseQuery()
{
S = new StringAction();
sql = "";
pageSize = 10;
totalRecord = 0;
initSuccessful = false;
currentJSPPageName = "";
displayMessage = "";
columnNameArray = null;
columnTypeArray = null;
currentPageRecordNum = 0;
columnCount = 0;
} /**功能:数据库初始化操作,其它操作的前提。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param pageSize:每页显示记录数;
* @param startPageNum:开始显示页码
*/
public void init(Connection conn, HttpServletRequest request, String querySQL, int pageSize, int startPageNum)
{
if(conn != null)
{
this.request = request;
this.sql = request.getParameter("querySQL");
this.userPara = request.getParameter("userPara");
if(sql == null || sql.equals(""))
{
sql = querySQL;
}
if(this.userPara == null)
{
this.userPara = "";
}
if(S.isContains(sql, "select;from", ";", true))
{
try
{
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
columnNameArray = new String[columnCount];
columnTypeArray = new String[columnCount];
String columnName;
String value;
while(rs != null && rs.next())
{
totalRecord++;
if(totalRecord == 1)
{
for(int i = 0; i < columnCount; i++)
{
columnNameArray[i] = rsmd.getColumnName(i + 1);
columnTypeArray[i] = rsmd.getColumnTypeName(i + 1);
}
}
}
if(rs != null)
{
rs.close();
}
//在总记录数大于0的情况下进行下列操作 //获取链接图象
if(totalRecord > 0 && pageSize > 0 && columnCount > 0 && startPageNum > 0)
{
//获取总页数
totalPages = totalRecord / pageSize;
int tempNum = totalRecord % pageSize;
if(tempNum != 0)
{
totalPages++;
} //获得当前页页码
String currentPage = request.getParameter("currentPageNum");
currentPageNum = (currentPage == null || currentPage.equals(""))? startPageNum:Integer.parseInt(currentPage);
currentPageNum = (currentPageNum > totalPages)?totalPages:currentPageNum;
currentPageNum = (currentPageNum <= 0)?1:currentPageNum; //获得当前页起始显示记录数
pageStartRecord = (currentPageNum - 1) * pageSize + 1;
pageStartRecord = (pageStartRecord <= 0)?1:pageStartRecord;
pageStartRecord = (pageStartRecord > totalRecord)?totalRecord:pageStartRecord; //获得当前页显示记录数
if(currentPageNum * pageSize > totalRecord)
{
currentPageRecordNum = totalRecord - (currentPageNum - 1) * pageSize;
}
else
{
currentPageRecordNum = pageSize;
}
resultArray = new String[currentPageRecordNum][columnCount]; //用于跳过前面不需显示的记录
int continueRowNum = 0; //用于跳过后面不再显示的记录
int breakRowNum = 0; ResultSet rs2 = st.executeQuery(sql);
while(rs2 != null && rs2.next())
{
//跳过前面不需显示的记录
continueRowNum++;
if(continueRowNum < pageStartRecord)
{
continue;
}
//存取当前页需显示的记录到二维数组
for(int i = 0; i < columnCount; i++)
{
value = rs2.getString(columnNameArray[i]);
value = (value == null)?"":value.trim();
resultArray[breakRowNum][i] = value;
}
//跳过后面不再显示的记录
breakRowNum++;
if(breakRowNum >= currentPageRecordNum)
{
break;
}
}
rs2.close();
}
st.close();
conn.close();
}
catch(SQLException e)
{
if(e != null)
{
e.printStackTrace();
}
}
}
transferSQL(sql);
initSuccessful = true;
}
} /**功能:数据库初始化操作,其它操作的前提,默认每页显示10条记录。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param startPageNum:开始显示页码
*/
public void init(Connection conn, HttpServletRequest request, String querySQL, int startPageNum)
{
init(conn, request, querySQL, 10, startPageNum);
} /**功能:数据库初始化操作,其它操作的前提,默认从第一页开始显示。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param pageSize:每页显示记录数;
*/
public void init(Connection conn, HttpServletRequest request, int pageSize, String querySQL)
{
init(conn, request, querySQL, pageSize, 1);
} /**功能:数据库初始化操作,其它操作的前提,默认从第一页开始显示,每页显示10条记录。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
*/
public void init(Connection conn, HttpServletRequest request, String querySQL)
{
init(conn, request, querySQL, 10, 1);
} /**功能:给出没有初始化的提醒信息,内部调用。
*
*/
private static void getMessage()
{
if(!initSuccessful)
{
System.out.println("没有完成初始化");
}
} /**功能:得到查询结果的总记录数。
*
* @return
*/
public int getTotalRecord()
{
getMessage();
return totalRecord;
} /**功能:得到当前页的页码
*
* @return
*/
public int getCurrentPageNum()
{
getMessage();
return currentPageNum;
} /**功能:获得当前页记录数
*
* @return
*/
public int getCurrentPageRecord()
{
getMessage();
return currentPageRecordNum;
} /**功能:获得总页数
*
* @return
*/
public int getTotalPages()
{
getMessage();
return totalPages;
} /**获得调用该javaBean的jsp页面文件名,用于翻页操作,可以免去外界输入页面参数的错误,用于内部调用。
*
* @return:调用该javaBean的jsp页面文件名
*/
private String getCurrentJSPPageName()
{
getMessage();
if(request != null)
{
String tempPage = request.getRequestURI();
String[] tempArray = S.stringSplit(tempPage, "/");
if(tempArray != null && tempArray.length > 0)
{
currentJSPPageName = tempArray[tempArray.length - 1];
}
}
/**功能:用于显示图片链接或字符串(上一页、下一页等链接)。用于翻页操作,内部调用
*
* @param imageSource:图片来源;
* @param i:翻页信息,1表示第一页,2表示上一页,3表示下一页,4表示尾页,
* @return:显示的链接图片或链接文字
*/
private void displayMessage(String imageSource, int i)
{
getMessage();
if(imageSource != null && !imageSource.equals(""))
{
displayMessage = "<img src=\"" + imageSource + "\" border=\"0\">";
}
else
{
switch(i)
{
case 1:
displayMessage = "<font size=\"2\">[首页]</font>";
break;
case 2:
displayMessage = "<font size=\"2\">[上一页]</font>";
break;
case 3:
displayMessage = "<font size=\"2\">[下一页]</font>";
break;
case 4:
displayMessage = "<font size=\"2\">[尾页]</font>";
}
}
} /**功能:链接到相应页面,内部调用。
*
* @param imageSource:图片来源;
* @param i:翻页信息,1表示第一页,2表示上一页,3表示下一页,4表示尾页,
* @return:相应页面的链接
*/
private String getNavigation(String imageSource, int i)
{
displayMessage(imageSource, i);
int pageNum = 0;
switch(i)
{
case 1:
pageNum = 1;
break;
case 2:
pageNum = currentPageNum - 1;
break;
case 3:
pageNum = currentPageNum + 1;
break;
case 4:
pageNum = totalPages;
}
currentJSPPageName = "<a href=\"" + currentJSPPageName + "?currentPageNum=" + pageNum + "&querySQL=" + sql + userPara + "\">" + displayMessage + "</a>";
if(((currentPageNum == 1 || totalPages == 0) && (i == 1 || i == 2)) || (currentPageNum == totalPages && (i == 3 || i == 4)))
{
currentJSPPageName = displayMessage;
}
return currentJSPPageName;
} /**功能:翻到第一页。以图片显示。
*
* @param imageSource
* @return
*/
public String firstPage(String imageSource)
{
return makePages(imageSource, 1);
} /**功能:方法重载。翻到第一页,以文字显示。
*
* @return
*/
public String firstPage()
{
return firstPage("");
} /**功能:翻到上一页,以图片显示链接。
*
* @return
*/
public String nextPage(String imageSource)
{
return makePages(imageSource, 3);
} /**功能:方法重载。翻到下一页,以文字显示。
*
* @return
*/
public String nextPage()
{
return nextPage("");
} /**功能:翻到上一页,以图片显示链接。
*
* @return
*/
public String previousPage(String imageSource)
{
return makePages(imageSource, 2);
} /**功能:方法重载。翻到上一页,以文字显示。
*
* @return
*/
public String previousPage()
{
return previousPage("");
} /**功能:方法重载。翻到尾页,以图片显示链接。
*
* @return
*/
public String lastPage(String imageSource)
{
return makePages(imageSource, 4);
} /**功能:方法重载。翻到尾页,以文字显示链接。
*
* @return
*/
public String lastPage()
{
return lastPage("");
} /**功能:得到查询记录中某字段的值。
*
* @param recordIndex:记录索引号,从0开始。
* @param columnName:字段名;
* @return:有结果返回该字段的值,否则为"";
*/
public String getColumnValue(int recordIndex, String columnName)
{
getMessage();
String columnValue = "";
if(recordIndex >=0 && recordIndex < currentPageRecordNum)
{
int columnIndex = S.findElementIndexOfArray(columnNameArray, columnName, true);
if(resultArray != null && columnIndex != -1)
{
columnValue = resultArray[recordIndex][columnIndex];
}
}
return columnValue;
} /**功能:方法重载。返回特定行特定列的值。
*
* @param recordIndex:行索引,从0开始;
* @param columnIndex:列索引,从1开始;
* @return
*/
public String getColumnValue(int recordIndex, int columnIndex)
{
getMessage();
String columnValue = "";
if(resultArray != null && recordIndex >= 0 && recordIndex < currentPageRecordNum && columnIndex > 0 && columnIndex <= columnNameArray.length)
{
columnValue = resultArray[recordIndex][columnIndex];
}
return columnValue;
}
/**功能:返回数据库字段名构成的数组
*
* @return
*/
public String[] getColumnName()
{
getMessage();
return columnNameArray;
} /**返回某一字段的类型名,不合法字段则返回空字符串""。
*
* @param columnName
* @return
*/
public String getColumnType(String columnName)
{
getMessage();
String columnType = "";
int index = S.findElementIndexOfArray(columnNameArray, columnName, true);
if(index >= 0 && index <= columnCount)
{
columnType = columnTypeArray[index];
}
return columnType;
}
/**功能:显示下面一排的翻页链接导航条,以图片显示上一页和下一页链接,中间显示数字
*
* @param previousIMage:上一页链接图片;
* @param nextImage:下一页链接图片;
* @param displayPages:中间显示的链接数;
* @return
*/
public String pagesArrange(String previousIMage, String nextImage, int displayPages)
{
getMessage();
getCurrentJSPPageName();
String pagesArrange = previousPage(previousIMage);
int startPage = 0;
int endPage = 0;
if(displayPages > 0 && displayPages <= totalPages)
{
startPage = (totalPages - displayPages > currentPageNum)?currentPageNum:totalPages - displayPages;
if(startPage == 0)
{
startPage = 1;
}
endPage = (currentPageNum + displayPages > totalPages)?totalPages:currentPageNum + displayPages;
}
else if(displayPages > totalPages)
{
startPage = 1;
endPage = totalPages;
}
for(int i = startPage; i <= endPage; i++)
{
getCurrentJSPPageName();
if(i == currentPageNum)
{
pagesArrange += "<font size=\"2\">" + i + "</font> ";
}
else
{
pagesArrange += "<a href=\"" + currentJSPPageName + "?currentPageNum=" + i + "&querySQL=" + sql + userPara + "\"><font size=\"2\">" + i + "</font></a> ";
}
}
pagesArrange += " " + nextPage(nextImage);
return pagesArrange;
} /**功能:显示下面一排的翻页链接导航条,以文字显示上一页和下一页链接,中间显示数字
*
* @param displayPages:中间显示的链接数;
* @return
*/
public String pagesArrange(int displayPages)
{
return pagesArrange("", "", displayPages);
}
/**功能:给出查询结果信息。模式为:共几条记录,第几页/共几页。
*
* @return
*/
public String getQueryInfo()
{
getMessage();
currentPageNum = (totalPages == 0)? 0:currentPageNum;
String queryInfo = "<font size=\"2\">共" + totalRecord + "条记录,第" + currentPageNum + "页/共" + totalPages +"页</font>";
return queryInfo;
} /**功能:以二维数组返回所有结果。
*
* @return
*/
public String[][] getAllResult()
{
getMessage();
return resultArray;
} /**功能:得到所有记录特定列的值。
*
* @param columnIndex:列索引号,从1开始。
* @return
*/
public String[] getSpecialColumn(int columnIndex)
{
getMessage();
String[] columnValue = null;
if(columnIndex >= 1 && columnIndex <= columnNameArray.length && resultArray != null)
{
columnValue = new String[resultArray.length];
for(int i = 0; i < resultArray.length; i++)
{
columnValue[i] = resultArray[i][columnIndex - 1];
}
}
return columnValue;
} /**得到所有记录第一列值。
*
* @return
*/
public String[] getFirstColumn()
{
return getSpecialColumn(1);
} /**功能:得到查询结果中特定行的值。
*
* @param recordIndex
* @return
*/
public String[] getSpecialRow(int recordIndex)
{
getMessage();
String[] returnArray = null;
if(recordIndex < currentPageRecordNum && recordIndex >= 0 && resultArray != null)
{
returnArray = new String[columnNameArray.length];
returnArray = resultArray[recordIndex];
}
return returnArray;
} /**得到查询结果第一行的值。
*
* @return
*/
public String[] getFirstRow()
{
return getSpecialRow(0);
} /**显示图片导航条,并传递用户参数。
*
* @param firstImage:第一页链接图片;
* @param previousImage:第一页链接图片;
* @param nextImage:第一页链接图片;
* @param lastImage:第一页链接图片;
* @param userPara:用户参数。传递格式为:¶=para
* @return
*/
public String getNavigator(String firstImage, String previousImage, String nextImage, String lastImage, String userPara)
{
getMessage();
this.userPara = userPara;
String navigator = firstPage(firstImage) + " " + previousPage(previousImage) + " " + nextPage(nextImage) + " " + lastPage(lastImage);
return navigator;
} /**功能:方法重载。显示图片导航条,不传递用户参数。
*
* @param firstImage:第一页链接图片;
* @param previousImage:第一页链接图片;
* @param nextImage:第一页链接图片;
* @param lastImage:第一页链接图片;
* @return
*/
public String getNavigator(String firstImage, String previousImage, String nextImage, String lastImage)
{
return getNavigator(firstImage, previousImage, nextImage, lastImage, "");
} /**功能:方法重载。显示文字链接导航条,不传递用户参数。
*
* @return
*/
public String getNavigator()
{
return getNavigator("", "", "", "", "");
} /**功能:返回查询结果列数。
*
* @return
*/
public int getColumnCount()
{
getMessage();
return columnCount;
} private String makePages(String imageSource, int i)
{
getMessage();
getCurrentJSPPageName();
displayMessage(imageSource, i);
return getNavigation(imageSource, i);
} private void transferSQL(String SQL)
{
String[] sourceStr = {"%", "+", "/", "\\", "<", "=", ">", "(", ")", "|", "&", "?", "#", "$", "^", "{", "}", "[", "]", "~", "`", "!", ":", ";", "\"", ","};
String[] toStr = {"%25", "%2B", "%2F", "%5C", "%3C", "%3D", "%3E", "%28", "%29", "%7C", "%26", "%3F", "%23", "%24", "%5E", "%7B", "%7D", "%5B", "%5D", "%7E", "%60", "%21", "%3A", "%3B", "%22", "%2C"};
for(int i = 0; i < sourceStr.length; i++)
{
SQL = S.stringReplace(SQL, sourceStr[i], toStr[i]);
}
sql = SQL;
} /**功能:显示调试信息
*
* @return
*/
public String getDebugInfo()
{
String returnString = "";
if(initSuccessful)
{
returnString += "查询语句:" + sql;
}
else
{
returnString = "没有初始化,请用init()方法完成初始化";
}
return returnString;
} public String getStaticInfo(String firstImage, String previousImage, String nextImage, String lastImage)
{
String returnString = "";
returnString += "<font size=\"2\">共" + totalRecord + "条记录," + getNavigator(firstImage, previousImage, nextImage, lastImage) + "第" + currentPageNum + "页/共" + totalPages + "页</font>";
return returnString;
} public String getStaticInfo()
{
return getStaticInfo("", "", "", "");
}
public void freeGlobalValue()
{
if(initSuccessful)
{
resultArray = null;
columnNameArray = null;
columnTypeArray = null;
pageSize = 0;
columnCount = 0;
currentPageNum = 0;
currentPageRecordNum = 0;
totalPages = 0;
pageStartRecord = 1;
totalRecord = 0;
initSuccessful = false;
currentJSPPageName = "";
displayMessage = "";
}
}
}
请教该类的写法。否则代码不完整啊!好人做到底吧,先谢谢了! [email protected] 下次单独的送你分。呵呵~~~~