JDBC 實戰（三）：大文本 (CLOB/TEXT) 與二進制 (BLOB) 數據讀寫完整範例

大對象類型對照表

MySQL **無 CLOB 類型**，統一用 TEXT 族；大小上限分別為 255B/64KB/16MB/4GB。

核心 API：流式讀寫

• PreparedStatement.setCharacterStream(int, Reader, long) → 寫 TEXT
• PreparedStatement.setBinaryStream(int, InputStream, long) → 寫 BLOB
• ResultSet.getCharacterStream(String) → 讀 TEXT
• ResultSet.getBinaryStream(String) → 讀 BLOB

**必須使用流**，不可直接 setString/getString（受 JVM 堆/驅動緩衝區限制，大對象會 OOM）。

完整代碼範例：CLOB/TEXT 操作

package com.cream.ice.jdbc;

import java.io.*;
import java.sql.*;
import org.junit.Test;

public class ClobDemo {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Test
    public void insertLongText() {
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("INSERT INTO test(id, content) VALUES(?, ?)");
            ps.setInt(1, 1);

            File file = new File("d:/test.txt");
            Reader reader = new FileReader(file);
            // 長度轉 int（MySQL 不支援超大流長度參數）
            ps.setCharacterStream(2, reader, (int) file.length());

            int rows = ps.executeUpdate();
            System.out.println(rows > 0 ? "插入成功" : "插入失敗");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseResources(null, ps, conn);
        }
    }

    @Test
    public void readLongText() {
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("SELECT * FROM test WHERE id=?");
            ps.setInt(1, 1);
            rs = ps.executeQuery();

            while (rs.next()) {
                Reader reader = rs.getCharacterStream("content");
                Writer writer = new FileWriter("e:/test.txt");
                char[] buf = new char[1024];
                int len;
                while ((len = reader.read(buf)) != -1) {
                    writer.write(buf, 0, len);
                }
                reader.close();
                writer.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseResources(rs, ps, conn);
        }
    }
}

完整代碼範例：BLOB 操作

package com.cream.ice.jdbc;

import java.io.*;
import java.sql.*;
import org.junit.Test;

public class BlobDemo {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Test
    public void insertBlob() {
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("INSERT INTO test(id, content) VALUES(?, ?)");
            ps.setInt(1, 1);

            InputStream in = new FileInputStream("d:/test.jpg");
            ps.setBinaryStream(2, in, in.available());

            int rows = ps.executeUpdate();
            System.out.println(rows > 0 ? "插入成功" : "插入失敗");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseResources(null, ps, conn);
        }
    }

    @Test
    public void readBlob() {
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement("SELECT * FROM test WHERE id=?");
            ps.setInt(1, 1);
            rs = ps.executeQuery();

            while (rs.next()) {
                InputStream in = rs.getBinaryStream("content");
                OutputStream out = new FileOutputStream("e:/test.jpg");
                byte[] buf = new byte[1024];
                int len;
                while ((len = in.read(buf)) != -1) {
                    out.write(buf, 0, len);
                }
                in.close();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseResources(rs, ps, conn);
        }
    }
}

關鍵工具類 JdbcUtils（假設已實現）

public class JdbcUtils {
    public static Connection getConnection() { /* Druid/HikariCP 獲取連接 */ }
    public static void releaseResources(ResultSet rs, Statement st, Connection conn) {
        // 標準關閉順序：RS → Stmt → Conn，捕獲異常不拋出
    }
}

生產環境避坑指南

1. 流長度參數：MySQL 驅動對 setCharacterStream/BinaryStream 的長度參數實際用 int，超大文件需分塊或用 LOAD DATA INFILE
2. 連接池配置：大對象讀寫佔用連接時間長，適當增大 maxPoolSize、設置 maxLifetime 避免連接過期
3. 事務邊界：大對象寫入建議獨立事務，避免長事務持有鎖/Undo 空間
4. 內存控制：緩衝區 4KB-16KB 適中，過大易觸發 GC、過小增繫統調用
5. 替代方案：超大文件（>100MB）建議存對象存儲 (S3/MinIO)，資料庫只存元數據 URL