如何使用JDBC将大型(或至少很简单)的BLOB放入Oracle中? [英] How do I place large (or at least nontrivial) BLOBs into Oracle with JDBC?

问题描述

我正在一个应用程序上进行一些批处理，并且想要将输入和输出数据作为文件存储在Oracle数据库的BLOB字段中. Oracle版本是10g r2.

I'm working on an application to do some batch processing, and want to store the input and output data as files in BLOB fields in an Oracle database. The Oracle version is 10g r2.

使用如下的PreparedStatement.setBinaryStream()方法会将一个小的文本文件插入数据库，但是对于较大的图像文件我没有任何运气.

Using the PreparedStatement.setBinaryStream() method as below will insert a small text file into the database, but I'm not having any luck with a larger image file.

我做错什么了吗?这可能与JDBC有关吗?我需要打扰DBA吗?感谢您的帮助.

Am I doing something wrong? Is this possible to do with JDBC? Will I need to bother the DBA? Thanks for your help.

问题已解决.我已将此代码更新为工作示例:

The issue has been resolved. I've updated this code to a working sample:

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class WriteBlobDriver {
    public static void main(String[] args) {
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(
                    "blahblah",
                    "blahblah",
                    "blahblah");
            con.setAutoCommit(false);
            Statement statement = con.createStatement();
            //statement.executeUpdate("UPDATE BATCH_GC_JOBS SET INPUT_BATCH_FILE = EMPTY_BLOB() WHERE JOB_ID = 'a'");

            //Get blob and associated output stream
            ResultSet resultSet = statement.executeQuery("SELECT INPUT_BATCH_FILE FROM BATCH_GC_JOBS WHERE JOB_ID = 'a' FOR UPDATE");
            resultSet.next();
            Blob blob = resultSet.getBlob(1);
            OutputStream outputStream = ((oracle.sql.BLOB)blob).getBinaryOutputStream();

            // Buffer to hold chunks of data to being written to the Blob.
            byte[] buffer = new byte[10* 1024];
            int nread = 0;

            //Write file to output stream
            File file = new File("C:\\TEMP\\Javanese_cat.jpg");
            FileInputStream fileInputStream = new FileInputStream(file);
            while ((nread = fileInputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, nread);
            }

            //Cleanup
            fileInputStream.close();
            outputStream.close();
            statement.close();
            con.commit();
            con.close();            
            System.out.println("done!");
        } catch (Exception e){
            e.printStackTrace();
        }
    }
}

推荐答案

我不认为您可以在一个步骤中(对于数据> 4k)通过JDBC更新或插入到BLOB/CLOB中.从 Oracle提供的示例，您需要:

I don't think you can update or insert into a BLOB/CLOB with JDBC in a single step (for data > 4k). From this example from Oracle, it seems you need to:

1. 使用SQL函数empty_clob()
插入一个空的LOB
2. 选择用于更新您插入的LOB
3. 使用ResultSet.getBlob()获取Java中的LOB，然后使用blob.setBinaryStream获取输出流(因为已弃用oracle.sql.BLOB.getBinaryOutputStream())
4. 写入此输出流
5. 完成后关闭输出流

1. Insert an empty LOB with the SQL function empty_clob()
2. Select for update the LOB you've inserted
3. get the LOB in java with ResultSet.getBlob() then get the output stream with blob.setBinaryStream (since oracle.sql.BLOB.getBinaryOutputStream() is deprecated)
4. write to this output stream
5. close the output stream when you are finished

您将在Pl/SQL中执行类似的操作(选择更新LOB，然后将其写入).

You would do something similar in Pl/SQL (SELECT FOR UPDATE a LOB, then write to it).

这篇关于如何使用JDBC将大型(或至少很简单)的BLOB放入Oracle中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！