Wednesday, October 5, 2022
HomeJavaThe Many Methods to Return Information From SQL DML – Java, SQL...

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 have a look at numerous methods of doing that with jOOQ, in a lot of jOOQ’s supported dialects, and with JDBC immediately.

Find out how 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 several projection, i.e. what you often do with SELECT. Meaning, you may also simply return every little thing:

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, similar 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 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 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 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. 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 needed in PostgreSQL, which helps virtually the identical syntax within the database.

How that is supported in Db2, H2, normal SQL

The SQL normal 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 referred to as the <knowledge change delta desk>, and it seems 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 information because it was previous to any default / set off generated values or earlier than the UPDATE, DELETE of information, in case that kind of assertion was executed.
  • NEW: returns the information 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 information because it was truly inserted, i.e. in spite of everything 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 immediately in SQL, with a number of limitations (e.g. some joins, set operations, aggregations are forbidden, however you may filter and challenge).

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

Regrettably, you must make up your thoughts a few single key phrase. You can’t 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.

Be aware 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 characteristic 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 gained’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 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 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 method 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 accomplished 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.

Be aware 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 will 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, virtually no JDBC driver implements this!

Utilizing JDBC to fetch generated keys (others)

In most different SQL dialects, we’ve got 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 is no method to entry LAST_UPDATE right here. We'll
        // need to run one other question
    }
}

Many JDBC drivers assist this in a roundabout way, however not all of them assist this strategy:

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

Conclusion

As ever so usually, the variations between numerous SQL distributors are huge, each when it comes to:

jOOQ has been hacking JDBC for you, so that you don’t need to. With jOOQ, all the above usually works like this, on all dialects, not less than while you’re inserting a single row:

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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments