Java JDBC 資料庫操作

JDBC (Java Database Connectivity) 是 Java 連接資料庫的標準 API，可以操作各種關聯式資料庫。

引入驅動程式

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

<!-- Maven - PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

建立連線

import java.sql.*;

String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String password = "password";

// 方法 1：DriverManager
try (Connection conn = DriverManager.getConnection(url, user, password)) {
    System.out.println("連線成功！");
}

// 方法 2：使用 Properties
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("useSSL", "false");

try (Connection conn = DriverManager.getConnection(url, props)) {
    // ...
}

查詢資料 (SELECT)

String sql = "SELECT id, name, email FROM users WHERE age > ?";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    pstmt.setInt(1, 18);  // 設定參數
    
    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            System.out.println(id + ": " + name + " - " + email);
        }
    }
}

新增資料 (INSERT)

String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    
    pstmt.setString(1, "Alice");
    pstmt.setString(2, "alice@example.com");
    pstmt.setInt(3, 25);
    
    int rows = pstmt.executeUpdate();
    System.out.println("新增 " + rows + " 筆");
    
    // 取得自動產生的 ID
    try (ResultSet rs = pstmt.getGeneratedKeys()) {
        if (rs.next()) {
            long id = rs.getLong(1);
            System.out.println("新 ID: " + id);
        }
    }
}

更新資料 (UPDATE)

String sql = "UPDATE users SET email = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    pstmt.setString(1, "newemail@example.com");
    pstmt.setInt(2, 1);
    
    int rows = pstmt.executeUpdate();
    System.out.println("更新 " + rows + " 筆");
}

刪除資料 (DELETE)

String sql = "DELETE FROM users WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    pstmt.setInt(1, 1);
    
    int rows = pstmt.executeUpdate();
    System.out.println("刪除 " + rows + " 筆");
}

交易處理

Connection conn = null;
try {
    conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);  // 關閉自動提交
    
    // 執行多個操作
    try (PreparedStatement pstmt1 = conn.prepareStatement(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
        pstmt1.setDouble(1, 100);
        pstmt1.setInt(2, 1);
        pstmt1.executeUpdate();
    }
    
    try (PreparedStatement pstmt2 = conn.prepareStatement(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        pstmt2.setDouble(1, 100);
        pstmt2.setInt(2, 2);
        pstmt2.executeUpdate();
    }
    
    conn.commit();  // 提交交易
    System.out.println("轉帳成功");
    
} catch (SQLException e) {
    if (conn != null) {
        try {
            conn.rollback();  // 回滾
            System.out.println("交易已回滾");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    e.printStackTrace();
} finally {
    if (conn != null) {
        try {
            conn.setAutoCommit(true);
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

批次處理

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    conn.setAutoCommit(false);
    
    for (int i = 0; i < 1000; i++) {
        pstmt.setString(1, "User" + i);
        pstmt.setString(2, "user" + i + "@example.com");
        pstmt.addBatch();
        
        if (i % 100 == 0) {
            pstmt.executeBatch();  // 每 100 筆執行一次
        }
    }
    
    pstmt.executeBatch();  // 執行剩餘的
    conn.commit();
}

連線池（使用 HikariCP）

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);

HikariDataSource dataSource = new HikariDataSource(config);

// 取得連線
try (Connection conn = dataSource.getConnection()) {
    // 使用連線...
}

// 關閉連線池
dataSource.close();

DAO 模式

public class UserDao {
    private final DataSource dataSource;
    
    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public Optional<User> findById(int id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapToUser(rs));
                }
            }
        }
        return Optional.empty();
    }
    
    private User mapToUser(ResultSet rs) throws SQLException {
        return new User(
            rs.getInt("id"),
            rs.getString("name"),
            rs.getString("email")
        );
    }
}

重點整理

• 使用 PreparedStatement 防止 SQL 注入
• 使用 try-with-resources 自動關閉資源
• 交易需要手動關閉 autoCommit 並處理 commit/rollback
• 批次處理提升大量資料操作效能
• 生產環境必須使用連線池
• 考慮使用 ORM 框架（如 Hibernate、MyBatis）簡化操作