JDBC 核心六步驟
- 載入驅動：
Class.forName("com.mysql.cj.jdbc.Driver") (JDBC 4.0+ 可省略，自動發現)
- 建立連線：
DriverManager.getConnection(url, user, pass)
- 建立 Statement：
conn.createStatement() 或 prepareStatement(sql)
- 執行 SQL：
executeQuery() (查詢) / executeUpdate() (增刪改)
- 處理結果集：
ResultSet.next() 迴圈讀取欄位
- 釋放資源：關閉
ResultSet、Statement、Connection（反向順序）
MySQL 完整實戰範例
import java.sql.*;
public class JdbcMySqlDemo {
// 資料庫配置常數
private static final String URL = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8";
private static final String USER = "root";
private static final String PASS = "password";
public static void main(String[] args) {
// 1. 建立連線 (try-with-resources 自動關閉)
try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
System.out.println("連線成功：" + conn.getMetaData().getURL());
// 2. 建立表格 (DDL)
createTable(conn);
// 3. 新增資料 (PreparedStatement 防注入)
insertUser(conn, "Alice", "alice@example.com", 25);
insertUser(conn, "Bob", "bob@example.com", 30);
// 4. 查詢資料
queryUsers(conn);
// 5. 更新資料
updateUserAge(conn, "Alice", 26);
// 6. 刪除資料
deleteUser(conn, "Bob");
// 7. 事務控制範例
transactionDemo(conn);
} catch (SQLException e) {
printSQLException(e);
}
}
private static void createTable(Connection conn) throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS users (" +
"id INT AUTO_INCREMENT PRIMARY KEY, " +
"name VARCHAR(50) NOT NULL, " +
"email VARCHAR(100) UNIQUE, " +
"age INT, " +
"created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
System.out.println("表格建立/確認完成");
}
}
private static void insertUser(Connection conn, String name, String email, int age) throws SQLException {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, name);
ps.setString(2, email);
ps.setInt(3, age);
int rows = ps.executeUpdate();
System.out.printf("新增 %d 筆: %s%n", rows, name);
// 取得自增主鍵
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) {
System.out.println("生成 ID: " + keys.getLong(1));
}
}
}
}
private static void queryUsers(Connection conn) throws SQLException {
String sql = "SELECT id, name, email, age, created_at FROM users ORDER BY id";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
System.out.println("\n=== 用戶列表 ===");
while (rs.next()) {
System.out.printf("ID:%d | Name:%-10s | Email:%-20s | Age:%d | Created:%s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age"),
rs.getTimestamp("created_at"));
}
}
}
private static void updateUserAge(Connection conn, String name, int newAge) throws SQLException {
String sql = "UPDATE users SET age = ? WHERE name = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, newAge);
ps.setString(2, name);
int rows = ps.executeUpdate();
System.out.printf("更新 %s 年齡 -> %d (%d 筆受影響)%n", name, newAge, rows);
}
}
private static void deleteUser(Connection conn, String name) throws SQLException {
String sql = "DELETE FROM users WHERE name = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, name);
int rows = ps.executeUpdate();
System.out.printf("刪除 %s (%d 筆受影響)%n", name, rows);
}
}
private static void transactionDemo(Connection conn) throws SQLException {
System.out.println("\n=== 事務演示：轉帳 ===");
// 關閉自動提交
conn.setAutoCommit(false);
try {
// 扣款
updateBalance(conn, "Alice", -100);
// 模擬錯誤：收款方不存在
updateBalance(conn, "NonExist", 100); // 會拋出異常或 0 筆影響
// 若無異常則提交
conn.commit();
System.out.println("事務提交成功");
} catch (SQLException e) {
conn.rollback();
System.out.println("事務回滾：" + e.getMessage());
} finally {
conn.setAutoCommit(true); // 恢復預設
}
}
private static void updateBalance(Connection conn, String name, int delta) throws SQLException {
String sql = "UPDATE users SET age = age + ? WHERE name = ?"; // 簡化用 age 當餘額
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, delta);
ps.setString(2, name);
int rows = ps.executeUpdate();
if (rows == 0) throw new SQLException("用戶不存在: " + name);
System.out.printf(" %s 餘額變更 %+d%n", name, delta);
}
}
private static void printSQLException(SQLException e) {
System.err.println("SQL 錯誤: " + e.getMessage());
System.err.println("SQLState: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
e.printStackTrace();
}
}
關鍵最佳實踐總結
| 實踐 | 說明 |
|---|
| PreparedStatement | 防 SQL 注入、預編譯效能、支援參數綁定 |
| try-with-resources | Java 7+ 自動關閉 Connection/Statement/ResultSet，避免洩漏 |
| 連線池 | 生產必須：HikariCP、Druid、DBCP2，避免頻繁建立連線開銷 |
| 關閉自動提交 | 多條 SQL 組成事務時 conn.setAutoCommit(false) |
| 批次處理 | 大量寫入用 ps.addBatch() + executeBatch() |
| ResultSet 類型 | 預設 TYPE_FORWARD_ONLY，需雙向游標用 TYPE_SCROLL_INSENSITIVE |
| 異常處理 | 捕獲 SQLException，記錄 SQLState、ErrorCode、完整堆疊 |
連線池整合範例
// HikariCP 單例
public class DataSourceUtil {
private static final HikariDataSource ds;
static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?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);
ds = new HikariDataSource(config);
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
public static void close() { ds.close(); }
}
// 使用
try (Connection conn = DataSourceUtil.getConnection()) {
// 業務邏輯
}
常見錯誤排查
| 錯誤 | 原因 | 解決 |
|---|
| Communications link failure | 網路不通、MySQL 未啟動、防火牆、bind-address | 檢查 3306 埠、MySQL 服務、my.cnf bind-address=0.0.0.0 |
| Public Key Retrieval is not allowed | MySQL 8+ 預設 caching_sha2_password | URL 加 allowPublicKeyRetrieval=true 或改用 mysql_native_password |
| No suitable driver | 驅動 JAR 缺失、類路徑錯誤 | Maven 引入 mysql-connector-java (8.0+) |
| ResultSet closed | Statement 關閉導致 RS 失效 | 先處理完 RS 再關閉 Statement，或用 try-with-resources 正確巢狀 |
總結
JDBC 是 Java 資料庫存取的基石。掌握 六步驟流程、PreparedStatement 防注入、try-with-resources 資源管理、事務控制、連線池整合 五大核心技能，即可構建健壯的資料存取層。現代專案建議進一步採用 Spring JDBC Template、MyBatis 或 JPA/Hibernate 等 ORM 框架提升開發效率。
