SQL/JDBC 中的内联 BLOB/BINARY 数据类型

Question

假设我想避免在 JDBC 中使用绑定变量并使用“临时”语句运行 SQL，例如：

connection.createStatement().executeQuery("SELECT ...");

是否有任何约定/JDBC 转义语法来内联 BLOB 数据类型？我知道 H2 有这种语法：

INSERT INTO lob_table VALUES (X'01FF');

但这不是标准。有通用的解决方案吗？请注意，我对通用方法感兴趣。我知道这可能会变得非常低效。

Answer 1

可能没有 JDBC 转义语法，因此我进行了一些搜索，发现并成功测试了以下内容：

• SQL Server、Sybase ASE、Sybase SQL Anywhere

  插入 lob_table 值 (0x01FF)；
  

• DB2

  -- 使用 blob 构造函数。 VARCHAR FOR BIT DATA 类型不需要这样做
  INSERT INTO lob_table VALUES (blob(X'01FF'));
  

• Derby、H2、HSQLDB、Ingres、MySQL、SQLite

  INSERT INTO lob_table VALUES (X'01FF');
  

• Oracle

  -- 正如 a_horse_with_no_name 所提到的，请记住相对较低的
  -- Oracle 的 VARCHAR 类型限制为只能容纳 4000 个字节！
  插入 lob_table 值 (hextoraw('01FF'));
  

• Postgres

  -- 从 Postgres 9.0 开始还有十六进制编码
  ——不过，明确的演员阵容很重要
  INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);
  

  有关更多详细信息，请参阅 AH 的回答 Postgres 的十六进制编码

• SQL标准

  -- SQL 实际上定义了二进制文字 
  --（由 DB2、Derby、H2、HSQLDB、Ingres、MySQL、SQLite 实现）：
  <二进制字符串文字> ::=
    X<引用> [<空格>...] 
    [ { <退出>; [ <空格>... ] <退出> [ <空格>... ] }... ] <引用>
  
  <退出> ::=
    <数字> |一个 |乙| C | d |电子| F |一个 |乙| c | d |电子| f
  

Answer 2

我想在 Lukas 的回答中添加一些 PostgreSQL 特定的内容：

最短和最简单的解决方案是（因为 PostgreSQL至少 9.0）：

insert into lob_table (data) values( E'\\x0102030405FF' )

没有任何转换（如果该列已经是 bytea 之一），并且在开头只有 一个 \\x 标记。这是二进制数据类型。

关于 X'01FF' 语法：根据 字符串常量 文档 PostgreSQL 确实支持它 - 对于位字符串。而且似乎没有从位到字节的标准转换。

Answer 3

public String binaryLiteral(Connection con, byte[] bytes) {
    String databaseName = con.getMetaData().getDatabaseProductName();
    String binary = DatatypeConverter.printHexBinary(bytes);
    switch (databaseName) {
        case "Microsoft SQL Server":
        case "Sybase Anywhere": case "ASE": case "Adaptive Server Enterprise": // Sybase
            return "CONVERT(VARBINARY(MAX), '0x" + binary + "', 1)";
        case "Oracle":
            if (binary.length() <= 4000) {
                return "HEXTORAW('" + binary + "')";
            } else {
                // https://stackoverflow.com/questions/18116634/oracle-10-using-hextoraw-to-fill-in-blob-data/62162036#62162036
                return "CONCAT_RAWS(RAWS(" +
                        Streams.stream(Splitter.fixedLength(4000).split(binary))
                                .map(chunk -> "HEXTORAW('" + chunk + "')")
                                .collect(Collectors.joining(",")) +
                        "))";
            }
        case "PostgreSQL":
            return "E'\\\\x" + binary + "'";
        case "H2":
        // the following list is mostly untested
        case "HSQL Database Engine":
        case "Apache Derby":
        case "Ingres":
        case "MySQL":
        case "MariaDB":
        case "SQLite":
        case "Informix Dynamic Server":
        case "DB2":
        case "Firebird":
        default: // SQL Standard
            return "X'" + binary + "'";
    }

CONCAT_RAWS 和 RAWS 的来源在 Oracle 10：使用 HEXTORAW 填充 blob 数据。