jsp分頁類－－－統一使用

　　一建立數據庫分頁的類

　　package newsbean;
import javasql*;

　　public class DBConnection{
//這裡使用ms jdbc
String sDBDriver = "commicrosoftjdbcsqlserverSQLServerDriver";
//指定數據庫名/url
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localsqlserver";
private final String portNumber = "";
private final String databaseName= "MyBusiDB";
//String sConnStr = "jdbc:microsoft:sqlserver://localsqlserver:;DatabaseName=ourcompany";
private final String dbUserName = "sa";
private final String dbPassword = "";
// Informs the driver to use server a sidecursor
// which permits more than one active statement
// on a connection
private final String selectMethod = "cursor";

　　Connection conn = null;
ResultSet rs = null;
Statement stmt = null;

　　//這三個參數用於記錄翻頁
int iRowCount = ; //返回總行數
int iPageCount = ; //返回總頁數
int iPage = ;

　　public DBConnection(){
try
{
ClassforName(sDBDriver);
}
catch(ClassNotFoundException e)
{
Systemerrprintln("DBConnection():" + egetMessage());
}
}

　　//構造一個連接字符串
private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}

　　//================考慮在類中的分頁======================================
//內部設置總條數
private void setRowCount(int irowcount)
{
thisiRowCount = irowcount;
}
//返回內部設置的總條數
public int getRowCount()
{
return thisiRowCount;
}

　　//內部設置總頁數
private void setPageCount(int ipagecount)
{
thisiPageCount = ipagecount;
}
//返回內部設置的總頁數
public int getPageCount()
{
return thisiPageCount;
}

　　//內部設置當前頁
private void setPage(int ipage)
{
thisiPage = ipage;
}
//返回內部設置的總頁數
public int getPage()
{
return thisiPage;
}
//顯示翻頁信息
//參數總頁數總行數當前頁
//應該考慮加一個查詢參數列表進入本方法
public String showChangePage()
{
return thisiPage + "/" + thisiPageCount + ">>>";
}

　　//考慮了翻頁的選擇查詢
public ResultSet execQuery(String sqlint iPageSizeint iPage)
{//======iRowCount== iPageCount==iPageSize===iPage================
try
{
conn = DriverManagergetConnection(getConnectionUrl()dbUserNamedbPassword);
stmt = conncreateStatement(ResultSetTYPE_SCROLL_SENSITIVEResultSetCONCUR_UPDATABLE);
rs = stmtexecuteQuery(sql);
rslast();

　　//獲取總行數 移動到最後檢索當前行編號
int iRowCount= rsgetRow();
//計算總頁數 總行 每頁行===〉總頁數= 頁==> 頁
int iPageCount = (iRowCount + iPageSize) / iPageSize;
if(iPage>iPageCount) iPage = iPageCount;
if(iPage <= ) iPage = ;
if(iPageCount>){
//在方法體中已經到了指定行
rsabsolute((iPage) * iPageSize +);
}
//自己設置iPageCount和iRowCountiPage避免混淆？！
setPageCount(iPageCount);
setRowCount(iRowCount);
setPage(iPage);
//stmtclose();???
}//end try
catch(SQLException ex)
{
Systemerrprintln("DBConnectionexecQuery():" + exgetMessage());
}//end catch

　　return rs;

　　}//end execQuery

　　//======================================================
//關閉數據庫
public void closeDB()
{
try
{

　　//Systemoutprintln("DBConnectioncloseDB( here!)" );
if(rs!=null)
{
rsclose();
rs = null;
}
else
{
Systemoutprintln("rs closed!");
}//?????

　　if(stmt!=null)
{
stmtclose();
stmt = null;
}
else
{
Systemoutprintln("stmt closed!");
}
if(conn!=null)
{
connclose();
conn = null;
}
else
{
Systemoutprintln("conn closed!");
}
}
catch(Exception ex)
{
//Systemerrprintln("DBConnectioncloseDB()" + exgetMessage());
Systemoutprintln("DBConnectioncloseDB()" + exgetMessage());
}
}

　　
}//end Class

　　二jsp程序中使用本類過程
<jsp:useBean id="conn" class="newsbeanDBConnection" />
處理查詢參數iPage txtSearchKeyword等
文章列表

　　int iPageSize = ;//每次讀行數作為參數傳入<jsp:setProperty id沒用上！
String sql=null;
ResultSet rs =null;
int iRowCount = ;
int iPageCount = ;
try{
//===========================
int i = ;
//l構造sql語句
sql="SELECT top number sms_no company_card_name Reg_Date Dead_Date OnUse province "
+ "City FROM dboCompany_Card where = " ;
if(!( txtSearchCompanyCardequals("")))sql = sql + " and company_card_name like %" + txtSearchCompanyCard + "%";
if(!( txtSearchCityequals("")))sql = sql + " and City =" + txtSearchCity + "";
sql = sql + " order by number desc ";

　　//取得resultset
rs = connexecQuery(sqliPageSizeiPage);

　　//獲取記錄總數
iRowCount = conngetRowCount();
//獲取總頁數
iPageCount = conngetPageCount();

　　//循環
do{
%>
<tr>
<td>[<%=rsgetRow()%>]</td>
<td><%=rsgetString("sms_no")%></td>
<td><%=rsgetString("company_card_name")%></td>
<td><%=rsgetDate("Reg_Date")%></td>
<td><%=rsgetString("province")%></td>
<td><%=rsgetString("City")%></td>
<td><a href="#" onclick="return domodify(<%=rsgetInt("number")%>)">修改</a></td>
</tr>
<%
}while(++i<iPageSize && rsnext());
}catch(Exception e){
outprint("rs Err:" + egetMessage());
outprint(sql + "<br/>");
}%>

From:http://tw.wingwit.com/Article/program/Java/JSP/201311/20574.html