JDBC 處理 Oracle BLOB 完整指南：正確讀寫流程、常見陷阱與最佳實踐

前言：為什麼 BLOB 處理容易踩坑？

JDBC 標準 `getBlob()`/`setBlob()` 在 Oracle 實作上有已知缺陷。`getBinaryStream()`/`setBinaryStream()` 對文字檔可行，但二進位檔案易損壞。本文提供經 Oracle 官方文檔驗證、生產環境實戰的**標準正確流程**。

一、核心原理：空 BLOB 定位器 → 鎖定行 → 寫入流 → 提交

INSERT empty_blob() → SELECT ... FOR UPDATE → getBinaryOutputStream() → 寫入 → COMMIT

關鍵點：

1. 必須先 `INSERT empty_blob()` 取得 **LOB 定位器**
2. 查詢必須 `FOR UPDATE` 鎖定行
3. 關閉 Auto-commit，手動控制交易
4. 使用 `getBinaryOutputStream()` 而非 `setBinaryStream()`

二、完整寫入流程 (新增記錄)

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
OutputStream os = null;
BufferedOutputStream bos = null;
BufferedInputStream bis = null;

try {
    conn = dataSource.getConnection();
    conn.setAutoCommit(false);  // 關鍵：關閉自動提交

    // 1. 插入空 BLOB 取得定位器
    String sqlInsert = "INSERT INTO xxx_table (id, blob_col, other_col) VALUES (?, empty_blob(), ?)";
    pstmt = conn.prepareStatement(sqlInsert);
    pstmt.setLong(1, id);
    pstmt.setString(2, "other_value");
    pstmt.executeUpdate();
    pstmt.close();

    // 2. 查詢剛插入的行並鎖定
    String sqlSelect = "SELECT blob_col FROM xxx_table WHERE id = ? FOR UPDATE";
    pstmt = conn.prepareStatement(sqlSelect);
    pstmt.setLong(1, id);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        // 3. 取得 BLOB 物件與輸出流
        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("blob_col");
        os = blob.getBinaryOutputStream();  // 關鍵：Oracle 專有 API
        bos = new BufferedOutputStream(os);

        // 4. 讀取檔案並寫入
        File file = new File("c:\\data\\image.jpg");
        bis = new BufferedInputStream(new FileInputStream(file));
        byte[] buffer = new byte[8192];  // 8KB 緩衝
        int bytesRead;
        while ((bytesRead = bis.read(buffer)) != -1) {
            bos.write(buffer, 0, bytesRead);
        }
        bos.flush();
    }

    // 5. 提交交易
    conn.commit();
    
} catch (Exception e) {
    if (conn != null) try { conn.rollback(); } catch (SQLException ignored) {}
    throw new RuntimeException("BLOB 寫入失敗", e);
} finally {
    // 6. 依序關閉資源 (反序)
    closeQuietly(bis);
    closeQuietly(bos);
    closeQuietly(os);
    closeQuietly(rs);
    closeQuietly(pstmt);
    if (conn != null) {
        try { conn.setAutoCommit(true); } catch (SQLException ignored) {}
        closeQuietly(conn);
    }
}

三、更新現有記錄的 BLOB

// 1. 先更新非 BLOB 欄位 (如需)
UPDATE xxx_table SET other_col = ? WHERE id = ?;

// 2. 查詢鎖定
SELECT blob_col FROM xxx_table WHERE id = ? FOR UPDATE;

// 3. 處理 BLOB 可能為 NULL 的情況
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("blob_col");
if (blob == null || blob.length() == 0) {
    rs.close();
    pstmt.close();
    // 先寫入 empty_blob() 再重新查詢
    PreparedStatement pstmt2 = conn.prepareStatement(
        "UPDATE xxx_table SET blob_col = empty_blob() WHERE id = ?");
    pstmt2.setLong(1, id);
    pstmt2.executeUpdate();
    pstmt2.close();
    
    // 重新查詢
    pstmt = conn.prepareStatement("SELECT blob_col FROM xxx_table WHERE id = ? FOR UPDATE");
    pstmt.setLong(1, id);
    rs = pstmt.executeQuery();
    rs.next();
    blob = (oracle.sql.BLOB) rs.getBlob("blob_col");
}

// 4. 後續寫入流程同上...

四、讀取 BLOB

String sql = "SELECT blob_col FROM xxx_table WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, id);
rs = pstmt.executeQuery();

if (rs.next()) {
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("blob_col");
    InputStream is = blob.getBinaryStream();  // 讀取用 getBinaryStream
    BufferedInputStream bis = new BufferedInputStream(is);
    
    // 寫入檔案或輸出流
    FileOutputStream fos = new FileOutputStream("output.jpg");
    BufferedOutputStream bos = new BufferedOutputStream(fos);
    byte[] buffer = new byte[8192];
    int bytesRead;
    while ((bytesRead = bis.read(buffer)) != -1) {
        bos.write(buffer, 0, bytesRead);
    }
    bos.flush();
    closeQuietly(bos);
    closeQuietly(fos);
    closeQuietly(bis);
}

五、兩資料庫間 BLOB 傳輸

// 來源資料庫讀取
oracle.sql.BLOB srcBlob = (oracle.sql.BLOB) srcRs.getBlob("blob_col");
InputStream is = srcBlob.getBinaryStream();

// 目標資料庫寫入 (按上述寫入流程)
oracle.sql.BLOB destBlob = (oracle.sql.BLOB) destRs.getBlob("blob_col");
OutputStream os = destBlob.getBinaryOutputStream();

// 直接管道傳輸
byte[] buffer = new byte[32768];  // 32KB 大緩衝
int bytesRead;
while ((bytesRead = is.read(buffer)) != -1) {
    os.write(buffer, 0, bytesRead);
}
os.flush();

六、常見錯誤與解決

七、最佳實踐清單

1. 必須使用 Oracle 專有 BLOB API：`oracle.sql.BLOB`、`getBinaryOutputStream()`、`getBinaryStream()`
2. 交易邊界清晰：`setAutoCommit(false)` → 操作 → `commit()`/`rollback()` → `setAutoCommit(true)`
3. 資源關閉順序：Stream → ResultSet → Statement → Connection
4. 大檔案分塊傳輸：8KB-32KB 緩衝，避免 OOM
5. 連線池設定：`oracle.jdbc.V8Compatible=true` 相容舊版行為
6. 12c+ 可考慮 `setBlob(InputStream)`：JDBC 4.2 標準化，但測試驗證後再用

八、Oracle Thin Driver vs OCI Driver

結語

JDBC 處理 Oracle BLOB 的核心是：**空 BLOB 初始化 → FOR UPDATE 鎖定 → Oracle 專有流 API → 分塊讀寫 → 手動交易控制**。避開標準 JDBC `setBlob()`/`getBlob()` 陷阱，遵循此標準流程，能可靠處理任意大小、任意類型的二進位資料。建議封裝為 `BlobUtils` 工具類，統一專案規範。