通用資料庫連線池 JavaBean 實作：ConnectionBean 與 PoolBean 完整原始碼

前言

資料庫連線建立開銷大 (網路握手、驗證、資源分配)，連線池透過預先建立、重複使用連線，大幅提升應用效能。本文提供一個純 JavaBean 實作的通用連線池，含連線包裝器 ConnectionBean 與池管理器 PoolBean，符合 JavaBean 規範，可整合於 JSP/Servlet/EJB 環境。

核心類別 1：ConnectionBean — 連線包裝器

package com.pool;
import java.io.Serializable;
import java.sql.*;

public class ConnectionBean implements Serializable {
    private Connection vConnection = null;
    private boolean bUseState = false; // 使用中標記

    public ConnectionBean() {}
    public ConnectionBean(Connection connection) {
        if (connection != null) vConnection = connection;
    }

    // JavaBean 屬性
    public Connection getConnection() { return vConnection; }
    public void setConnection(Connection connection) { vConnection = connection; }
    public boolean getUseState() { return bUseState; }
    public void setUseState(boolean bUseState) { this.bUseState = bUseState; }

    // 關閉實體連線
    public void close() {
        try { if (vConnection != null) vConnection.close(); }
        catch (SQLException e) { System.err.println(e.getMessage()); }
    }
}

核心類別 2：PoolBean — 連線池管理器

package com.pool;
import java.io.Serializable;
import java.sql.*;
import java.util.*;
import com.pool.ConnectionBean;

public class PoolBean implements Serializable {
    // 配置屬性
    private String strDriver = null;
    private String strURL = null;
    private int iSize = 0;
    private String strUserName = "";
    private String strPassword = "";

    // 內部狀態
    private Vector vPool = null; // 存放 ConnectionBean

    // 建構子與配置屬性 Getter/Setter (省略，標準 JavaBean 模式)
    public PoolBean() {}
    public void setDriver(String d) { if (d != null) strDriver = d; }
    public String getDriver() { return strDriver; }
    public void setURL(String u) { if (u != null) strURL = u; }
    public String getURL() { return strURL; }
    public void setSize(int s) { if (s > 1) iSize = s; }
    public int getSize() { return iSize; }
    public void setUserName(String u) { if (u != null) strUserName = u; }
    public String getUserName() { return strUserName; }
    public void setPassword(String p) { if (p != null) strPassword = p; }
    public String getPassword() { return strPassword; }

    // 初始化連線池：建立指定數量連線
    public synchronized void initializePool() throws Exception {
        if (strDriver == null) throw new Exception("未提供驅動程式名稱!");
        if (strURL == null) throw new Exception("未提供 URL!");
        if (iSize < 1) throw new Exception("連線池大小必須大於 0!");

        try {
            Class.forName(strDriver);
            for (int i = 0; i < iSize; i++) {
                Connection conn = createConnection();
                if (conn != null) addConnection(new ConnectionBean(conn));
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            throw e;
        }
    }

    private Connection createConnection() throws Exception {
        return DriverManager.getConnection(strURL, strUserName, strPassword);
    }

    private void addConnection(ConnectionBean cb) {
        if (vPool == null) vPool = new Vector(iSize);
        vPool.addElement(cb);
    }

    // 取得可用連線：優先重用閒置，不足則動態新增
    public synchronized Connection getConnection() throws Exception {
        // 1. 尋找閒置連線
        for (int i = 0; i < vPool.size(); i++) {
            ConnectionBean cb = (ConnectionBean) vPool.elementAt(i);
            if (!cb.getUseState()) {
                cb.setUseState(true);
                return cb.getConnection();
            }
        }
        // 2. 池滿則動態擴充 (超過初始大小)
        try {
            Connection conn = createConnection();
            ConnectionBean cb = new ConnectionBean(conn);
            cb.setUseState(true);
            vPool.addElement(cb);
            return conn;
        } catch (Exception e) {
            System.err.println(e.getMessage());
            throw e;
        }
    }

    // 歸還連線：標記為閒置
    public synchronized void releaseConnection(Connection conn) {
        for (int i = 0; i < vPool.size(); i++) {
            ConnectionBean cb = (ConnectionBean) vPool.elementAt(i);
            if (cb.getConnection() == conn) {
                cb.setUseState(false);
                System.err.println("釋放第 " + i + " 個連線!");
                break;
            }
        }
    }

    // 關閉所有連線：銷毀池
    public synchronized void emptyPool() {
        for (int i = 0; i < vPool.size(); i++) {
            ConnectionBean cb = (ConnectionBean) vPool.elementAt(i);
            if (!cb.getUseState()) {
                cb.close();
            } else {
                try {
                    Thread.sleep(20000); // 等待使用中連線釋放
                    cb.close();
                } catch (InterruptedException e) {
                    System.err.println(e.getMessage());
                }
            }
        }
    }
}

使用範例：JSP 頁面整合

<%@ page import="com.pool.*, java.sql.*" %>
<%
    // 應用啟動時初始化 (建議放在 ServletContextListener)
    PoolBean pool = new PoolBean();
    pool.setDriver("oracle.jdbc.driver.OracleDriver");
    pool.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
    pool.setUserName("scott");
    pool.setPassword("tiger");
    pool.setSize(10);
    pool.initializePool();
    application.setAttribute("dbPool", pool);
%>

<%
    // 業務邏輯中使用
    PoolBean pool = (PoolBean) application.getAttribute("dbPool");
    Connection conn = null;
    try {
        conn = pool.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
        while (rs.next()) {
            out.println(rs.getString("ename") + "<br>");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (conn != null) pool.releaseConnection(conn); // 必須歸還!
    }
%>

設計優缺點分析

現代替代方案建議

生產環境強烈建議使用成熟連線池框架：

• HikariCP：極致效能、輕量、預設最佳化
• Druid：阿里巴巴出品、監控強大、SQL 防火牆
• Apache DBCP2 / Tomcat JDBC Pool：Servlet 容器整合良好
• 應用伺服器內建 DataSource：WebLogic、WebSphere、JBoss、Tomcat 皆提供標準 javax.sql.DataSource 實作，透過 JNDI 注入，零程式碼配置

結語

本連線池 Bean 展示了資源池化核心邏輯：預建實例集合 + 狀態追蹤 + 同步存取 + 生命週期管理。雖然現代專案極少自行實作連線池，但理解其運作原理 (借用/歸還模式、閒置清理、動態擴充) 有助於：排查連線洩漏、調優池參數、理解 DataSource 內部行為、在受限環境 (無第三方庫) 實作輕量替代方案。