SQL Server `TEXT`/`NTEXT`/`IMAGE` (已棄用,對應 `VARCHAR(MAX)`/`NVARCHAR(MAX)`/`VARBINARY(MAX)`) 存儲大文本/二進制數據。Java JDBC **必須用 `getClob`/`getBlob` 流式讀取**,不能用 `getString`/`getBytes` (會載入整個物件到 JVM Heap,導致 OOM 或截斷)。
String sql = "SELECT FileName, Content FROM Documents WHERE Id = ?";
try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, docId);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
String fileName = rs.getString("FileName");
// 關鍵:獲取 Clob 物件,而非 getString
Clob clob = rs.getClob("Content"); // NTEXT/VARCHAR(MAX)
// 或 Blob blob = rs.getBlob("Content"); // IMAGE/VARBINARY(MAX)
// 流式寫入檔案,避免記憶體暴漲
try (Reader reader = clob.getCharacterStream();
BufferedWriter writer = Files.newBufferedWriter(Paths.get("D:/export", fileName))) {
char[] buffer = new char[8192];
int len;
while ((len = reader.read(buffer)) != -1) {
writer.write(buffer, 0, len);
}
}
// 二進制同理
// try (InputStream in = blob.getBinaryStream();
// OutputStream out = Files.newOutputStream(Paths.get(...))) { ... }
}
}
}
| 錯誤代碼 | 問題 | 修正 |
|---|---|---|
| `rs.getString("Content")` | JDBC 驅動預設截斷 (如 4000 字元)、大文本 OOM、編碼轉換錯誤 | `rs.getClob("Content").getCharacterStream()` 流式讀取 |
| `rs.getBytes("Content")` | 同樣全載入 Heap,二進制無編碼問題但 OOM 風險同在 | `rs.getBlob("Content").getBinaryStream()` |
| Base64 編碼存入 TEXT | 空間膨脹 33%、查詢/索引無效、雙重編解碼開銷 | **直接存二進制 `VARBINARY(MAX)`**,或啟用 `FILESTREAM`/`FileTable` |
String sql = "INSERT INTO Documents (FileName, Content) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, fileName);
// 關鍵:setCharacterStream / setBinaryStream + 長度
try (FileReader fr = new FileReader(file)) {
ps.setCharacterStream(2, fr, file.length()); // Clob
// ps.setBinaryStream(2, new FileInputStream(file), file.length()); // Blob
ps.executeUpdate();
}
}
若歷史數據已 Base64 存入 `TEXT`,讀取後解碼:
Clob clob = rs.getClob("Content");
try (Reader reader = clob.getCharacterStream();
InputStream b64In = new ReaderInputStream(reader, StandardCharsets.US_ASCII); // Apache Commons IO
InputStream decoded = Base64.getDecoder().wrap(b64In); // Java 8+ Base64
OutputStream out = Files.newOutputStream(Paths.get(targetPath))) {
decoded.transferTo(out); // 高效零拷貝
}
需依賴 `commons-io` 提供 `ReaderInputStream` 將 `Reader` 轉 `InputStream` 供 `Base64.Decoder.wrap()` 使用。