使用 Java 为 DB2 和 Oracle 插入 BLOB

2022-01-14 00:00:00 blob db2 oracle jdbc java

我目前正在验证在 Oracle 上为 DB2 开发的应用程序.由于我们不想维护两个单独的源,我需要一些查询来将 blob 插入到字段中,这在 oracle 和 db2 中都可以使用.我没有任何标识符来区分应用程序在哪个数据库下运行.

I am currently validating an application developed on Oracle for DB2. Since we don't want to maintain two separate sources, I need some query to insert blob into a field, that works in both oracle and db2. I don't have any identifier to distinguish under which DB the application is running.

我在oracle中使用utl_raw.cast_to_raw,在DB2中使用CAST() as BLOB,两者互不兼容.

I used utl_raw.cast_to_raw in oracle and CAST() as BLOB in DB2 which are mutually incompatible.

推荐答案

您将无法找到使用某种类型转换的通用 SQL.但是您可以使用 JDBC 的 setBinaryStream()

You won't be able to find a common SQL that uses some kind of casting. But you can do this with "plain" SQL using JDBC's setBinaryStream()

PreparedStatement pstmt = connection.prepareStatement(
   "insert into blob_table (id, blob_data) values (?, ?)";

File blobFile = new File("your_document.pdf");
InputStream in = new FileInputStream(blobFile);

pstmt.setInt(1, 42);
pstmt.setBinaryStream(2, in, (int)blobFile.length());
pstmt.executeUpdate();
connection.commit();

您可以使用 setBinaryStream() 以与 UPDATE 语句相同的方式.

You can use setBinaryStream() the same way with an UPDATE statement.

相关文章