JDBC 操作 SQL Server 大對象 (BLOB/IMAGE)：PreparedStatement 流式讀寫最佳實踐

在 SQL Server 中存儲圖片、文檔、二進制文件等大對象，需使用 IMAGE (舊版) 或 VARBINARY(MAX) (SQL Server 2005+) 資料類型。本文展示基於 PreparedStatement 的流式讀寫模式，避免將大文件完整載入記憶體導致 OOM，並提供完整的寫入與讀取代碼範例。

資料表結構

CREATE TABLE LargeFiles (
    id CHAR(4) PRIMARY KEY,      -- 業務主鍵
    data VARBINARY(MAX) NOT NULL -- 大二進制對象 (推薦替代 IMAGE)
);

寫入大對象：流式輸入 (避免 byte[] 全量載入)

import java.io.*;
import java.sql.*;

public class BlobInsertStream {
    public static void main(String[] args) {
        String filePath = "D:\\test1.jpg";      // 來源文件
        String sql = "INSERT INTO LargeFiles (id, data) VALUES ('0012', ?)";
        String url = "jdbc:jtds:sqlserver://localhost:1433/YourDB"; // jTDS URL
        String user = "sa";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             InputStream fin = new FileInputStream(filePath)) {
            
            // 關鍵：setBinaryStream 流式傳輸，不載入完整 byte[] 到堆
            File file = new File(filePath);
            pstmt.setBinaryStream(1, fin, (int) file.length());
            
            int rows = pstmt.executeUpdate();
            System.out.println("寫入完成，影響行數: " + rows);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

關鍵 API 對比

讀取大對象：流式輸出 (分塊寫入目標文件)

import java.io.*;
import java.sql.*;

public class BlobReadStream {
    public static void main(String[] args) {
        String outputPath = "D:\\test2.jpg";    // 目標文件
        String sql = "SELECT data FROM LargeFiles WHERE id = '0012'";
        String url = "jdbc:jtds:sqlserver://localhost:1433/YourDB";
        String user = "sa";
        String password = "password";
        
        final int BUFFER_SIZE = 8192; // 8KB 緩衝區
        
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery();
             InputStream bin = null;
             OutputStream out = new BufferedOutputStream(new FileOutputStream(outputPath), BUFFER_SIZE)) {
            
            if (rs.next()) {
                // 獲取二進制流
                bin = rs.getBinaryStream("data");
                if (bin == null) {
                    System.out.println("數據為 NULL");
                    return;
                }
                
                // 分塊讀寫
                byte[] buffer = new byte[BUFFER_SIZE];
                int bytesRead;
                long totalBytes = 0;
                while ((bytesRead = bin.read(buffer)) != -1) {
                    out.write(buffer, 0, bytesRead);
                    totalBytes += bytesRead;
                }
                out.flush();
                System.out.println("讀取完成，總位元組: " + totalBytes);
            } else {
                System.out.println("未找到記錄");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

完整封裝工具類 (生產就緒)

import java.io.*;
import java.sql.*;

public class SqlServerBlobUtil {
    private final String jdbcUrl;
    private final String user;
    private final String password;
    private final int bufferSize;
    
    public SqlServerBlobUtil(String jdbcUrl, String user, String password, int bufferSize) {
        this.jdbcUrl = jdbcUrl;
        this.user = user;
        this.password = password;
        this.bufferSize = bufferSize;
    }
    
    // 寫入
    public void insertBlob(String table, String idColumn, String idValue, String blobColumn, File file) 
            throws SQLException, IOException {
        String sql = String.format("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, idColumn, blobColumn);
        try (Connection conn = DriverManager.getConnection(jdbcUrl, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             InputStream fin = new FileInputStream(file)) {
            pstmt.setString(1, idValue);
            pstmt.setBinaryStream(2, fin, (int) file.length());
            pstmt.executeUpdate();
        }
    }
    
    // 讀取
    public void extractBlob(String table, String blobColumn, String idColumn, String idValue, File outputFile) 
            throws SQLException, IOException {
        String sql = String.format("SELECT %s FROM %s WHERE %s = ?", blobColumn, table, idColumn);
        try (Connection conn = DriverManager.getConnection(jdbcUrl, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             OutputStream out = new BufferedOutputStream(new FileOutputStream(outputFile), bufferSize)) {
            pstmt.setString(1, idValue);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    try (InputStream in = rs.getBinaryStream(blobColumn)) {
                        if (in == null) throw new SQLException("BLOB 數據為 NULL");
                        byte[] buffer = new byte[bufferSize];
                        int len;
                        while ((len = in.read(buffer)) != -1) out.write(buffer, 0, len);
                    }
                } else {
                    throw new SQLException("記錄不存在: " + idValue);
                }
            }
        }
    }
    
    // 使用範例
    public static void main(String[] args) throws Exception {
        SqlServerBlobUtil util = new SqlServerBlobUtil(
            "jdbc:jtds:sqlserver://localhost:1433/YourDB", "sa", "password", 8192);
        util.insertBlob("LargeFiles", "id", "0013", "data", new File("D:\\doc.pdf"));
        util.extractBlob("LargeFiles", "data", "id", "0013", new File("D:\\doc_copy.pdf"));
    }
}

關鍵注意事項

• 資料類型選擇：SQL Server 2005+ 強烈建議用 VARBINARY(MAX) 替代 IMAGE (已棄用)，支援更大尺寸 (2GB)、標準 SQL、效能更好
• 連線屬性：jTDS URL 加 ;blobBufferSize=8192 可調整驅動端緩衝
• 事務邊界：大對象寫入耗時長，確保事務超時設置足夠 (setTransactionTimeout)
• NULL 處理：getBinaryStream 返回 null 時需業務判斷
• 資源關閉順序：ResultSet → Statement → Connection → Stream (try-with-resources 自動保證)
• 記憶體監控：大文件流式讀寫堆佔用應穩定在 bufferSize * 2 左右，若飆升檢查驅動是否內部緩衝

總結

SQL Server 大對象讀寫核心原則：PreparedStatement + 流式 API (setBinaryStream/getBinaryStream) + 固定緩衝區分塊處理。此模式將記憶體佔用從 O(FileSize) 降為 O(BufferSize)，支援任意大小文件 (受限於 2GB VARBINARY(MAX) 上限)。封裝為工具類後，業務代碼簡潔為單行調用，並統一異常處理、資源管理、緩衝區策略，達到生產級就緒標準。