MySQL 數據庫連接池配置完整教程 (Tomcat + JNDI + DataSource)

架構圖解

Java 應用 → JNDI 查找 DataSource → 連接池 → MySQL

步驟一：編寫 JavaBean 封裝連接池操作

package withouttears.jdbc.db;

import javax.naming.*;
import javax.sql.DataSource;
import java.sql.*;
import sun.jdbc.rowset.CachedRowSet;
import java.util.HashMap;

public class Database {
    private static DataSource getDataSource() {
        // 簡單緩存 DataSource 實例
        HashMap cache = new HashMap<>();
        if (cache.containsKey("ds")) return (DataSource)cache.get("ds");
        
        try {
            Context initCtx = new InitialContext();
            DataSource ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/testdb");
            cache.put("ds", ds);
            return ds;
        } catch (Exception e) { e.printStackTrace(); }
        return null;
    }

    private static Connection getConnection() throws SQLException {
        return getDataSource().getConnection();
    }

    public static CachedRowSet executeQuery(String sql) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            CachedRowSet crs = new CachedRowSet();
            crs.populate(rs);
            return crs;
        } catch (Exception e) { e.printStackTrace(); }
        return null;
    }

    public static boolean executeUpdate(String sql) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            return stmt.executeUpdate(sql) > 0;
        } catch (Exception e) { e.printStackTrace(); }
        return false;
    }
}

步驟二：Tomcat 配置

1. 虛擬主機配置 (C:/Program Files/Tomcat 5.5/conf/Catalina/localhost/test.xml)

<Context docBase="E:/MyWorkSpace/test" path="/test"></Context>

2. 全局資源配置 (conf/context.xml)

<Context>
  <Resource name="jdbc/testdb"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/mytestdb"
    username="root"
    password="157744375"
    maxActive="100"
    maxIdle="30"
    maxWait="10000"
  />
</Context>

3. Web 應用部署描述符 (WEB-INF/web.xml)

<web-app>
  <welcome-file-list>
    <welcome-file>test.jsp</welcome-file>
  </welcome-file-list>
</web-app>

步驟三：JSP 測試頁面

<%@ page import="withouttears.jdbc.db.*, java.sql.*" %>
<%
  String sql = "SELECT * FROM table_test";
  ResultSet rs = Database.executeQuery(sql);
  while (rs.next()) {
%>
  姓名：<%=rs.getString("name")%><br>
  電話：<%=rs.getString("mobile")%><br>
<%
  }
%>

關鍵連接池參數解釋

現代化建議

• 生產環境推薦 HikariCP (Spring Boot 默認)，性能優於 DBCP/c3p0
• 使用 @ConfigurationProperties 外部化配置
• 監控連接池指標：active/idle/usage/await 時間