过于复杂的 oracle jdbc BLOB 处理 [英] Overcomplicated oracle jdbc BLOB handling

问题描述

当我在网上搜索使用 jdbc 瘦驱动程序将 BLOB 插入 Oracle 数据库时，大多数网页都建议采用 3 步方法:

When I search the web for inserting BLOBs into Oracle database with jdbc thin driver, most of the webpages suggest a 3-step approach:

1. 插入 empty_blob() 值.
2. 选择带有 for update 的行.
3. 插入实际值.

这对我来说很好用，这是一个例子:

This works fine for me, here is an example:

Connection oracleConnection = ...

byte[] testArray = ...

PreparedStatement ps = oracleConnection.prepareStatement(
    "insert into test (id, blobfield) values(?, empty_blob())");
ps.setInt(1, 100);
ps.executeUpdate();
ps.close();
ps = oracleConnection.prepareStatement(
    "select blobfield from test where id = ? for update");
ps.setInt(1, 100);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
if (rs.next()) {
    BLOB blob = (BLOB) rs.getBLOB(1);
    OutputStream outputStream = blob.setBinaryStream(0L);
    InputStream inputStream = new ByteArrayInputStream(testArray);
    byte[] buffer = new byte[blob.getBufferSize()];
    int byteread = 0;
    while ((byteread = inputStream.read(buffer)) != -1) {
        outputStream.write(buffer, 0, byteread);
    }
    outputStream.close();
    inputStream.close();
}

在某些网页上，作者建议使用更简单的 1 步解决方案.此解决方案的上一个示例:

There are some webpages where the authors suggest using a simpler 1-step solution. Previous example with this solution:

Connection oracleConnection = ...

byte[] testArray = ...

PreparedStatement ps = oracleConnection.prepareStatement(
    "insert into test(id, blobfield) values(?, ?)");
BLOB blob = BLOB.createTemporary(oracleConnection, false, BLOB.DURATION_SESSION);
OutputStream outputStream = blob.setBinaryStream(0L);
InputStream inputStream = new ByteArrayInputStream(testArray);
byte[] buffer = new byte[blob.getBufferSize()];
int byteread = 0;
while ((byteread = inputStream.read(buffer)) != -1) {
    outputStream.write(buffer, 0, byteread);
}
outputStream.close();
inputStream.close();

ps.setInt(1, 100);
ps.setBlob(2, blob);
ps.executeUpdate();
ps.close();

第二个代码要容易得多，所以我的问题是:第一个(流行的)解决方案的重点是什么?第二个解决方案(Oracle 服务器版本号、jdbc 驱动程序版本、blob 的大小……)是否存在(是否存在)某种限制?第一个解决方案是否更好(速度、内存消耗等)?有什么理由不使用更简单的第二种方法?

The second code is much more easier, so my question is: What is the point of first (popular) solution? Is there (was there) some kind of constraint for the second solution (Oracle server version number, jdbc driver version, size of the blob,...)? Is the first solution better (speed, memory consumption,...)? Any reasons for not using the simpler second approach?

完全相同的问题适用于 CLOB 字段.

The exact same question applies for CLOB fields.

推荐答案

您在第一种情况下提到的更新方法可以使用纯 JDBC 代码重写，从而减少您对特定于 Oracle 的类的依赖.如果您的应用需要与数据库无关，这可能会有所帮助.

The update approach you mention in the first case can be rewritten using pure JDBC code and thus reduce your dependency on Oracle-specific classes. This could be helpful if your app needs to be database agnostic.

public static void updateBlobColumn(Connection con, String table, String blobColumn, byte[] inputBytes, String idColumn, Long id) throws SQLException {
  PreparedStatement pStmt = null;
  ResultSet rs = null;
  try {
    String sql = 
      " SELECT " + blobColumn + 
      " FROM " + table + 
      " WHERE " + idColumn + " = ? " +
      " FOR UPDATE";
    pStmt = con.prepareStatement(sql, 
      ResultSet.TYPE_FORWARD_ONLY, 
      ResultSet.CONCUR_UPDATABLE);
    pStmt.setLong(1, id);
    rs = pStmt.executeQuery();
    if (rs.next()) {
      Blob blob = rs.getBlob(blobColumn);
      blob.truncate(0);
      blob.setBytes(1, inputBytes);
      rs.updateBlob(blobColumn, blob);
      rs.updateRow();
    }
  }
  finally {
    if(rs != null) rs.close();
    if(pStmt != null) pStmt.close();
  }
}

对于 MSSQL，我知道锁定语法是不同的:

For MSSQL I understand that the locking syntax is different:

String sql = 
  " SELECT " + blobColumn + 
  " FROM " + table + " WITH (rowlock, updlock) " + 
  " WHERE " + idColumn + " = ? "

这篇关于过于复杂的 oracle jdbc BLOB 处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！