我想通过流式传输在Oracle表中的Blob进行查询。这个示例在DB2、SQL Server、PostgreSQL和 MySQL上运行良好，但在Oracle上会报错。我有如下表：

create table person (
  id number(12) primary key not null,
  name varchar2(20),
  photo blob
);

我想执行这个查询：

SELECT id FROM person WHERE photo = ?

我现在的代码是：

try (InputStream is = new FileInputStream("photos/photo1.png")) {
  try (Connection conn = getConnection()) {
    try (PreparedStatement ps = conn.prepareStatement(
         "SELECT id FROM person WHERE photo = ?");) {
      ps.setBinaryStream(1, is);
      try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
          // do something
        }
      }
    }
  }
}

但它会报错：

java.sql.SQLSyntaxErrorException: ORA-00932:
    inconsistent datatypes: expected - got CLOB
https://docs.oracle.com/error-help/db/ora-00932/

这里提到的页面很遗憾对解决问题并没有太大帮助。

解决方案

与问题 如何创建BLOB对象？ 类似，使用 Connection.createBlob() 来创建一个 Blob 对象，然后使用 Blob.setBinaryStream()，并将 Blob 赋给预处理语句（PreparedStatement）：

try (
  InputStream is = new FileInputStream("photos/photo1.png");
  Connection conn = getConnection();
  PreparedStatement ps = conn.prepareStatement(
    "SELECT id FROM person WHERE DBMS_LOB.COMPARE(photo, ?) = 0"
  );
  Blob blob = conn.createBlob();
) {
  byte[] buffer = new byte[10 * 1024];
  int nread = 0;

  try ( OutputStream os = blob.setBinaryStream(1) )
  {
    while ((nread = is.read(buffer)) != -1) {
      os.write(buffer, 0, nread);
    }
  }

  ps.setBlob(1, blob);
  try (ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
      // do something
    }
  }
  blob.free();
}