Java 操作 MySQL 實戰指南：PreparedStatement 防注入、事務控制、連接池與結果集流式處理

JDBC 是 Java 操作關係型資料庫的基石。本文聚焦 MySQL 實戰，涵蓋 PreparedStatement 防 SQL 注入、事務邊界控制、HikariCP 連接池調優、大結果集流式讀取、批次寫入優化與常見異常處理模式。

核心 API 分類

標準查詢模板 (try-with-resources)

public User findById(Long id) {
    String sql = "SELECT id, username, email, created_at FROM users WHERE id = ?";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setLong(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return mapRow(rs);
            }
        }
    } catch (SQLException e) {
        throw new DataAccessException("QUERY_FAILED", e);
    }
    return null;
}

private User mapRow(ResultSet rs) throws SQLException {
    return new User(rs.getLong("id"), rs.getString("username"),
                    rs.getString("email"), rs.getTimestamp("created_at").toLocalDateTime());
}

事務控制：手動提交 vs Spring @Transactional

// 手動事務 (無框架時)
public void transfer(Long from, Long to, BigDecimal amt) {
    Connection conn = null;
    try {
        conn = dataSource.getConnection();
        conn.setAutoCommit(false); // 開啟事務

        updateBalance(conn, from, amt.negate());
        updateBalance(conn, to, amt);
        insertLog(conn, from, to, amt);

        conn.commit();
    } catch (SQLException e) {
        if (conn != null) conn.rollback();
        throw new BizException("TRANSFER_FAILED", e);
    } finally {
        if (conn != null) { conn.setAutoCommit(true); closeQuietly(conn); }
    }
}

private void updateBalance(Connection conn, Long id, BigDecimal delta) throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        ps.setBigDecimal(1, delta);
        ps.setLong(2, id);
        ps.executeUpdate();
    }
}

**生產建議**：使用 Spring @Transactional 聲明式事務，配合 rollbackFor = Exception.class、isolation = READ_COMMITTED、timeout 參數。

HikariCP 連接池 MySQL 專項調優

spring.datasource.hikari:
  jdbc-url: jdbc:mysql://host:3306/db?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
  username: user
  password: pass
  maximum-pool-size: 20
  minimum-idle: 5
  connection-timeout: 30000
  idle-timeout: 600000
  max-lifetime: 1800000
  connection-test-query: SELECT 1
  data-source-properties:
    cachePrepStmts: true           # 緩存 PreparedStatement
    prepStmtCacheSize: 250
    prepStmtCacheSqlLimit: 2048
    useServerPrepStmts: true       # 啟用服務端預編譯
    rewriteBatchedStatements: true # 關鍵：批次 INSERT 重寫為多值 INSERT，效能提升 10x+
    maintainTimeStats: false       # 關閉統計減少開銷

大結果集流式處理 (避免 OOM)

// 預設 ResultSet 會將所有行載入記憶體，大表必須啟用流式讀取
String sql = "SELECT * FROM huge_log_table WHERE create_time > ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql,
             ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {

    ps.setFetchSize(Integer.MIN_VALUE); // MySQL 魔法值：啟用流式讀取
    ps.setTimestamp(1, Timestamp.valueOf(startTime));

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            // 逐行處理，游標移動時才從網路讀取
            processRow(rs);
        }
    }
}

⚠️ 流式讀取期間**不能執行其他查詢**，同一連接同一時間只能有一個開啟的流式 ResultSet。

批次寫入優化

public void batchInsert(List users) {
    String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        conn.setAutoCommit(false);
        for (int i = 0; i < users.size(); i++) {
            User u = users.get(i);
            ps.setString(1, u.getUsername());
            ps.setString(2, u.getEmail());
            ps.addBatch();

            if (i % 1000 == 0) { // 分批提交，避免巨大事務
                ps.executeBatch();
                conn.commit();
            }
        }
        ps.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        throw new DataAccessException("BATCH_INSERT_FAILED", e);
    }
}

配合 rewriteBatchedStatements=true，MySQL 驅動會將 1000 條 INSERT ... VALUES (?,?) 重寫為單條 INSERT ... VALUES (?,?), (?,?), ...，網路往返減少 99%。

常見異常與排查

結語

JDBC 操作 MySQL 的核心是：**PreparedStatement 防注入、連接池調優防斷連、流式讀取防 OOM、批次重寫提吞吐**。掌握這四大支柱，再配合 Spring Data JPA / MyBatis 等 ORM 框架的最佳實踐，即可構建穩健高效的資料存取層。