Java 連接 MySQL 數據庫完整步驟：從驅動安裝到 CRUD 代碼實戰

環境準備

步驟 1：添加驅動依賴

Maven (pom.xml) - 推薦

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version> <!-- 查詢中央倉庫最新版 -->
</dependency>

Gradle (build.gradle)

implementation 'com.mysql:mysql-connector-j:8.3.0'

手動導入 JAR (無構建工具)

1. 下載 mysql-connector-j-8.x.x.jar。
2. Eclipse: 右鍵項目 → Build Path → Configure Build Path → Libraries → Add External JARs 選中 JAR → Apply and Close。
3. IDEA: File → Project Structure → Modules → Dependencies → + → JARs or directories 選中 JAR → OK。

步驟 2：數據庫準備

CREATE DATABASE IF NOT EXISTS myschool CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE myschool;

CREATE TABLE admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    pwds VARCHAR(100) NOT NULL
);

INSERT INTO admin (name, pwds) VALUES ('admin', '123456'), ('test', 'abcdef');

步驟 3：JDBC 核心代碼模板

import java.sql.*;

public class JdbcDemo {
    // 數據庫連接配置 (生產環境請外部化到配置文件/環境變量)
    private static final String URL = "jdbc:mysql://localhost:3306/myschool?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    public static void main(String[] args) {
        // 1. 加載驅動 (JDBC 4.0+ 可省略，驅動自動註冊)
        // Class.forName("com.mysql.cj.jdbc.Driver");

        // 2. 建立連接
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("連接成功: " + conn.getMetaData().getURL());

            // 3. 創建 Statement (簡單查詢) 或 PreparedStatement (參數化查詢/增刪改)
            String sql = "SELECT id, name, pwds FROM admin";

            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {

                // 4. 處理結果集
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    String pwd = rs.getString("pwds");
                    System.out.printf("ID: %d, 用戶: %s, 密碼: %s%n", id, name, pwd);
                }
            }

            // 5. 增刪改示例 (使用 PreparedStatement 防注入)
            String insertSql = "INSERT INTO admin(name, pwds) VALUES(?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, "new_user");
                ps.setString(2, "secure_pwd");
                int affected = ps.executeUpdate();
                System.out.println("插入行數: " + affected);

                // 獲取自增主鍵
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    if (keys.next()) {
                        System.out.println("新記錄 ID: " + keys.getLong(1));
                    }
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

關鍵配置參數解析 (URL 參數)

生產環境完整 URL 示例：

jdbc:mysql://db-host:3306/myschool?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048

進階最佳實踐

1. 連接池 (HikariCP) - 生產必備

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

HikariConfig config = new HikariConfig();
config.setJdbcUrl(URL);
config.setUsername(USER);
config.setPassword(PASSWORD);
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("useServerPrepStmts", "true");
config.addDataSourceProperty("rewriteBatchedStatements", "true");

DataSource dataSource = new HikariDataSource(config);
// 使用: try (Connection conn = dataSource.getConnection()) { ... }

2. 事務控制模板

public void transfer(Connection conn, long fromId, long toId, double amount) throws SQLException {
    boolean originalAutoCommit = conn.getAutoCommit();
    try {
        conn.setAutoCommit(false); // 開啟事務

        // 扣款
        try (PreparedStatement ps = conn.prepareStatement("UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?")) {
            ps.setDouble(1, amount);
            ps.setLong(2, fromId);
            ps.setDouble(3, amount);
            int rows = ps.executeUpdate();
            if (rows == 0) throw new RuntimeException("餘額不足或賬戶不存在");
        }

        // 入款
        try (PreparedStatement ps = conn.prepareStatement("UPDATE account SET balance = balance + ? WHERE id = ?")) {
            ps.setDouble(1, amount);
            ps.setLong(2, toId);
            ps.executeUpdate();
        }

        conn.commit(); // 提交事務
    } catch (Exception e) {
        conn.rollback(); // 回滾
        throw e;
    } finally {
        conn.setAutoCommit(originalAutoCommit);
    }
}

3. 批量插入優化

String sql = "INSERT INTO log(event, create_time) VALUES(?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (int i = 0; i < 10000; i++) {
        ps.setString(1, "event_" + i);
        ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
        ps.addBatch();
        if (i % 1000 == 0) ps.executeBatch(); // 分批提交防 OOM
    }
    ps.executeBatch(); // 剩餘
}

關鍵：URL 必須包含 rewriteBatchedStatements=true，驅動會將多條 INSERT 重寫為單條 INSERT ... VALUES (...), (...)，性能提升 10-100 倍。

常見錯誤速查表

總結

1. Maven/Gradle 引入 mysql-connector-j。
2. URL 參數齊全：useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true。
3. 業務代碼統一用 PreparedStatement + try-with-resources。
4. 生產環境必上 HikariCP 連接池 + 事務模板 + 批量優化參數。
5. 配置外部化：application.yml / .env / 配置中心，不硬編碼密碼。