通用資料庫連線池 JavaBean 實現 (ConnectionBean + PoolBean)

前言

早期 JDBC 應用常自行實作連線池。本文提供一組可序列化的 ConnectionBean (連線包裝) 與 PoolBean (池管理) 參考實現，展示核心邏輯：初始化預建連線、同步借還、動態擴展、優雅關閉。

1. ConnectionBean - 連線包裝器

public class ConnectionBean implements Serializable {
    private Connection conn;
    private boolean inUse = false;

    public ConnectionBean() {}
    public ConnectionBean(Connection c) { this.conn = c; }

    public Connection getConnection() { return conn; }
    public void setConnection(Connection c) { this.conn = c; }
    public boolean getUseState() { return inUse; }
    public void setUseState(boolean b) { this.inUse = b; }

    public void close() {
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

2. PoolBean - 連線池核心

public class PoolBean implements Serializable {
    private String driver, url, user, password;
    private int poolSize = 10;
    private Vector pool = new Vector<>();

    // 屬性 setter/getter 略 (driver, url, user, password, poolSize)

    // 初始化：載入驅動、預建連線
    public synchronized void initializePool() throws Exception {
        if (driver == null) throw new Exception("驅動未設定");
        if (url == null) throw new Exception("URL 未設定");
        if (poolSize < 1) throw new Exception("池大小需 >= 1");
        Class.forName(driver);
        for (int i = 0; i < poolSize; i++) {
            Connection c = createConnection();
            if (c != null) pool.add(new ConnectionBean(c));
        }
    }

    private Connection createConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    // 取得連線：尋找閒置，無則動態新建 (超過初始上限)
    public synchronized Connection getConnection() throws Exception {
        for (ConnectionBean cb : pool) {
            if (!cb.getUseState()) {
                cb.setUseState(true);
                return cb.getConnection();
            }
        }
        // 池滿動態擴展
        Connection c = createConnection();
        ConnectionBean cb = new ConnectionBean(c);
        cb.setUseState(true);
        pool.add(cb);
        return c;
    }

    // 歸還連線：標記閒置
    public synchronized void releaseConnection(Connection c) {
        for (ConnectionBean cb : pool) {
            if (cb.getConnection() == c) {
                cb.setUseState(false);
                break;
            }
        }
    }

    // 關閉池：等待忙碌連線釋放 (最長 20 秒)
    public synchronized void emptyPool() {
        for (ConnectionBean cb : pool) {
            if (!cb.getUseState()) cb.close();
            else {
                try { Thread.sleep(20000); cb.close(); }
                catch (InterruptedException e) { e.printStackTrace(); }
            }
        }
    }

    // 供 JSP/Servlet 直接取得包裝 Bean (可選)
    public ConnectionBean getConnectionBean() throws Exception {
        return new ConnectionBean(getConnection());
    }
}

3. 使用範例 (JSP/Servlet)

// 應用啟動時初始化 (ServletContextListener)
PoolBean pool = new PoolBean();
pool.setDriver("com.mysql.jdbc.Driver");
pool.setUrl("jdbc:mysql://localhost:3306/mydb");
pool.setUser("root"); pool.setPassword("123");
pool.setSize(20);
pool.initializePool();
// 存入 ServletContext 供全域使用

// 業務代碼取用
Connection conn = null;
try {
    conn = pool.getConnection();
    // 執行 SQL...
} finally {
    if (conn != null) pool.releaseConnection(conn);
}

⚠️ 現代生產環境建議：棄用自製池，改用成熟連線池

HikariCP 快速整合範例

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:mysql://localhost/mydb");
cfg.setUsername("root"); cfg.setPassword("123");
cfg.setMaximumPoolSize(20);
DataSource ds = new HikariDataSource(cfg);

// 使用
try (Connection conn = ds.getConnection()) { ... }