Java 數據庫連接池配置全攻略

Java 數據庫連接池主流配置方式完整指南

連接池核心參數

方式一：Tomcat 容器級數據源

1. context.xml (應用級)

<Context>
    <Resource name="jdbc/myapp" 
              auth="Container" 
              type="javax.sql.DataSource"
              maxActive="50" maxIdle="20" maxWait="10000"
              username="root" password="secret"
              driverClassName="com.mysql.cj.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC"/>
</Context>

2. server.xml (全局)

<Context path="/myapp" docBase="...">
    <Resource name="jdbc/myapp" ... /> <!-- 同上 -->
</Context>

3. 代碼查找

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:/comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/myapp");
Connection conn = ds.getConnection();

注意：必須在 Tomcat 容器中運行，需將驅動 jar 放入 $CATALINA_HOME/lib。

方式二：Hibernate 內建/整合連接池

1. Hibernate 內建 (僅開發測試)

<property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/db</property>
<property name="connection.username">root</property>
<property name="connection.password">pass</property>

2. 整合 DBCP2

<property name="hibernate.dbcp.maxTotal">50</property>
<property name="hibernate.dbcp.maxIdle">20</property>
<property name="hibernate.dbcp.minIdle">5</property>
<property name="hibernate.dbcp.maxWaitMillis">10000</property>

3. 整合 C3P0

<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">100</property>

4. 整合 Proxool (需獨立 XML)

<property name="hibernate.proxool.pool_alias">myPool</property>
<property name="hibernate.proxool.xml">proxool.xml</property>
<property name="connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</property>

5. 使用容器數據源

<property name="hibernate.connection.datasource">java:/comp/env/jdbc/myapp</property>

方式三：Spring 配置連接池

1. DBCP2 (Spring XML)

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/db"/>
    <property name="username" value="root"/>
    <property name="password" value="pass"/>
    <property name="maxTotal" value="50"/>
    <property name="maxIdle" value="20"/>
    <property name="minIdle" value="5"/>
    <property name="maxWaitMillis" value="10000"/>
    <property name="testWhileIdle" value="true"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="removeAbandonedOnBorrow" value="true"/>
    <property name="removeAbandonedTimeout" value="60"/>
</bean>

2. HikariCP (推薦生產環境)

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/db"/>
    <property name="username" value="root"/>
    <property name="password" value="pass"/>
    <property name="maximumPoolSize" value="50"/>
    <property name="minimumIdle" value="10"/>
    <property name="connectionTimeout" value="5000"/>
    <property name="idleTimeout" value="600000"/>
    <property name="maxLifetime" value="1800000"/>
    <property name="leakDetectionThreshold" value="30000"/>
</bean>

方式四：代碼編程式配置

public class DataSourceConfig {
    private static BasicDataSource dataSource;

    public static DataSource getDataSource() {
        if (dataSource == null) {
            dataSource = new BasicDataSource();
            dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://localhost:3306/db");
            dataSource.setUsername("root");
            dataSource.setPassword("pass");
            dataSource.setInitialSize(10);
            dataSource.setMaxTotal(50);
            dataSource.setMaxIdle(20);
            dataSource.setMinIdle(5);
            dataSource.setMaxWaitMillis(10000);
            dataSource.setTestWhileIdle(true);
            dataSource.setValidationQuery("SELECT 1");
        }
        return dataSource;
    }
}

選型建議

避坑指南

• 驅動類名：MySQL 8.x 用 com.mysql.cj.jdbc.Driver，5.x 用 com.mysql.jdbc.Driver
• URL 參數：useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
• 連接洩漏：務必 try-with-resources 或 finally 關閉 Connection
• 監控指標：啟用泄漏檢測、慢查詢日誌、連接池狀態導出