Oracle中BLOB、CLOB的读取和写入

在Oracle数据库中，大类型字段（也称为大对象或LOB，Large Object）用于存储大量的数据，如文本、图像、视频等。Oracle 提供了几种不同的大类型字段，主要包括：

1. CLOB（Character Large Object）：

   • 存储大量的字符数据，可以存储多达4 GB的文本。
   • 适用于需要存储大段文本信息的场景，如文档、日志记录等。

2. BLOB（Binary Large Object）：

   • 存储大量的二进制数据，可以存储多达4 GB的二进制信息。
   • 常用于存储图片、音频、视频等媒体文件。

3. NCLOB（National Character Large Object）：

   • 类似于CLOB，但用于存储多字节字符集（如Unicode字符集）的数据。
   • 适用于需要存储多国语言文本的应用。

4. BFILE（Binary File）：

   • 存储外部文件的引用，而不是将文件内容直接存储在数据库中。
   • BFILE可以存储在数据库外部文件系统中，数据库只存储其路径和文件名。

基于SQL和Java的方式实现读取和插入这些大类型字段，同时将读取的数据转换为字符串类型。

一、基于SQL 的方式实现 CLOB、BLOB 的插入与读取

1. 插入大类型数据

插入 CLOB 数据

CLOB用于存储大段文本，可以通过简单的SQL插入语句来插入数据：

INSERT INTO my_table (id, clob_column)
VALUES (1, 'This is a large text that can go up to 4 GB');

插入 BLOB 数据

BLOB用于存储二进制数据。由于直接通过SQL插入BLOB数据较为复杂，通常会通过文件或其他方法插入数据。

假设我们要插入一段十六进制字符串代表的二进制数据：

INSERT INTO my_table (id, blob_column)
VALUES (1, hextoraw('48656C6C6F20576F726C64')); -- 'Hello World' in hexadecimal

2. 读取大类型数据并转换为字符串

读取 CLOB 数据并转换为字符串

CLOB字段中的数据可以直接读取并视为字符串：

SELECT clob_column
FROM my_table
WHERE id = 1;

读取 BLOB 数据并转换为字符串 (UTL_RAW.CAST_TO_VARCHAR2)

BLOB数据通常是二进制的，如果需要将其转换为字符串，可以使用SQL中的UTL_RAW.CAST_TO_VARCHAR2函数，将其转换为VARCHAR2类型（注意BLOB大小不能超过VARCHAR2的限制[2000]）：

SELECT UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(blob_column, 4000, 1))
FROM my_table
WHERE id = 1;

这里dbms_lob.substr用于提取BLOB中的数据，最大可提取4000字节。
也可以先使用dbms_lob.substr(blob_column) 来判断BLOB类型字段的数据长度，如果不超过 2000,可以直接使用UTL_RAW.CAST_TO_VARCHAR2(blob_column)来将BLOB类型数据转为字符类型。

SELECT UTL_RAW.CAST_TO_VARCHAR2(blob_column)
FROM my_table
WHERE id = 1;

二、基于Java 方式实现 CLOB 和 BLOB 的插入与读取

在Java中，通过PreparedStatement进行插入，通过ResultSet进行读取。

1. Java 中插入 CLOB 和 BLOB 数据

插入 CLOB 数据

使用Java的PreparedStatement将字符串数据插入到CLOB字段：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class ClobInsertExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "INSERT INTO my_table (id, clob_column) VALUES (?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);
            pstmt.setString(2, "This is a large text for CLOB field.");

            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

插入 BLOB 数据

BLOB通常用于存储二进制数据，如图像或文件。通过Java的PreparedStatement插入二进制数据：

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class BlobInsertExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "INSERT INTO my_table (id, blob_column) VALUES (?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);

            FileInputStream inputStream = new FileInputStream("path/to/your/file.jpg");
            pstmt.setBinaryStream(2, inputStream, inputStream.available());

            pstmt.executeUpdate();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

2. Java 中读取 CLOB 和 BLOB 数据并转换为字符串

读取 CLOB 数据并转换为字符串

读取CLOB数据并将其转换为字符串非常简单：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ClobReadExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "SELECT clob_column FROM my_table WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                String clobData = rs.getString("clob_column");
                System.out.println(clobData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

读取 BLOB 数据并转换为字符串

由于BLOB是二进制数据，需要先读取为字节数组，然后将其转换为字符串

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class BlobReadExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "SELECT blob_column FROM my_table WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 1);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                InputStream inputStream = rs.getBinaryStream("blob_column");
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

                byte[] buffer = new byte[4096];
                int bytesRead = -1;

                while ((bytesRead = inputStream.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }

                byte[] imageBytes = outputStream.toByteArray();
                String blobAsString = new String(imageBytes, "UTF-8");
                System.out.println(blobAsString);

                inputStream.close();
                outputStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}