Java JDBC 連接 MySQL 完整教學：從驅動載入到連線池最佳實踐

基礎 JDBC 連線步驟

1. 載入驅動類別：Class.forName("com.mysql.cj.jdbc.Driver") (MySQL 8.0+)
2. 建立連線：DriverManager.getConnection(url, user, pass)
3. 建立 Statement/PreparedStatement
4. 執行 SQL：executeQuery/executeUpdate
5. 處理 ResultSet
6. 關鍵：finally 區塊關閉資源（連線、Statement、ResultSet）

完整基礎範例

import java.sql.*;

public class JdbcBasicDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 1. 載入驅動 (JDBC 4.0+ 可省略，但建議保留)
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 取得連線
            conn = DriverManager.getConnection(URL, USER, PASS);
            System.out.println("連線成功!");

            // 3. 準備參數化查詢 (防止 SQL 注入)
            String sql = "SELECT id, name, email FROM users WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);

            // 4. 執行查詢
            rs = pstmt.executeQuery();

            // 5. 處理結果
            while (rs.next()) {
                System.out.printf("ID: %d, Name: %s, Email: %s%n",
                    rs.getInt("id"), rs.getString("name"), rs.getString("email"));
            }

        } catch (ClassNotFoundException e) {
            System.err.println("找不到驅動類別: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("資料庫錯誤: " + e.getMessage());
        } finally {
            // 6. 反序關閉資源 (Java 7+ 建議使用 try-with-resources)
            closeQuietly(rs);
            closeQuietly(pstmt);
            closeQuietly(conn);
        }
    }

    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try { resource.close(); } catch (Exception ignored) {}
        }
    }
}

進階：連線池 - HikariCP (效能最佳)

生產環境必須使用連線池，避免頻繁建立/關閉連線開銷。

Maven 依賴

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

連線池配置與使用

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.*;

public class JdbcPoolDemo {
    private static DataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        dataSource = new HikariDataSource(config);
    }

    public static void queryUsers() {
        String sql = "SELECT * FROM users WHERE status = ?";
        // try-with-resources 自動關閉連線回池
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "active");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    // 處理資料...
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void shutdown() {
        if (dataSource instanceof HikariDataSource) {
            ((HikariDataSource) dataSource).close();
        }
    }
}

多表操作與事務控制

public void transferMoney(int fromId, int toId, double amount) {
    String sql1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
    String sql2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
    
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);  // 開啟事務
        try (PreparedStatement pstmt1 = conn.prepareStatement(sql1);
             PreparedStatement pstmt2 = conn.prepareStatement(sql2)) {
            
            pstmt1.setDouble(1, amount); pstmt1.setInt(2, fromId);
            pstmt2.setDouble(1, amount); pstmt2.setInt(2, toId);
            
            pstmt1.executeUpdate();
            pstmt2.executeUpdate();
            
            conn.commit();  // 提交事務
        } catch (SQLException e) {
            conn.rollback();  // 回滾事務
            throw new RuntimeException("轉帳失敗", e);
        }
    } catch (SQLException e) {
        throw new RuntimeException("資料庫連線失敗", e);
    }
}

最佳實踐總結

總結

JDBC 核心流程固定，關鍵在於資源正確釋放與參數化查詢。現代專案應直接採用 HikariCP 連線池搭配 try-with-resources，配合事務控制實現可靠的多表操作。避免手動管理連線生命週期，專注於業務 SQL 撰寫與優化。