JDBC 與 Hibernate 處理 CLOB/BLOB 大型欄位完整指南

資料庫與 Java 類型對應表

Domain 實體類對應

• CLOB → String 或 java.sql.Clob
• BLOB → byte[] 或 java.sql.Blob

Hibernate 映射配置

<property name="clobField" type="clob"/>
<property name="blobField" type="binary"/>

也可直接使用資料庫專屬類型，如 oracle.sql.Clob、oracle.sql.Blob。

JDBC 操作 CLOB（以 Oracle 為例）

操作 CLOB/BLOB 需兩步驟：先插入空值鎖定行，再更新大物件內容。

寫入 CLOB

conn.setAutoCommit(false);
// 1. 插入空 CLOB
String sql = "INSERT INTO file(name, file_content) VALUES('jack', EMPTY_CLOB())";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();

// 2. 鎖定該行並取得 Clob 物件
sql = "SELECT file_content FROM file WHERE name='jack' FOR UPDATE";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    oracle.sql.Clob clob = (oracle.sql.Clob) rs.getClob(1);
    java.io.OutputStream writer = clob.getAsciiOutputStream();
    byte[] data = newFileContent.getBytes();
    writer.write(data);
    writer.flush();
    writer.close();
}
pstmt.close();

讀取 CLOB

oracle.sql.Clob clob = rs.getClob("file_content");
if (clob != null) {
    Reader is = clob.getCharacterStream();
    BufferedReader br = new BufferedReader(is);
    String line;
    while ((line = br.readLine()) != null) {
        content += line + "<br>";
    }
}

JDBC 操作 BLOB

conn.setAutoCommit(false);
String sql = "INSERT INTO photo(name, photo) VALUES('jack', EMPTY_BLOB())";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();

sql = "SELECT photo FROM photo WHERE name='jack' FOR UPDATE";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    oracle.sql.Blob blob = (oracle.sql.Blob) rs.getBlob(1);
    File file = new File("c:\\test.rar");
    FileInputStream fin = new FileInputStream(file);
    OutputStream out = blob.getBinaryOutputStream();
    byte[] buffer = new byte[blob.getBufferSize()];
    int count;
    while ((count = fin.read(buffer)) != -1) {
        out.write(buffer, 0, count);
    }
    fin.close();
    out.close();
}

Hibernate 處理 CLOB

MyFile file = new MyFile();
file.setName("jack");
file.setContent(session.getLobHelper().createClob("")); // 建立空 CLOB
session.save(file);
session.flush();
session.refresh(file, LockMode.UPGRADE); // 鎖定

oracle.sql.Clob clob = (oracle.sql.Clob) file.getContent();
Writer pw = clob.getCharacterOutputStream();
pw.write(longText); // 寫入長文本
pw.close();
session.close();

Hibernate 處理 BLOB

Photo photo = new Photo();
photo.setName("jack");
photo.setPhoto(session.getLobHelper().createBlob(new byte[0])); // 空 BLOB
session.save(photo);
session.flush();
session.refresh(photo, LockMode.UPGRADE);

oracle.sql.Blob blob = (oracle.sql.Blob) photo.getPhoto();
OutputStream out = blob.getBinaryOutputStream();
File f = new File("c:\\test.rar");
FileInputStream fin = new FileInputStream(f);
byte[] data = new byte[(int) fin.available()];
fin.read(data);
out.write(data);
fin.close();
out.close();
session.flush();

關鍵點總結

1. 必須關閉自動提交（setAutoCommit(false)）以支援大物件操作。
2. Oracle 需使用 EMPTY_CLOB()/EMPTY_BLOB() 初始化，並以 FOR UPDATE 鎖定行。
3. Hibernate 透過 LobHelper 建立空大物件，再透過 refresh(..., LockMode.UPGRADE) 取得實體鎖定。
4. 資料流操作完成後務必關閉流並 flush()。