Oracle 大型物件 (LOB) 分為 CLOB (字元大物件) 與 BLOB (二進位大物件)。JDBC 操作 LOB 核心流程：**關閉自動提交 → 插入空 LOB (EMPTY_CLOB/BLOB) → SELECT ... FOR UPDATE 鎖定 → 取得 LOB 定位器 → 透過輸出流寫入/輸入流讀取 → 提交事務**。本文提供完整、可直接運行的工具類，涵蓋插入、修改、替換、讀取四大場景。
import java.io.*;
import java.sql.*;
import oracle.sql.CLOB;
import oracle.sql.BLOB;
public class LobPros {
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String USER = "scott";
private static final String PWD = "tiger";
private static Connection conn;
private static Statement stmt;
// ===== 通用事務模板 =====
private static void executeInTransaction(TransactionCallback callback) throws Exception {
boolean oldAuto = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
callback.doInTransaction();
conn.commit();
} catch (Exception e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(oldAuto);
}
}
@FunctionalInterface
interface TransactionCallback { void doInTransaction() throws Exception; }
// ===== CLOB 操作 =====
public static void clobInsert(String inFile) throws Exception {
executeInTransaction(() -> {
stmt.executeUpdate("INSERT INTO test_clob (id, content) VALUES ('111', EMPTY_CLOB())");
ResultSet rs = stmt.executeQuery("SELECT content FROM test_clob WHERE id='111' FOR UPDATE");
if (rs.next()) {
CLOB clob = (CLOB) rs.getClob("content");
try (BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
public static void clobModify(String inFile) throws Exception {
executeInTransaction(() -> {
ResultSet rs = stmt.executeQuery("SELECT content FROM test_clob WHERE id='111' FOR UPDATE");
if (rs.next()) {
CLOB clob = (CLOB) rs.getClob("content");
try (BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c); // 覆蓋寫入
}
}
});
}
public static void clobReplace(String inFile) throws Exception {
executeInTransaction(() -> {
stmt.executeUpdate("UPDATE test_clob SET content = EMPTY_CLOB() WHERE id='111'");
ResultSet rs = stmt.executeQuery("SELECT content FROM test_clob WHERE id='111' FOR UPDATE");
if (rs.next()) {
CLOB clob = (CLOB) rs.getClob("content");
try (BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
public static void clobRead(String outFile) throws Exception {
executeInTransaction(() -> {
ResultSet rs = stmt.executeQuery("SELECT content FROM test_clob WHERE id='111'");
if (rs.next()) {
CLOB clob = (CLOB) rs.getClob("content");
try (BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
// ===== BLOB 操作 =====
public static void blobInsert(String inFile) throws Exception {
executeInTransaction(() -> {
stmt.executeUpdate("INSERT INTO test_blob (id, data) VALUES ('222', EMPTY_BLOB())");
ResultSet rs = stmt.executeQuery("SELECT data FROM test_blob WHERE id='222' FOR UPDATE");
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("data");
try (BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
public static void blobModify(String inFile) throws Exception {
executeInTransaction(() -> {
ResultSet rs = stmt.executeQuery("SELECT data FROM test_blob WHERE id='222' FOR UPDATE");
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("data");
try (BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
public static void blobReplace(String inFile) throws Exception {
executeInTransaction(() -> {
stmt.executeUpdate("UPDATE test_blob SET data = EMPTY_BLOB() WHERE id='222'");
ResultSet rs = stmt.executeQuery("SELECT data FROM test_blob WHERE id='222' FOR UPDATE");
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("data");
try (BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(inFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
public static void blobRead(String outFile) throws Exception {
executeInTransaction(() -> {
ResultSet rs = stmt.executeQuery("SELECT data FROM test_blob WHERE id='222'");
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("data");
try (BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outFile))) {
int c; while ((c = in.read()) != -1) out.write(c);
}
}
});
}
// ===== 初始化與測試 =====
public static void createTables() throws Exception {
try { stmt.executeUpdate("CREATE TABLE test_clob (id VARCHAR2(10), content CLOB)"); } catch (SQLException ignore) {}
try { stmt.executeUpdate("CREATE TABLE test_blob (id VARCHAR2(10), data BLOB)"); } catch (SQLException ignore) {}
}
public static void main(String[] args) throws Exception {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PWD);
stmt = conn.createStatement();
createTables();
clobInsert("c:/clobInsert.txt"); clobRead("c:/clobInsert.out");
clobModify("c:/clobModify.txt"); clobRead("c:/clobModify.out");
clobReplace("c:/clobReplace.txt"); clobRead("c:/clobReplace.out");
blobInsert("c:/blobInsert.doc"); blobRead("c:/blobInsert.out");
blobModify("c:/blobModify.doc"); blobRead("c:/blobModify.out");
blobReplace("c:/blobReplace.doc"); blobRead("c:/blobReplace.out");
conn.close();
System.out.println("LOB 操作測試完成");
}
}| 操作 | SQL 動作 | LOB 定位器 | 適用場景 |
|---|---|---|---|
| 修改 | 直接 SELECT ... FOR UPDATE | 原 LOB 定位器 | 在原資料基礎上追加/覆蓋，保留 LOB 物件身份 |
| 替換 | UPDATE SET col=EMPTY_LOB() → 再 SELECT ... FOR UPDATE | 新 LOB 定位器 | 完全捨棄舊資料，需分配新 LOB 區段 (效能稍低) |
| 錯誤現象 | 原因 | 解決 |
|---|---|---|
ORA-22920: row containing the LOB value is not locked | 未使用 FOR UPDATE 鎖定列 | 查詢必須加 FOR UPDATE |
ORA-01555: snapshot too old | 長時間事務中 LOB 被其他事務修改 | 縮短事務時間、增加 Undo 表空間 |
| 讀出內容為空/亂碼 | 字元集不匹配 (CLOB) / 流未正確關閉 | 確認 DB/NLS_CHARACTERSET、使用 try-with-resources 關閉流 |
JDBC 操作 Oracle LOB 核心在於：**事務邊界控制 + FOR UPDATE 鎖定 + 定位器流式讀寫**。掌握插入/修改/替換/讀取四大模式，配合緩衝流與分塊處理，即可高效處理 GB 級大物件。現代框架 (Spring JdbcTemplate, MyBatis, Hibernate) 已封裝此類細節，但理解底層機制對排查疑難效能問題至關重要。
