Java 連接 MySQL 完整實戰：JDBC 驅動加載、連接池、CRUD 與 PreparedStatement 防注入

依賴準備

Maven 坐標（以 8.0 驅動為例，兼容 5.6+）：

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.0.33</version>
</dependency>

舊版驅動類名：com.mysql.jdbc.Driver（5.x）
新版驅動類名：com.mysql.cj.jdbc.Driver（8.x）

核心步驟三部曲

1. 加載驅動：Class.forName("com.mysql.cj.jdbc.Driver")（JDBC 4.0+ 可省略，自動 SPI 加載）。
2. 建立連接：DriverManager.getConnection(url, user, pass)。
3. 執行與結果處理：Statement / PreparedStatement / CallableStatement。

完整範例代碼（含資源關閉最佳實踐）

import java.sql.*;
import java.util.Properties;

public class MySQLDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/mytest?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "123456";

    public static void main(String[] args) {
        // 1. 普通 Statement 查詢
        queryWithStatement();
        
        // 2. PreparedStatement 參數化查詢（防 SQL 注入）
        queryWithPreparedStatement("a");
        
        // 3. 事務控制示例
        transactionDemo();
    }

    static void queryWithStatement() {
        String sql = "SELECT name, major, score FROM student";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("=== Statement 查詢 ===");
            while (rs.next()) {
                System.out.printf("%s\t%s\t%d%n", 
                    rs.getString("name"), rs.getString("major"), rs.getInt("score"));
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void queryWithPreparedStatement(String name) {
        String sql = "SELECT name, major, score FROM student WHERE name = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, name);  // 參數綁定，自動轉義
            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("=== PreparedStatement 查詢 ===");
                while (rs.next()) {
                    System.out.printf("%s\t%s\t%d%n", 
                        rs.getString(1), rs.getString(2), rs.getInt(3));
                }
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void transactionDemo() {
        String sql1 = "UPDATE account SET balance = balance - 100 WHERE id = 1";
        String sql2 = "UPDATE account SET balance = balance + 100 WHERE id = 2";
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false);  // 開啟事務
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(sql1);
                stmt.executeUpdate(sql2);
                conn.commit();  // 提交
                System.out.println("轉賬成功");
            } catch (SQLException e) {
                conn.rollback();  // 回滾
                System.err.println("轉賬失敗，已回滾: " + e.getMessage());
            }
        } catch (SQLException e) { e.printStackTrace(); }
    }
}

關鍵最佳實踐

生產級連接池：HikariCP

// Maven
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>5.0.1</version>
</dependency>

// 配置
HikariConfig config = new HikariConfig();
config.setJdbcUrl(URL);
config.setUsername(USER);
config.setPassword(PASS);
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 ds = new HikariDataSource(config);
// 使用 ds.getConnection() 替代 DriverManager

常見坑位排查

• 時區報錯：URL 加 serverTimezone=Asia/Shanghai。
• SSL 報警：useSSL=false（內網）或配置憑證。
• 公共鍵獲取失敗：8.0+ 需 allowPublicKeyRetrieval=true（非 SSL 時）。
• 字符集亂碼：characterEncoding=utf8 + 表/庫 charset=utf8mb4。