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

Most likely the toughest factor to standardise in SQL is RETURNING information from DML statements. On this article, we’ll have a look at varied methods of doing that with jOOQ, in lots of jOOQ’s supported dialects, and with JDBC straight.

do it with jOOQ

Assuming the same old 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’ll be able to 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 all projection, i.e. what you often do with SELECT. Which means, you may as well simply return the whole lot:

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, corresponding to CockroachDB, YugabyteDB)
• Oracle PL/SQL

Let’s look into some particulars of how others help 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 knowledge to the JDBC driver by way of Assertion.RETURN_GENERATED_KEYS. So the generated SQL doesn’t have any hint of the RETURNING clause (though ojdbc will add it afterward). See beneath for extra particulars.

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

DECLARE

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

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

  -- Enter information
  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 information
  OPEN c0 FOR SELECT * FROM desk(o0);
  OPEN c1 FOR SELECT * FROM desk(o1);
  ? := c0;
  ? := c1;
END;

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

I’m nonetheless hoping Oracle will enhance their help for combining varied syntactic options to make the above workarounds out of date. None of that is vital in PostgreSQL, which helps nearly the identical syntax within 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 , and it seems to be 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 NEW TABLE operator, which takes a DML assertion as argument with the next modifiers:

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

You’ll be able to 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’ll be able to filter and challenge).

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

Regrettably, it’s important to make up your thoughts a couple of single key phrase. You can’t entry all 3 variations of the info in a single assertion (see SQL Server’s OUTPUT additional down, which may do that), e.g. when implementing an UPDATE audit log.

Notice that in contrast to 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 characteristic the place you’ll be able to 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’ll be able to’t do the identical factor in a derived desk. E.g. this gained’t work, even when in any other case, CTE and derived tables are largely logically equal:

-- Does 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 help the above, i.e. Firebird, MariaDB, and Oracle PL/SQL.

SQL Server’s OUTPUT clause

SQL Server has an OUTPUT clause that is likely to 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 information each from earlier than and after the modifications on the identical time.

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

The disadvantage is that there’s no native approach 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 @outcome TABLE (
  id INT,
  last_update DATETIME2
);

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

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

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

Notice that in contrast to RETURNING, this syntax additionally works with MERGE

Utilizing JDBC to fetch generated keys (Oracle, HSQLDB)

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

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

    attempt (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, we have now to do one thing like this:

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

    attempt (ResultSet rs = s.getGeneratedKeys()) {
        System.out.println("ID = " + rs.getInt(1));
        
        // However there is no approach to entry LAST_UPDATE right here. We'll
        // need to run one other question
    }
}

Many JDBC drivers help this not directly, however not all of them help this method:

• For a number of inserted rows
• For statements apart 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 varied SQL distributors are huge, each by way of:

jOOQ has been hacking JDBC for you, so that you don’t need to. With jOOQ, all the above sometimes works like this, on all dialects, at the least if you’re inserting a single row:

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