Apache DBCP 連線池重構 DBUtility：高效、安全的資料庫連線管理實踐

背景動機

傳統 DriverManager.getConnection() 每次建立物理連線，開銷大、無法控制併發、易洩漏。引入 **Apache Commons DBCP (Database Connection Pool)** 實現連線池化，提升效能、資源可控、支援事務管理。

核心依賴 (Maven)

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version> <!-- 或 2.x 對應 commons-dbcp2 -->
</dependency>
<dependency>
    <groupId>commons-pool</groupId>
    <artifactId>commons-pool</artifactId>
    <version>1.6</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

配置檔 db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/your_db?useUnicode=true&characterEncoding=utf8&useSSL=false
jdbc.user=db_user
jdbc.password=db_pass
# 連線池核心參數
initialSize=5
maxActive=20
maxIdle=10
minIdle=5
maxWait=3000
# 驗證機制
validationQuery=SELECT 1
testOnBorrow=true
testWhileIdle=true
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
removeAbandoned=true
removeAbandonedTimeout=300
logAbandoned=true

重構後的 DBUtility (單例模式 + ThreadLocal 事務支援)

package com.example.util;

import org.apache.commons.dbcp.BasicDataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import java.lang.ThreadLocal;

public class DBUtil {
    // 連線池單例
    private static BasicDataSource dataSource;
    // 線程綁定連線，支援同一線程多 DAO 共用事務
    private static ThreadLocal threadLocal = new ThreadLocal<>();

    static {
        try {
            Properties props = new Properties();
            InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
            props.load(is);
            is.close();

            dataSource = new BasicDataSource();
            dataSource.setDriverClassName(props.getProperty("jdbc.driver"));
            dataSource.setUrl(props.getProperty("jdbc.url"));
            dataSource.setUsername(props.getProperty("jdbc.user"));
            dataSource.setPassword(props.getProperty("jdbc.password"));

            // 池大小控制
            dataSource.setInitialSize(Integer.parseInt(props.getProperty("initialSize")));
            dataSource.setMaxActive(Integer.parseInt(props.getProperty("maxActive")));
            dataSource.setMaxIdle(Integer.parseInt(props.getProperty("maxIdle")));
            dataSource.setMinIdle(Integer.parseInt(props.getProperty("minIdle")));
            dataSource.setMaxWait(Long.parseLong(props.getProperty("maxWait")));

            // 連線驗證 (關鍵：防止資料庫斷開導致池中連線失效)
            dataSource.setValidationQuery(props.getProperty("validationQuery"));
            dataSource.setTestOnBorrow(Boolean.parseBoolean(props.getProperty("testOnBorrow")));
            dataSource.setTestWhileIdle(Boolean.parseBoolean(props.getProperty("testWhileIdle")));
            dataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(props.getProperty("timeBetweenEvictionRunsMillis")));
            dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(props.getProperty("minEvictableIdleTimeMillis")));

            // 洩漏檢測
            dataSource.setRemoveAbandoned(Boolean.parseBoolean(props.getProperty("removeAbandoned")));
            dataSource.setRemoveAbandonedTimeout(Integer.parseInt(props.getProperty("removeAbandonedTimeout")));
            dataSource.setLogAbandoned(Boolean.parseBoolean(props.getProperty("logAbandoned")));

        } catch (Exception e) {
            throw new ExceptionInInitializerError("DBUtil 初始化失敗: " + e.getMessage());
        }
    }

    /** 獲取連線 (若線程已綁定則復用，支援事務) */
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn == null || conn.isClosed()) {
            conn = dataSource.getConnection();
            threadLocal.set(conn);
        }
        return conn;
    }

    /** 釋放連線 (歸還池中，非關閉物理連線) */
    public static void closeConnection() {
        Connection conn = threadLocal.get();
        threadLocal.remove();
        if (conn != null) {
            try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }

    /** 事務控制輔助 */
    public static void beginTransaction() throws SQLException {
        Connection conn = getConnection();
        conn.setAutoCommit(false);
    }
    public static void commit() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn != null) conn.commit();
    }
    public static void rollback() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn != null) conn.rollback();
    }

    /** 關閉連線池 (應用關閉時調用) */
    public static void shutdown() {
        try { dataSource.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

DAO 層使用範例

public class UserDao {
    public void insert(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 單次操作自動釋放 (非事務場景)
            // 事務場景由 Service 層統一 commit/rollback 後調用 closeConnection()
            DBUtil.closeConnection();
        }
    }

    // 事務範例：Service 層控制
    public void transfer(Account from, Account to, double amount) {
        Connection conn = null;
        try {
            DBUtil.beginTransaction();
            conn = DBUtil.getConnection();
            // 扣款
            updateBalance(conn, from.getId(), -amount);
            // 入款
            updateBalance(conn, to.getId(), amount);
            DBUtil.commit();
        } catch (Exception e) {
            DBUtil.rollback();
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeConnection();
        }
    }
    private void updateBalance(Connection conn, long id, double delta) throws SQLException {
        String sql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, delta);
            ps.setLong(2, id);
            ps.executeUpdate();
        }
    }
}

關鍵參數調優建議

遷移至 DBCP 2 / HikariCP 建議

• Commons DBCP 1.x 已停維護**，建議新專案直接使用 **HikariCP** (Spring Boot 2+ 默認) 或 **DBCP 2** (package org.apache.commons.dbcp2)。
• HikariCP 配置更簡單、效能更優、預設值更合理：

# application.yml (Spring Boot)
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8mb4&useSSL=false&serverTimezone=Asia/Shanghai
    username: user
    password: pass
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 3000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1