Java 过于复杂的 oracle jdbc BLOB 处理

Overcomplicated oracle jdbc BLOB handling

提问by asalamon74

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

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

1. insert empty_blob()value.
2. select the row with for update.
3. insert the real value.

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();
}

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

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

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();

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?

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

The exact same question applies for CLOB fields.

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

回答by skaffman

The Oracle server's LOB handling is pretty poor and can suffer from serious performance problems (e.g. massive overuse of the redo log), so the first solution may be a way to address those.

Oracle 服务器的 LOB 处理非常差，可能会遇到严重的性能问题（例如重做日志的大量过度使用），因此第一个解决方案可能是解决这些问题的方法。

I would suggest trying both approaches. if you have a competent DBA, they may be able to advise which approach has the lowest impact on the server.

我建议尝试两种方法。如果您有称职的 DBA，他们可能会建议哪种方法对服务器的影响最小。

回答by u7867

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.

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

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();
  }
}

For MSSQL I understand that the locking syntax is different:

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

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

回答by Brian

One interesting thing with JDBC is you can upgrade rather aggressively to the latest drivers and work with JDBC 4.0 features. The oracle JDBC drivers will work with older database versions, so you can use an 11g branded JDBC driver against a 10g database. The Oracle database 11g JDBC comes in two flavors: ojdbc5.jar for Java 5 (i.e., JDK 1.5) and ojdbc6.jar for Java 6 (i.e., JDK 1.6). The ojdbc6.jar supports the new JDBC 4.0 specification.

使用 JDBC 的一件有趣的事情是您可以相当积极地升级到最新的驱动程序并使用 JDBC 4.0 功能。oracle JDBC 驱动程序适用于较旧的数据库版本，因此您可以针对 10g 数据库使用 11g 品牌的 JDBC 驱动程序。Oracle 数据库 11g JDBC 有两种版本：用于 Java 5（即 JDK 1.5）的 ojdbc5.jar 和用于 Java 6（即 JDK 1.6）的 ojdbc6.jar。ojdbc6.jar 支持新的 JDBC 4.0 规范。

With the newer drivers/jdbc 4.0 you can create Blobs and Clobs off the connection object:

使用较新的驱动程序/jdbc 4.0，您可以从连接对象创建 Blob 和 Clob：

Blob aBlob = con.createBlob();
int numWritten = aBlob.setBytes(1, val);

回答by Yinch

This statement :

这个说法 ：

blob.setBytes(1, inputBytes);

is giving issues when I use oracle thin client ojdbc14.jar, "Unsupported Features"

当我使用 oracle 瘦客户端 ojdbc14.jar 时出现问题，“不支持的功能”

So, I had to work around by :

所以，我不得不解决：

rset.next();
Blob bobj = rset.getBlob(1);
BLOB object = (BLOB) bobj;
int chunkSize = object.getChunkSize();
byte[] binaryBuffer = new byte[chunkSize];
int position = 1;
int bytesRead = 0;
int bytesWritten = 0, totbytesRead = 0, totbytesWritten = 0;
InputStream is = fileItem.getInputStream();
while ((bytesRead = is.read(binaryBuffer)) != -1) {
bytesWritten = object.putBytes(position, binaryBuffer, bytesRead);
position += bytesRead;
totbytesRead += bytesRead;
totbytesWritten += bytesWritten;
is.close();

回答by Quartz

Provided the CLOB data is small enough to fit in your memory without blowing up, you can just create a prepared statement and simply call

如果 CLOB 数据足够小以适合您的内存而不会爆炸，您只需创建一个准备好的语句并简单地调用

ps.setString(1, yourString);

There may be other size limitations, but it seems to work for the sizes we're dealing with (500kB max).

可能还有其他大小限制，但它似乎适用于我们正在处理的大小（最大 500kB）。

回答by Ivan

Another point of view from Oracle DBA. Sun guys did very poor job when they designed JDBC standards(1.0, 2.0, 3.0, 4.0). BLOB stands for large object and therefore it can be very large. It is something that can not be stored in JVM heap. Oracle thinks of BLOBs as something like file handles(it fact they are call then "lob locators"). LOBS can not be created via constructor and are not Java objects. Also LOB locators(oracle.sql.BLOB) can not be created via constructor - they MUST be created in the DB side. In Oracle there are two ways how to create a LOB.

另一个观点来自 Oracle DBA。Sun 人在设计 JDBC 标准（1.0、2.0、3.0、4.0）时做得很差。BLOB 代表大对象，因此它可以非常大。它是无法存储在 JVM 堆中的东西。Oracle 认为 BLOB 类似于文件句柄（事实上它们被称为“lob 定位器”）。LOBS 不能通过构造函数创建，也不是 Java 对象。此外，LOB 定位器(oracle.sql.BLOB) 不能通过构造函数创建 - 它们必须在 DB 端创建。在 Oracle 中有两种创建 LOB 的方法。

1. DBMS_LOB.CREATETEMPORATY - the returned locator in this case points into temporary tablespace. All the writes/reads against this locator will be sent via network onto DB server. Nothing is stored in JVM heap.

2. Call to EMPTY_BLOB function. INSERT INTO T1(NAME, FILE) VALUES("a.avi", EMPTY_BLOB()) RETURNING FILE INTO ?; In this case returned lob locator points into data tablespace. All the writes/reads against this locator will be sent via network onto DB server. All the writes are "guarded" by writes into redo-logs. Nothing is stored in JVM heap. The returning clause was not supported by JDBC standards (1.0, 2.0), therefore you can find many examples on the internet where people recommend approach of two steps: "INSERT...; SELECT ... FOR UPDATE;"

1. DBMS_LOB.CREATETEMPORATY - 在这种情况下返回的定位器指向临时表空间。针对此定位器的所有写入/读取都将通过网络发送到数据库服务器。JVM 堆中不存储任何内容。

2. 调用 EMPTY_BLOB 函数。INSERT INTO T1(NAME, FILE) VALUES("a.avi", EMPTY_BLOB()) RETURNING FILE INTO ?; 在这种情况下，返回的 lob 定位器指向数据表空间。针对此定位器的所有写入/读取都将通过网络发送到数据库服务器。所有写入都通过写入重做日志来“保护”。JVM 堆中不存储任何内容。JDBC 标准（1.0、2.0）不支持返回子句，因此您可以在互联网上找到许多人们推荐两步方法的示例：“INSERT...; SELECT ... FOR UPDATE;”

Oracle lobs must be associated with some database connection, they can not be used when DB connection is lost/closed/(or "commited"). They can not be passed from one connection to another.

Oracle lob 必须与某些数据库连接相关联，当数据库连接丢失/关闭/（或“提交”）时不能使用它们。它们不能从一个连接传递到另一个连接。

You second example can work, but will require excessive copying if data from temporary tablespace into data tablespace.

您的第二个示例可以工作，但如果数据从临时表空间到数据表空间，则需要过度复制。

回答by Kirby

I found a simple call to setObject(pos, byte[])works for my case. From Database Programming with JDBC and JavaBy George Reese,

我发现一个简单的调用setObject(pos, byte[])适用于我的情况。来自George Reese 的Database Programming with JDBC and Java，

        byte[] data = null;
        stmt = con.prepareStatement("INSERT INTO BlobTest(fileName, "
            + "blobData) VALUES(?, ?)");
        stmt.setString(1, "some-file.txt");
        stmt.setObject(2, data, Types.BLOB);
        stmt.executeUpdate();

回答by j23

Some watchouts found for the second solution

为第二个解决方案找到了一些注意事项

I am using ojdbc6.jar - the latest release and for the statement from 'the second solution':

我正在使用 ojdbc6.jar - 最新版本和来自“第二个解决方案”的声明：

BLOB blob = BLOB.createTemporary(oracleConnection, false, BLOB.DURATION_SESSION);

I have to release blob after the statement is completed - or otherwise blob is closed when session is closed (which can take long time with connection pooling).

我必须在语句完成后释放 blob - 否则 blob 在会话关闭时关闭（连接池可能需要很长时间）。

blob.freeTemporary();

Otherwise you can see locked resources:

否则你可以看到锁定的资源：

select * from v$temporary_lobs

Another problem with temporary BLOBs is the need to allocate temporary tablespace: as per documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e18294.pdf

临时 BLOB 的另一个问题是需要分配临时表空间：根据文档http://docs.oracle.com/cd/E11882_01/appdev.112/e18294.pdf

Managing Temporary Tablespace for Temporary LOBs Temporary tablespace is used to store temporary LOB data

管理临时 LOB 的临时表空间 临时表空间用于存储临时 LOB 数据

回答by AVA

If size of inserting BLOB is greater than blob.getBufferSize(), transaction is commited as soon as first chunk is written to db as default value of autoCommit property of jdbc connection is trueand further chunks writes fail as db treats them as new transactions. It is suggested as follows:
a) Set jdbc connection autoCommit property to false.

如果插入 BLOB 的大小大于 blob.getBufferSize()，则在将第一个块写入 db 时立即提交事务，因为jdbc 连接的 autoCommit 属性的默认值为 true并且进一步的块写入失败，因为 db 将它们视为新事务。建议如下：
a) 将 jdbc 连接的 autoCommit 属性设置为 false。

conn.setAutoCommit(false);

b) Explicitely commit the transaction after uploading the whole BLOB.

b) 在上传整个 BLOB 后明确提交事务。

while ((bytesRead = messageInputStream.read(buffer)) != -1) {
     cumBytes += bytesRead;
     blobOutputStream.write(buffer, 0, bytesRead);
    }
conn.commit();