从SQL DML返回数据的多种方式

在SQL中最难标准化的事情可能是RETURNING DML语句的数据。在这篇文章中，我们将看看用jOOQ做这件事的各种方法，在许多jOOQ支持的方言中，以及直接用JDBC。

如何用jOOQ来做

假设是来自sakila数据库的普通表：

CREATE TABLE actor (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  first_name TEXT,
  last_name TEXT,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

jOOQ从Firebird、MariaDB、PostgreSQL、Oracle PL/SQL中获得了语法上的灵感，这些数据库有相当直观的语法。在任何DML语句（即INSERT,UPDATE,DELETE ）中，你可以直接附加RETURNING ，像这样：

INSERT INTO actor (first_name, last_name)
VALUES ('John', 'Doe')
RETURNING id, last_update

总之，RETURNING 子句的作用与任何投影一样，也就是你通常用SELECT 。这意味着，你也可以直接返回一切：

INSERT INTO actor (first_name, last_name)
VALUES ('John', 'Doe')
RETURNING *

因此，在jOOQ中，这只是转化为

ActorRecord actor
ctx.insertInto(ACTOR, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .values("John", "Doe")
   .returning()
   .fetchOne();

或者，使用returningResult() 来返回任意的投影。我们已经看到了这是如何翻译成的。

• Firebird
• MariaDB
• PostgreSQL（以及相关的方言，如CockroachDB、YugabyteDB)
• Oracle PL/SQL

让我们来看看其他公司如何支持该语法的一些细节。

PL/SQL中是如何支持的

虽然Oracle PL/SQL本身支持该语法，但它并不完全等同于PostgreSQL的语法。首先，当jOOQ知道 它只插入一条记录时，它通过以下方式将数据的返回委托给JDBC驱动 [Statement.RETURN_GENERATED_KEYS](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Statement.html#RETURN_GENERATED_KEYS).所以生成的SQL没有任何RETURNING 子句的痕迹（尽管ojdbc以后会添加它）。更多细节见下文。

但当jOOQ不知道行数时，或者当它知道你插入的是多行时，就会退回到一个更复杂的模拟（实际内容可能不同）：

DECLARE

  -- Variables for input data
  i0 DBMS_SQL.VARCHAR2_TABLE;
  i1 DBMS_SQL.VARCHAR2_TABLE;

  -- Variables for output data
  o0 DBMS_SQL.VARCHAR2_TABLE;
  o1 DBMS_SQL.TIMESTAMP_TABLE;
  c0 sys_refcursor;
  c1 sys_refcursor;
BEGIN

  -- Input data
  i0(1) := ?;
  i0(2) := ?;
  i1(1) := ?;
  i1(2) := ?;

  -- Use Oracle's FORALL statement for bulk insertion
  FORALL i IN 1 .. i0.count
    INSERT INTO actor (first_name, last_name)
    VALUES (i0(i), i1(i))
    RETURNING id, last_update
    BULK COLLECT INTO o0, o1;

  -- Fetch the update count
  ? := sql%rowcount;

  -- Fetch the returned data
  OPEN c0 FOR SELECT * FROM table(o0);
  OPEN c1 FOR SELECT * FROM table(o1);
  ? := c0;
  ? := c1;
END;

生成这个SQL，以及实现正确的JDBC API调用顺序以绑定所有的输入和返回所有的输出，是一项相当费力的工作。当然，这不是你想手动编写的东西。

我仍然希望Oracle能够改进他们对组合各种语法特征的支持，以使上述变通方法过时。这些在PostgreSQL中都不需要，它在数据库内部支持几乎相同的语法。

在Db2、H2、标准SQL中是如何支持这些的

SQL标准有一个原生的语法，它比PostgreSQL的稍微强大一些，尽管我说可读性相当差。它被称为<data change delta table> ，它看起来像这样。

SELECT id, last_update
FROM FINAL TABLE (
  INSERT INTO actor (first_name, last_name)
  VALUES ('John', 'Doe')
) a

所以，不是调整INSERT 语句的语法，而是有一个{ OLD | NEW | FINAL } TABLE 操作符，它以一个DML语句为参数，并带有以下修饰语。

• OLD:返回在任何默认/触发器生成的值之前的数据，或者在UPDATE,DELETE 的数据之前的数据，如果该类型的语句被执行的话。
• NEW:返回任何默认值或UPDATE （如果执行了该类型的语句）之后的数据，但在触发器生成值之前的数据。
• FINAL：返回实际插入的数据，即在所有触发器生成的值之后，或者在执行该类型语句的UPDATE 之后。

然后，你可以直接在SQL中进一步处理这种插入的结果，但有一些限制（例如，禁止一些连接、集合操作、聚合，但你可以过滤和预测）。

这些关键词最强大的用途是与UPDATE 语句一起使用，因为它可以访问实际UPDATE 之前或之后的数据。

遗憾的是，你必须对单个关键词下定决心。你不能在一个语句中访问所有3个版本的数据（见SQL Server的OUTPUT ，进一步说，它可以做到这一点），例如，在实现UPDATE 审计日志时。

注意，与RETURNING 不同，这个语法也适用于MERGE

让我们再一次回到PostgreSQL上来

如果你想做和上面一样强大的事情，在PostgreSQL中，有一个神秘的语法特性，你可以把RETURNING 语句放在CTE（WITH 子句）中，如下所示。

WITH
  a (id, last_update) AS (
    INSERT INTO actor (first_name, last_name)
    VALUES ('John', 'Doe')
    RETURNING id, last_update
  )
SELECT *
FROM a;

奇怪的是，你不能在一个派生表中做同样的事情。例如，这样做是不行的，即使在其他方面，CTE和派生表在逻辑上基本等同。

-- Doesn't work:
SELECT *
FROM (
  INSERT INTO actor (first_name, last_name)
  VALUES ('John', 'Doe')
  RETURNING id, last_update
) a (id, last_update);

其他模仿PostgreSQL语法的方言不支持上述内容，即Firebird、MariaDB和Oracle PL/SQL。

SQL Server的OUTPUT子句

SQL Server有一个OUTPUT 子句，从语法上看可能有点奇怪，但它甚至比Db2的FINAL TABLE 更强大一些，因为它允许同时访问修改前和 修改后的 数据。

为此，SQL Server引入了INSERTED 和DELETED 伪表，它们包含了UPDATE 之后或之前的数据。

缺点是，在SQL Server中没有原生的方法来访问触发器生成的值，所以jOOQ的模拟要更精细一些。

-- Declare an in-memory table for the results
DECLARE @result TABLE (
  id INT,
  last_update DATETIME2
);

-- Insert the data and return the results into the in-memory table
INSERT INTO actor (first_name, last_name)
OUTPUT inserted.id, inserted.last_update
INTO @result
VALUES ('John', 'Doe');

-- Merge trigger generated values into the previous results
MERGE INTO @result r
USING (
  SELECT actor.id, actor.last_update AS x
  FROM actor
) s
ON r.id = s.id
WHEN MATCHED THEN UPDATE SET last_update = s.x;

-- Return the results to the client
SELECT id, last_update
FROM @result;

同样，实际的SQL可能会有一些不同，但你会明白这一点。这一切都需要完成，以便能够获取触发器生成的值。目前，jOOQ的运行时还没有意识到有触发器的表，尽管这在未来可能会因为#13912而改变。

请注意，与RETURNING 不同，这种语法也适用于MERGE

使用JDBC来获取生成的键（Oracle，HSQLDB）

正如上面提到的Oracle，我们也可以使用JDBC来获取生成的密钥。原则上，它是这样工作的。

try (PreparedStatement s = c.prepareStatement(
    "INSERT INTO actor (first_name, last_name) VALUES (?, ?)", 
    new String[] { "ID", "LAST_UPDATE" }
)) {
    s.setString(1, firstName);
    s.setString(2, lastName);
    s.executeUpdate();

    try (ResultSet rs = s.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println("ID = " + rs.getInt(1));
            System.out.println("LAST_UPDATE = " + rs.getTimestamp(2));
        }
    }
}

不幸的是，除了HSQLDB和Oracle之外，几乎没有JDBC驱动实现这个功能

使用JDBC来获取生成的键（其他）

在大多数其他SQL方言中，我们必须做这样的事情。

try (PreparedStatement s = c.prepareStatement(
    "INSERT INTO actor (first_name, last_name) VALUES (?, ?)", 
    Statement.RETURN_GENERATED_KEYS
)) {
    s.setString(1, firstName);
    s.setString(2, lastName);
    s.executeUpdate();

    try (ResultSet rs = s.getGeneratedKeys()) {
        System.out.println("ID = " + rs.getInt(1));
        
        // But there's no way to access LAST_UPDATE here. We'll
        // have to run another query
    }
}

许多JDBC驱动以某种方式支持这种方式，但不是所有的驱动都支持这种方式。

• 对于多个插入的行
• 对于除此之外的语句INSERT
• 对于没有标识的表，或者标识不是主键的表

结论

和以往一样，各种SQL供应商之间的差异是巨大的，无论是在以下方面：

• SQL支持
• JDBC支持

jOOQ已经为你破解了JDBC，所以你不必再做。使用jOOQ，上述所有的工作通常是这样的，在所有的方言中，至少在你插入单行时是这样的。

ActorRecord actor
ctx.insertInto(ACTOR, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .values("John", "Doe")
   .returning()
   .fetchOne();