一、建立數據庫分頁的類
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";
//String sConnStr = "jdbc:microsoft:sqlserver://localsqlserver:1433;DatabaseName=ourcompany";
private final String dbUserName = "sa";
private final String dbPassword = "111111";
// Informs the driver to use server a side-cursor,
// 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 = 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;
}
//顯示翻頁信息
//參數：總頁數,總行數，當前頁
//應該考慮加一個查詢參數列表進入本方法--2007-4-12
public String showChangePage()
{
return this.iPage + "/" + this.iPageCount + ">>>";
}
//考慮了翻頁的選擇查詢
public ResultSet execQuery(String sql,int iPageSize,int iPage)
{//======iRowCount== iPageCount==iPageSize===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行===〉總頁數= 11.4頁==> 12頁
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);
//stmt.close();???
}//end try
catch(SQLException ex)
{
System.err.println("DBConnection.execQuery():" + ex.getMessage());
}//end catch
return rs;
}//end execQuery
//======================================================
//關閉數據庫
public void closeDB()
{
try
{
//System.out.println("DBConnection.closeDB( here!)" );
if(rs!=null)
{
rs.close();
rs = null;
}
else
{
System.out.println("rs closed!");
}//?????
if(stmt!=null)
{
stmt.close();
stmt = null;
}
else
{
System.out.println("stmt closed!");
}
if(conn!=null)
{
conn.close();
conn = null;
}
else
{
System.out.println("conn closed!");
}
}
catch(Exception ex)
{
//System.err.println("DBConnection.closeDB()" + ex.getMessage());
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;//每次讀行數，作為參數傳入<jsp:setProperty id......沒用上！
String sql=null;
ResultSet rs =null;
int iRowCount = 0;
int iPageCount = 0;
try{
//===========================
int i = 0;
//l構造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/>");
}%>