DBConnection 分頁組件完整代碼
package newsbean;
import java.sql.*;
public class DBConnection {
// SQL Server 2000 驅動與連線參數
private final String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
private final String url = "jdbc:microsoft:sqlserver://";
private final String server = "localsqlserver";
private final String port = "1433";
private final String dbName = "MyBusiDB";
private final String user = "sa";
private final String pwd = "111111";
private final String selectMethod = "cursor";
private Connection conn;
private Statement stmt;
private ResultSet rs;
// 分頁狀態
private int rowCount = 0, pageCount = 0, currentPage = 0;
public DBConnection() {
try { Class.forName(driver); }
catch (ClassNotFoundException e) { System.err.println("Driver load failed: " + e.getMessage()); }
}
private String getConnectionUrl() {
return url + server + ":" + port + ";databaseName=" + dbName + ";selectMethod=" + selectMethod + ";";
}
// === 分頁核心查詢 ===
public ResultSet execQuery(String sql, int pageSize, int pageNo) {
try {
conn = DriverManager.getConnection(getConnectionUrl(), user, pwd);
// 可滾動、可更新的 ResultSet 支援 absolute()
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
rs.last(); // 移至最後一行
rowCount = rs.getRow(); // 總筆數
pageCount = (rowCount + pageSize - 1) / pageSize; // 總頁數
if (pageNo > pageCount) pageNo = pageCount;
if (pageNo <= 0) pageNo = 1;
currentPage = pageNo;
if (pageCount > 0) {
// 定位到該頁第一筆
rs.absolute((pageNo - 1) * pageSize + 1);
}
} catch (SQLException ex) {
System.err.println("execQuery error: " + ex.getMessage());
}
return rs;
}
// Getter
public int getRowCount() { return rowCount; }
public int getPageCount() { return pageCount; }
public int getPage() { return currentPage; }
public String showPageNav() {
return currentPage + "/" + pageCount + " >>>";
}
// === 關閉資源 ===
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 e) { System.out.println("closeDB error: " + e.getMessage()); }
}
}
JSP 頁面使用範例
<jsp:useBean id="conn" class="newsbean.DBConnection" />
<%
int pageSize = 15;
int pageNo = 1;
String p = request.getParameter("pages");
if (p != null) pageNo = Integer.parseInt(p);
String sql = "SELECT number, sms_no, company_card_name, Reg_Date, Dead_Date, OnUse, province, City "
+ "FROM dbo.Company_Card WHERE 1=1 ";
if (!searchCompanyCard.equals("")) sql += " AND company_card_name LIKE '%" + searchCompanyCard + "%'";
if (!searchCity.equals("")) sql += " AND City = '" + searchCity + "'";
sql += " ORDER BY number DESC";
ResultSet rs = conn.execQuery(sql, pageSize, pageNo);
int totalRows = conn.getRowCount();
int totalPages = conn.getPageCount();
%>
<table>
<tr><th>序號</th><th>簡訊號</th><th>公司名</th><th>註冊日</th><th>省份</th><th>城市</th><th>操作</th></tr>
<%
int i = 0;
if (rs != null) {
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 < pageSize && rs.next());
}
%>
</table>
<div align="center">
<a href="?pages=1">首頁</a> |
<a href="?pages=<%= conn.getPage() - 1 %>">上一頁</a> |
第 <%= conn.getPage() %> / <%= conn.getPageCount() %> 頁 |
<a href="?pages=<%= conn.getPage() + 1 %>">下一頁</a> |
<a href="?pages=<%= conn.getPageCount() %>">尾頁</a>
<span> 共 <%= conn.getRowCount() %> 筆 </span>
</div>
<% conn.closeDB(); %>
設計優缺點分析
| 優點 | 缺點/風險 |
| 封裝分頁邏輯,JSP 簡潔 | 使用 TYPE_SCROLL_SENSITIVE 效能差,大數據集會全表掃描 |
| 狀態封裝於 Bean,可重用 | 非線程安全:同一 Bean 實例併發請求會互相覆蓋狀態 |
| 支援任意 SQL 查詢 | SQL 拼接存在注入風險,應用 PreparedStatement |
| 自動計算總頁數/導航 | 每次查詢都執行 rs.last() 取總數,雙倍查詢開銷 |
現代化改進方案
- 改用 LIMIT/OFFSET (MySQL/PostgreSQL) 或
OFFSET ... FETCH (標準 SQL) 避免游標
- 分離查詢:
COUNT(*) 走索引快速統計,主查詢只取當頁資料
- 無狀態設計:方法傳入參數、返回
PageResult<T> 對象,不保留實例變數
- 連線池:DataSource 注入,
try-with-resources 自動關閉
- 泛型分頁 DTO:
public class PageResult {
private List data;
private long total;
private int pageNo, pageSize;
// getter/setter + hasNext/hasPrev
}