YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon Language

#LOB数据处理最佳实践

# 适用范围

本文适用于Java应用通过YashanDB JDBC驱动处理CLOB和BLOB数据的场景，包括业务表创建、流式插入、分块读取、内容校验、事务内更新和资源释放。

示例验证环境如下：

组件 版本
JDK 17.0.19
YashanDB JDBC驱动 1.10.11
YashanDB Server 23.4.10.100

# 预期效果

完成本实践后，应用应具备以下能力：

  1. 使用CLOB和BLOB存储长文本与二进制内容。
  2. 使用流式接口读写LOB。
  3. 分块读取LOB并做校验。
  4. 在同一事务中更新LOB和业务状态。
  5. 及时关闭相关资源。

# 实施步骤

# 1. 创建LOB业务表

由部署账号执行以下建表语句：

CREATE TABLE business_asset (
    asset_id INTEGER PRIMARY KEY,
    asset_code VARCHAR(32),
    description CLOB,
    attachment BLOB,
    status VARCHAR(16)
);

业务账号只需要该表的SELECT、INSERT和UPDATE权限。应用不使用高权限账号，也不允许把用户输入直接拼接为表名或列名。

# 2. 选择LOB接口

数据类型 流式写入 流式读取 适用数据
CLOB setCharacterStream() getCharacterStream() 长文本、说明文档、文本配置
BLOB setBinaryStream() getBinaryStream() 图片、压缩包、协议文件和其他二进制内容

使用带长度参数的重载时，CLOB长度单位为字符，BLOB长度单位为字节。长度未知时可以使用不带长度参数的重载，但应先验证目标驱动版本。

# 3. 流式插入LOB

输入流由业务层创建，JDBC代码在流仍然有效时执行SQL。流、PreparedStatement和连接应位于清晰的资源作用域内。

try (PreparedStatement statement = connection.prepareStatement(
        "INSERT INTO business_asset "
                + "(asset_id, asset_code, description, attachment, status) "
                + "VALUES (?, ?, ?, ?, ?)")) {
    statement.setInt(1, 1001);
    statement.setString(2, "ASSET_1001");
    statement.setCharacterStream(3, textReader, characterLength);
    statement.setBinaryStream(4, binaryInputStream, byteLength);
    statement.setString(5, "DRAFT");
    statement.executeUpdate();
}

真实大文件可以使用FileInputStream和Files.newBufferedReader()提供流。不要先使用readAllBytes()把整个文件加载到Java堆内存。

# 4. 分块读取并校验LOB

读取时使用固定大小缓冲区。BLOB可以计算SHA-256摘要，用于校验业务传输前后的内容一致性；摘要不是加密存储，也不能替代访问控制。

try (Reader reader = resultSet.getCharacterStream("description")) {
    char[] buffer = new char[2048];
    for (int count; (count = reader.read(buffer)) != -1;) {
        textWriter.write(buffer, 0, count);
    }
}

try (InputStream input = resultSet.getBinaryStream("attachment")) {
    byte[] buffer = new byte[4096];
    for (int count; (count = input.read(buffer)) != -1;) {
        digest.update(buffer, 0, count);
    }
}

如果业务只需要把BLOB发送到文件或网络响应，应直接把读取缓冲区写入目标流，不需要保存完整byte[]。

# 5. 在事务中更新LOB和业务状态

LOB内容和状态字段属于同一业务变更时，应在一个事务中更新。更新行数不为1表示业务对象不存在或条件不满足，程序应回滚而不是继续提交。

boolean initialAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
try (PreparedStatement statement = connection.prepareStatement(
        "UPDATE business_asset "
                + "SET description = ?, attachment = ?, status = ? "
                + "WHERE asset_id = ?")) {
    statement.setCharacterStream(1, updatedTextReader, updatedCharacterLength);
    statement.setBinaryStream(2, updatedBinaryStream, updatedByteLength);
    statement.setString(3, "APPROVED");
    statement.setInt(4, 1001);
    if (statement.executeUpdate() != 1) {
        throw new SQLException("LOB business update did not affect one row");
    }
    connection.commit();
} catch (Exception exception) {
    connection.rollback();
    throw exception;
} finally {
    connection.setAutoCommit(initialAutoCommit);
}

# 6. 执行完整业务示例

以下程序使用jdbc_bp_lob_asset临时表完成CLOB和BLOB流式插入、读取校验、业务更新和再次校验。程序只删除本次成功创建的对象。

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.security.MessageDigest;

public class LobBestPracticeExample {
    private static final String TABLE = "jdbc_bp_lob_asset";

    public static void main(String[] args) throws Exception {
        String initialText = repeat("Initial asset description. ", 200);
        byte[] initialBinary = bytes(8192, 7);
        String updatedText = repeat("Approved asset description. ", 160);
        byte[] updatedBinary = bytes(12288, 11);
        boolean created = false;

        try (Connection connection = open()) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("CREATE TABLE " + TABLE
                        + " (asset_id INTEGER PRIMARY KEY, "
                        + "asset_code VARCHAR(32), description CLOB, "
                        + "attachment BLOB, status VARCHAR(16))");
                created = true;
            }

            boolean initialAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
            try {
                insert(connection, initialText, initialBinary);
                LobValue inserted = read(connection);
                print("Inserted", inserted);
                require(initialText.equals(inserted.text),
                        "Inserted CLOB mismatch");
                require(digest(initialBinary).equals(
                                inserted.binaryDigest),
                        "Inserted BLOB mismatch");

                update(connection, updatedText, updatedBinary);
                connection.commit();
                System.out.println("LOB transaction committed");
            } catch (Exception exception) {
                connection.rollback();
                throw exception;
            } finally {
                connection.setAutoCommit(initialAutoCommit);
            }

            LobValue updated = read(connection);
            print("Updated", updated);
            require(updatedText.equals(updated.text),
                    "Updated CLOB mismatch");
            require(digest(updatedBinary).equals(updated.binaryDigest),
                    "Updated BLOB mismatch");
            require("APPROVED".equals(updated.status), "Status mismatch");
        } finally {
            if (created) {
                execute("DROP TABLE " + TABLE);
            }
        }
    }

    private static void insert(
            Connection connection, String text, byte[] binary)
            throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO " + TABLE
                        + " (asset_id, asset_code, description, "
                        + "attachment, status) VALUES (?, ?, ?, ?, ?)")) {
            statement.setInt(1, 1001);
            statement.setString(2, "ASSET_1001");
            statement.setCharacterStream(
                    3, new StringReader(text), text.length());
            statement.setBinaryStream(
                    4, new ByteArrayInputStream(binary), binary.length);
            statement.setString(5, "DRAFT");
            statement.executeUpdate();
        }
    }

    private static void update(
            Connection connection, String text, byte[] binary)
            throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(
                "UPDATE " + TABLE
                        + " SET description = ?, attachment = ?, status = ? "
                        + "WHERE asset_id = ?")) {
            statement.setCharacterStream(
                    1, new StringReader(text), text.length());
            statement.setBinaryStream(
                    2, new ByteArrayInputStream(binary), binary.length);
            statement.setString(3, "APPROVED");
            statement.setInt(4, 1001);
            if (statement.executeUpdate() != 1) {
                throw new SQLException(
                        "LOB business update did not affect one row");
            }
        }
    }

    private static LobValue read(Connection connection) throws Exception {
        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT description, attachment, status FROM " + TABLE
                        + " WHERE asset_id = ?")) {
            statement.setInt(1, 1001);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (!resultSet.next()) {
                    throw new SQLException("LOB business row not found");
                }
                String text;
                try (Reader reader = resultSet.getCharacterStream(1);
                     StringWriter writer = new StringWriter()) {
                    copy(reader, writer);
                    text = writer.toString();
                }
                byte[] binary;
                try (InputStream input = resultSet.getBinaryStream(2);
                     ByteArrayOutputStream output =
                             new ByteArrayOutputStream()) {
                    copy(input, output);
                    binary = output.toByteArray();
                }
                return new LobValue(text, binary.length,
                        digest(binary), resultSet.getString(3));
            }
        }
    }

    private static void copy(Reader reader, StringWriter writer)
            throws Exception {
        char[] buffer = new char[2048];
        for (int count; (count = reader.read(buffer)) != -1;) {
            writer.write(buffer, 0, count);
        }
    }

    private static void copy(
            InputStream input, ByteArrayOutputStream output)
            throws Exception {
        byte[] buffer = new byte[4096];
        for (int count; (count = input.read(buffer)) != -1;) {
            output.write(buffer, 0, count);
        }
    }

    private static String digest(byte[] value) throws Exception {
        byte[] result = MessageDigest.getInstance("SHA-256")
                .digest(value);
        StringBuilder text = new StringBuilder();
        for (byte item : result) {
            text.append(String.format("%02x", item & 0xff));
        }
        return text.toString();
    }

    private static String repeat(String value, int count) {
        StringBuilder result = new StringBuilder(value.length() * count);
        for (int index = 0; index < count; index++) {
            result.append(value);
        }
        return result.toString();
    }

    private static byte[] bytes(int length, int seed) {
        byte[] result = new byte[length];
        for (int index = 0; index < result.length; index++) {
            result[index] = (byte) ((index * 31 + seed) & 0xff);
        }
        return result;
    }

    private static void print(String phase, LobValue value) {
        System.out.println(phase + " CLOB chars: " + value.text.length());
        System.out.println(phase + " BLOB bytes: " + value.binaryLength);
        System.out.println(phase + " BLOB SHA-256: "
                + value.binaryDigest);
        System.out.println(phase + " status: " + value.status);
    }

    private static void require(boolean condition, String message) {
        if (!condition) {
            throw new IllegalStateException(message);
        }
    }

    private static Connection open() throws SQLException {
        return DriverManager.getConnection(required("YASDB_URL"),
                required("YASDB_USER"), required("YASDB_PASSWORD"));
    }

    private static void execute(String sql) throws SQLException {
        try (Connection connection = open();
             Statement statement = connection.createStatement()) {
            statement.execute(sql);
        }
    }

    private static String required(String name) {
        String value = System.getenv(name);
        if (value == null || value.isEmpty()) {
            throw new IllegalStateException(
                    "Missing environment variable: " + name);
        }
        return value;
    }

    private static final class LobValue {
        private final String text;
        private final int binaryLength;
        private final String binaryDigest;
        private final String status;

        private LobValue(String text, int binaryLength,
                         String binaryDigest, String status) {
            this.text = text;
            this.binaryLength = binaryLength;
            this.binaryDigest = binaryDigest;
            this.status = status;
        }
    }
}

# 7. 验证结果

关键输出如下：

Inserted CLOB chars: 5400
Inserted BLOB bytes: 8192
Inserted status: DRAFT
LOB transaction committed
Updated CLOB chars: 4480
Updated BLOB bytes: 12288
Updated status: APPROVED

程序还会输出插入前后BLOB的SHA-256摘要，并在摘要、CLOB文本或业务状态不一致时抛出异常。

pdf-btn 下载文档 copy-btn 复制链接
edit-icon
反馈
coperate-icon
coperate
合作
communication-icon
communicate
交流