The Many Methods to Return Information From SQL DML – Java, SQL and jOOQ.

In all probability the toughest factor to standardise in SQL is RETURNING knowledge from DML statements. On this article, we’ll take a look at numerous methods of doing that with jOOQ, in lots of jOOQ’s supported dialects, and with JDBC straight.

Easy methods to do it with jOOQ

Assuming the standard desk from the sakila database:

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

jOOQ took syntactic inspiration from Firebird, MariaDB, PostgreSQL, Oracle PL/SQL, which have fairly the intuitive syntax. On any DML assertion (i.e. INSERT, UPDATE, DELETE), you may simply append RETURNING, like this:

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

In abstract, the RETURNING clause acts like every projection, i.e. what you often do with SELECT. Which means, you may also simply return all the things:

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

So, in jOOQ, this simply interprets to

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

Alternatively, use returningResult() to return arbitrary projections. We’ve already seen how that is translated to:

• Firebird
• MariaDB
• PostgreSQL (and associated dialects, comparable to CockroachDB, YugabyteDB)
• Oracle PL/SQL

Let’s look into some particulars of how others assist the syntax:

How that is supported in PL/SQL

Whereas Oracle PL/SQL helps the syntax per se, it isn’t precisely equal to PostgreSQL’s. First off, when jOOQ is aware of that it’s solely inserting a single row, then it delegates the returning of information to the JDBC driver through Assertion.RETURN_GENERATED_KEYS. So the generated SQL doesn’t have any hint of the RETURNING clause (though ojdbc will add it afterward). See under for extra particulars.

However when jOOQ doesn’t know the variety of rows, or when it is aware of you’re inserting a couple of row, then it falls again to a way more refined emulation (precise contents could differ):

DECLARE

  -- Variables for enter knowledge
  i0 DBMS_SQL.VARCHAR2_TABLE;
  i1 DBMS_SQL.VARCHAR2_TABLE;

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

  -- Enter knowledge
  i0(1) := ?;
  i0(2) := ?;
  i1(1) := ?;
  i1(2) := ?;

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

  -- Fetch the replace depend
  ? := sqlpercentrowcount;

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

It’s fairly the laborious job to generate this SQL, in addition to to implement the proper sequence of JDBC API calls to bind all inputs and return all outputs. Definitely not one thing you’d wish to write manually.

I’m nonetheless hoping Oracle will enhance their assist for combining numerous syntactic options to make the above workarounds out of date. None of that is vital in PostgreSQL, which helps nearly the identical syntax inside the database.

How that is supported in Db2, H2, customary SQL

The SQL customary has a local syntax for this, and it’s barely extra highly effective than PostgreSQL’s, though I’d say fairly much less readable. It’s known as the <knowledge change delta desk>, and it appears to be like one thing like this:

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

So, as a substitute of tweaking the INSERT assertion syntax, there’s a FINAL TABLE operator, which takes a DML assertion as argument with the next modifiers:

• OLD: returns the information because it was previous to any default / set off generated values or earlier than the UPDATE, DELETE of information, in case that sort of assertion was executed.
• NEW: returns the information because it was after any default or the UPDATE in case that sort of assertion was executed, however earlier than set off generated values
• FINAL: returns the information because it was really inserted, i.e. in any case set off generated values, or after the UPDATE in case that sort of assertion was executed

You possibly can then additional course of outcomes from such inserts straight in SQL, with just a few limitations (e.g. some joins, set operations, aggregations are forbidden, however you may filter and venture).

Probably the most highly effective use-case for these key phrases is when used with an UPDATE assertion, because it provides entry to knowledge earlier than or after the precise UPDATE was made.

Regrettably, you need to make up your thoughts a few single key phrase. You can not entry all 3 variations of the information in a single assertion (see SQL Server’s OUTPUT additional down, which might do that), e.g. when implementing an UPDATE audit log.

Observe that not like RETURNING, this syntax additionally works with MERGE

Let’s get again to PostgreSQL as soon as extra

If you wish to do one thing as highly effective because the above, in PostgreSQL, there’s an arcane syntactic function the place you may place RETURNING statements in a CTE (WITH clause) as follows:

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

Curiously, you may’t do the identical factor in a derived desk. E.g. this received’t work, even when in any other case, CTE and derived tables are largely logically equal:

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

The opposite dialects that mimicked PostgreSQL’s syntax don’t assist the above, i.e. Firebird, MariaDB, and Oracle PL/SQL.

SQL Server’s OUTPUT clause

SQL Server has an OUTPUT clause that may be a bit bizarre, syntactically, nevertheless it’s additionally a bit extra highly effective even than Db2’s FINAL TABLE because it permits to entry knowledge each from earlier than and after the modifications on the similar time.

For this, SQL Server introduces INSERTED and DELETED pseudo tables, which include the information after or earlier than the UPDATE.

The downside is that there’s no native technique to entry set off generated values in SQL Server, so jOOQ’s emulation is a little more elaborate,

-- Declare an in-memory desk for the outcomes
DECLARE @consequence TABLE (
  id INT,
  last_update DATETIME2
);

-- Insert the information and return the outcomes into the in-memory desk
INSERT INTO actor (first_name, last_name)
OUTPUT inserted.id, inserted.last_update
INTO @consequence
VALUES ('John', 'Doe');

-- Merge set off generated values into the earlier outcomes
MERGE INTO @consequence 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 outcomes to the consumer
SELECT id, last_update
FROM @consequence;

Once more, the precise SQL could differ a bit, however you get the purpose. This all must be carried out to have the ability to fetch set off generated values. At present, jOOQ’s runtime isn’t conscious of tables having triggers, though which may change sooner or later with #13912.

Observe that not like RETURNING, this syntax additionally works with MERGE

Utilizing JDBC to fetch generated keys (Oracle, HSQLDB)

As talked about above for Oracle, we are able to additionally use JDBC to fetch generated keys. In precept, that is the way it works:

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

    strive (ResultSet rs = s.getGeneratedKeys()) {
        whereas (rs.subsequent()) {
            System.out.println("ID = " + rs.getInt(1));
            System.out.println("LAST_UPDATE = " + rs.getTimestamp(2));
        }
    }
}

Sadly, other than HSQLDB and Oracle, nearly no JDBC driver implements this!

Utilizing JDBC to fetch generated keys (others)

In most different SQL dialects, now we have to do one thing like this:

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

    strive (ResultSet rs = s.getGeneratedKeys()) {
        System.out.println("ID = " + rs.getInt(1));
        
        // However there isn't any technique to entry LAST_UPDATE right here. We'll
        // should run one other question
    }
}

Many JDBC drivers assist this ultimately, however not all of them assist this strategy:

• For a number of inserted rows
• For statements aside from INSERT
• For tables with out an identification, or the place the identification isn’t the first key

Conclusion

As ever so typically, the variations between numerous SQL distributors are huge, each by way of:

jOOQ has been hacking JDBC for you, so that you don’t should. With jOOQ, the entire above sometimes works like this, on all dialects, no less than if you’re inserting a single row:

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