Oracle BLOB (Binary Large Object) 用於存儲二進制大對象（圖片、文檔、視頻等）。本文提供基於 JDBC 的完整工具類，實現文件寫入 BLOB 字段及從 BLOB 讀取還原文件，採用單例模式管理數據庫連接，支援事務控制。
CREATE TABLE PS_ZP_PRJ_WBS_BLOB (
ZP_PRJ_ID VARCHAR2(50), -- 項目ID
ZZ_SEQ_NUM VARCHAR2(50), -- 任務序號
ZZ_FILE_NAME VARCHAR2(200), -- 文件名
ZZ_IMAGE_BLOB BLOB -- 二進制數據
);
package com.example.blob;
import oracle.sql.BLOB;
import java.io.*;
import java.sql.*;
import java.util.Properties;
public class BlobUtil {
// 單例模式
private static BlobUtil instance;
private String envProfile; // 環境標識：dev/test/prod
private BlobUtil() {}
public static synchronized BlobUtil getInstance(String env) {
if (instance == null) instance = new BlobUtil();
instance.envProfile = env;
return instance;
}
/** 獲取數據庫連接（從配置文件讀取） */
private Connection getConnection() throws Exception {
Properties props = new Properties();
try (InputStream is = getClass().getClassLoader().getResourceAsStream("config.properties")) {
props.load(is);
}
String url = String.format("jdbc:oracle:thin:@%s:%s:%s",
props.getProperty(envProfile + ".host"),
props.getProperty(envProfile + ".port"),
props.getProperty(envProfile + ".database"));
return DriverManager.getConnection(url,
props.getProperty(envProfile + ".username"),
props.getProperty(envProfile + ".password"));
}
/**
* 寫入文件到 BLOB 字段
* @return true 成功，false 失敗
*/
public boolean writeBlob(String projectId, String taskId, String fileName, String filePath) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
BLOB blob = null;
try {
conn = getConnection();
conn.setAutoCommit(false); // 開啟事務
// 1. 插入空 BLOB 佔位
String sql = "INSERT INTO PS_ZP_PRJ_WBS_BLOB (ZP_PRJ_ID, ZZ_SEQ_NUM, ZZ_FILE_NAME, ZZ_IMAGE_BLOB) " +
"VALUES (?, ?, ?, empty_blob())";
ps = conn.prepareStatement(sql);
ps.setString(1, projectId);
ps.setString(2, taskId);
ps.setString(3, fileName);
ps.executeUpdate();
ps.close();
// 2. 鎖定該行並獲取 BLOB 定位器
sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB " +
"WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ? FOR UPDATE";
ps = conn.prepareStatement(sql);
ps.setString(1, projectId);
ps.setString(2, taskId);
ps.setString(3, fileName);
rs = ps.executeQuery();
if (rs.next()) {
blob = (BLOB) rs.getBlob(1);
} else {
throw new SQLException("插入後未查詢到 BLOB 定位器");
}
rs.close();
ps.close();
// 3. 流式寫入二進制數據
try (InputStream in = new FileInputStream(filePath);
OutputStream out = blob.setBinaryStream(1L)) { // 1-based 位置
byte[] buffer = new byte[8192]; // 8KB 緩衝區
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
}
conn.commit();
return true;
} catch (Exception e) {
rollbackQuietly(conn);
e.printStackTrace();
return false;
} finally {
closeQuietly(rs, ps, conn);
}
}
/**
* 從 BLOB 讀取文件到本地
*/
public boolean readBlob(String projectId, String taskId, String fileName, String destPath) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
BLOB blob = null;
try {
conn = getConnection();
String sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB " +
"WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, projectId);
ps.setString(2, taskId);
ps.setString(3, fileName);
rs = ps.executeQuery();
if (rs.next()) {
blob = (BLOB) rs.getBlob(1);
} else {
System.err.println("未找到對應記錄");
return false;
}
try (InputStream in = blob.getBinaryStream();
FileOutputStream out = new FileOutputStream(destPath)) {
byte[] buffer = new byte[8192];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
closeQuietly(rs, ps, conn);
}
}
// ========== 輔助方法 ==========
private void rollbackQuietly(Connection conn) {
if (conn != null) {
try { conn.rollback(); } catch (SQLException ignored) {}
}
}
private void closeQuietly(AutoCloseable... resources) {
for (AutoCloseable r : resources) {
if (r != null) {
try { r.close(); } catch (Exception ignored) {}
}
}
}
// 測試主方法
public static void main(String[] args) {
BlobUtil util = BlobUtil.getInstance("dev");
boolean writeOk = util.writeBlob("PRJ001", "TASK01", "logo.png", "D:/images/logo.png");
System.out.println("寫入結果：" + writeOk);
boolean readOk = util.readBlob("PRJ001", "TASK01", "logo.png", "D:/temp/logo_copy.png");
System.out.println("讀取結果：" + readOk);
}
}
dev.host=localhost
dev.port=1521
dev.database=ORCL
dev.username=scott
dev.password=tiger
prod.host=192.168.1.100
prod.port=1521
prod.database=PRODDB
prod.username=app_user
prod.password=******
Oracle 要求先插入 empty_blob() 創建 BLOB 定位器，然後通過 SELECT ... FOR UPDATE 鎖定行並獲取可寫的 BLOB 對象。直接插入數據會報錯 "ORA-22275: invalid LOB locator"。
使用 blob.setBinaryStream(pos) 和 blob.getBinaryStream() 進行流式讀寫，配合 8KB 緩衝區，避免大文件載入內存導致 OOM。單次寫入支持 2GB 以上文件（受限於 Oracle BLOB 最大 4GB）。
寫入操作包含插入、查詢、流寫入三步，必須在同一事務中完成。設置 conn.setAutoCommit(false)，成功後 commit()，異常時 rollback()。
代碼中強轉 (BLOB) rs.getBlob(1) 使用 oracle.sql.BLOB 而非標準 java.sql.Blob，是為了調用 setBinaryStream 等 Oracle 擴展方法。若追求可移植性，可用標準 JDBC 4.0 Blob.setBinaryStream(long)（JDK 1.6+ 支持）。
ojdbc8.jar（JDK 8+）或對應版本驅動包CREATE TABLE、INSERT、SELECT FOR UPDATE 權限oracle.jdbc.defaultNChar=true本示例提供了生產級的 Oracle BLOB 讀寫工具類，涵蓋連接池預留、事務管理、資源安全釋放、大文件流式處理等最佳實踐。實際項目中建議整合連接池（HikariCP/Druid）並添加重試機制、進度回調等增強功能。
