JDBC 連接 MySQL 完整範例：DriverManager、Connection、PreparedStatement

JDBC 連接 MySQL 完整範例：DriverManager、Connection、PreparedStatement

Java 透過 JDBC API 連接 MySQL 的標準範例，涵蓋驅動載入、連線建立、參數化查詢、資源關閉與 Maven 依賴管理。

Maven 依賴 (MySQL Connector/J 8.0+)

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

舊版 (5.1.x)：`mysql:mysql-connector-java:5.1.49`，驅動類 `com.mysql.jdbc.Driver`

完整程式碼範例

import java.sql.*;

public class MySQLJdbcDemo {
    // 連線配置 (建議外部化至配置檔)
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "0000";

    public static void main(String[] args) {
        // 1. 載入驅動 (JDBC 4.0+ SPI 自動載入，可省略)
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("MySQL 驅動未找到", e);
        }

        // 2. 建立連線 (try-with-resources 自動關閉)
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            System.out.println("資料庫連線成功！");
            
            // 3. 參數化插入 (防止 SQL Injection)
            String insertSql = "INSERT INTO stu (id, name) VALUES (?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                ps.setInt(1, 12);
                ps.setString(2, "Shara");
                int rows = ps.executeUpdate();
                System.out.println("插入行數: " + rows);
            }

            // 4. 查詢示範
            String querySql = "SELECT id, name FROM stu WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(querySql)) {
                ps.setInt(1, 12);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.printf("ID: %d, Name: %s%n", rs.getInt("id"), rs.getString("name"));
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println("資料庫操作失敗: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

關鍵最佳實務

連線池配置範例

# application.properties (Spring Boot)
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Taipei
spring.datasource.username=root
spring.datasource.password=0000
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.connection-timeout=20000

常見錯誤排查

驅動類對照