DIV CSS 佈局教程網

打造可重複使用的 JSP 分頁類別
編輯:JavaScript綜合知識     

以下示範如何建立一個資料庫分頁類別,並在 JSP 中統一呼叫使用。

一、建立資料庫分頁類別

package newsbean;
import java.sql.*;

public class DBConnection{
//這裡使用 ms jdbc
String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
//指定資料庫名/url
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName = "localsqlserver";
private final String portNumber = "1433";
private final String databaseName = "MyBusiDB";
private final String dbUserName = "sa";
private final String dbPassword = "111111";
// 指示驅動程式使用伺服器端 cursor,以在同一連線上支援多個作用中的 statement
private final String selectMethod = "cursor";

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

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

public DBConnection(){
try
{
Class.forName(sDBDriver);
}
catch(ClassNotFoundException e)
{
System.err.println("DBConnection():" + e.getMessage());
}
}

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

//================考慮在類別中分頁======================================
//內部設定總條數
private void setRowCount(int irowcount)
{
this.iRowCount = irowcount;
}
//傳回內部設定的總條數
public int getRowCount()
{
return this.iRowCount;
}

//內部設定總頁數
private void setPageCount(int ipagecount)
{
this.iPageCount = ipagecount;
}
//傳回內部設定的總頁數
public int getPageCount()
{
return this.iPageCount;
}

//內部設定目前頁
private void setPage(int ipage)
{
this.iPage = ipage;
}
//傳回內部設定的目前頁
public int getPage()
{
return this.iPage;
}
//顯示翻頁資訊:參數為總頁數、總行數、目前頁
public String showChangePage()
{
return this.iPage + "/" + this.iPageCount + ">>>";
}

//考慮了翻頁的選擇查詢
public ResultSet execQuery(String sql, int iPageSize, int iPage)
{
try
{
conn = DriverManager.getConnection(getConnectionUrl(), dbUserName, dbPassword);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
rs.last();

//取得總行數:移到最後並擷取目前列號
int iRowCount = rs.getRow();
//計算總頁數:總 100 行、每頁 10 行 → 總頁數 = 10 頁(無條件進位)
int iPageCount = (iRowCount + iPageSize - 1) / iPageSize;
if (iPage > iPageCount) iPage = iPageCount;
if (iPage <= 0) iPage = 1;
if (iPageCount > 0){
//在方法體中已到達指定列
rs.absolute((iPage - 1) * iPageSize + 1);
}
//設定 iPageCount、iRowCount、iPage
setPageCount(iPageCount);
setRowCount(iRowCount);
setPage(iPage);
}//end try
catch(SQLException ex)
{
System.err.println("DBConnection.execQuery():" + ex.getMessage());
}//end catch

return rs;

}//end execQuery

//======================================================
//關閉資料庫
public void closeDB()
{
try
{
if(rs != null)
{
rs.close();
rs = null;
}
if(stmt != null)
{
stmt.close();
stmt = null;
}
if(conn != null)
{
conn.close();
conn = null;
}
}
catch(Exception ex)
{
System.out.println("DBConnection.closeDB()" + ex.getMessage());
}
}

}//end Class

二、在 JSP 程式中使用本類別

1、<jsp:useBean id="conn" class="newsbean.DBConnection" />

2、處理查詢參數 iPage、txtSearchKeyword 等。

3、文章列表:

int iPageSize = 15;//每次讀取列數,作為參數傳入
String sql = null;
ResultSet rs = null;
int iRowCount = 0;
int iPageCount = 0;
try{
//構造 sql 語句
sql = "SELECT top 200 number, sms_no, company_card_name, Reg_Date, Dead_Date, OnUse, province, "
+ "City FROM dbo.Company_Card where 1=1 ";
if (!(txtSearchCompanyCard.equals(""))) sql = sql + " and company_card_name like '%" + txtSearchCompanyCard + "%'";
if (!(txtSearchCity.equals(""))) sql = sql + " and City ='" + txtSearchCity + "'";
sql = sql + " order by number desc ";

//取得 resultset
rs = conn.execQuery(sql, iPageSize, iPage);

//取得記錄總數
iRowCount = conn.getRowCount();
//取得總頁數
iPageCount = conn.getPageCount();

//循環
do{
%>
<tr>
<td>[<%=rs.getRow()%>]</td>
<td><%=rs.getString("sms_no")%></td>
<td><%=rs.getString("company_card_name")%></td>
<td><%=rs.getDate("Reg_Date")%></td>
<td><%=rs.getString("province")%></td>
<td><%=rs.getString("City")%></td>
<td><a href="#" onclick="return domodify('<%=rs.getInt("number")%>')">修改</a></td>
</tr>
<%
}while(++i < iPageSize && rs.next());
}catch(Exception e){
out.print("rs Err:" + e.getMessage());
out.print(sql + "<br/>");
}%>

 

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved