Java 連線 MySQL 完整教學：JDBC 基礎、連線池與最佳實務

環境準備

1. 下載 MySQL Connector/J (mysql-connector-java-x.x.x.jar)
2. 專案加入依賴：
   • Maven：<dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.3.0</version></dependency>
   • Gradle：implementation 'com.mysql:mysql-connector-j:8.3.0'

基礎連線範例 (JDBC 4.0+ 自動載入驅動)

String url  = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Taipei";
String user = "root";
String pass = "password";

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    System.out.println("連線成功：" + conn.getMetaData().getURL());
} catch (SQLException e) {
    e.printStackTrace();
}

關鍵參數說明：

• useSSL=false：本地開發關閉 SSL 減少握手延遲
• serverTimezone：避免時區解析錯誤
• allowPublicKeyRetrieval=true：MySQL 8.0+ caching_sha2_password 需此參數

完整 CRUD 範例 (PreparedStatement 防注入)

String sql = "INSERT INTO users(username, email) VALUES(?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

    ps.setString(1, "alice");
    ps.setString(2, "alice@example.com");
    int affected = ps.executeUpdate();
    System.out.println("影響列數：" + affected);

    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) System.out.println("自增 ID：" + keys.getLong(1));
    }
}

查詢與 ResultSet 處理

String qry = "SELECT id, username, email FROM users WHERE id > ?";
try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(qry)) {
    ps.setInt(1, 0);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%d | %s | %s%n",
                rs.getLong("id"),
                rs.getString("username"),
                rs.getString("email"));
        }
    }
}

資源管理鐵律：try-with-resources

JDBC 物件 (Connection、Statement、ResultSet) 實作 AutoCloseable，務必使用 try-with-resources 自動關閉，避免洩漏導致連線耗盡。

連線池：HikariCP (生產環境標配)

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl(url);
cfg.setUsername(user);
cfg.setPassword(pass);
cfg.setMaximumPoolSize(20);
cfg.setMinimumIdle(5);
cfg.setConnectionTimeout(30_000);
cfg.setIdleTimeout(600_000);
cfg.setMaxLifetime(1_800_000);
cfg.addDataSourceProperty("cachePrepStmts", "true");
cfg.addDataSourceProperty("prepStmtCacheSize", "250");
cfg.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

DataSource ds = new HikariDataSource(cfg);
// 使用時：try (Connection conn = ds.getConnection()) { ... }

常見坑點與解決

進階技巧

• 批次處理：ps.addBatch() + ps.executeBatch()，大量寫入提升 10x+
• 游標流式讀取：stmt.setFetchSize(Integer.MIN_VALUE) 避免大結果集 OOM
• 事務控制：conn.setAutoCommit(false) → 操作 → conn.commit()/rollback()

結語

掌握 JDBC 基礎、PreparedStatement 防注入、try-with-resources 資源管理、HikariCP 連線池調優，即可構建穩健高效的 Java-MySQL 應用。