萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

JSP 通用分頁 JavaBean 組件設計與使用

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() 取總數,雙倍查詢開銷

現代化改進方案

  1. 改用 LIMIT/OFFSET (MySQL/PostgreSQL) 或 OFFSET ... FETCH (標準 SQL) 避免游標
  2. 分離查詢:COUNT(*) 走索引快速統計,主查詢只取當頁資料
  3. 無狀態設計:方法傳入參數、返回 PageResult<T> 對象,不保留實例變數
  4. 連線池:DataSource 注入,try-with-resources 自動關閉
  5. 泛型分頁 DTO:
    public class PageResult {
        private List data;
        private long total;
        private int pageNo, pageSize;
        // getter/setter + hasNext/hasPrev
    }
    
copyright © 萬盛學電腦網 all rights reserved