[ad_1]
Most likely the toughest factor to standardise in SQL is RETURNING information from DML statements. On this article, we’ll take a look at varied methods of doing that with jOOQ, in lots of jOOQ’s supported dialects, and with JDBC straight.
How you can 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 possibly can 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 normally do with SELECT. Meaning, you can too 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, 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 knowledge 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 in a while). See under 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 subtle 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.rely
INSERT INTO actor (first_name, last_name)
VALUES (i0(i), i1(i))
RETURNING id, last_update
BULK COLLECT INTO o0, o1;
-- Fetch the replace rely
? := 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. Definitely not one thing you’d wish to write manually.
I’m nonetheless hoping Oracle will enhance their assist for combining varied syntactic options to make the above workarounds out of date. None of that is needed in PostgreSQL, which helps virtually the identical syntax inside the database.
How that is supported in Db2, H2, commonplace SQL
The SQL commonplace 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 <information change delta desk>, 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 information because it was previous to any default / set off generated values or earlier than theUPDATE,DELETEof knowledge, in case that sort of assertion was executed.NEW: returns the information because it was after any default or theUPDATEin case that sort of assertion was executed, however earlier than set off generated valuesFINAL: returns the information because it was truly inserted, i.e. in any case set off generated values, or after theUPDATEin case that sort 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 possibly can filter and mission).
Essentially the most highly effective use-case for these key phrases is when used with an
UPDATEassertion, because it provides entry to information earlier than or after the preciseUPDATEwas made.Regrettably, you need to make up your thoughts a couple of single key phrase. You can not entry all 3 variations of the information in a single assertion (see SQL Server’s
OUTPUTadditional down, which might do that), e.g. when implementing anUPDATEaudit log.
Notice 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 possibly can 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 possibly can’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 could be a bit bizarre, syntactically, however 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 similar time.
For this, SQL Server introduces INSERTED and DELETED pseudo tables, which include the information after or earlier than the UPDATE.
The disadvantage is that there’s no native strategy 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 might 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 the moment, jOOQ’s runtime isn’t conscious of tables having triggers, though that may change sooner or later with #13912.
Notice 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:
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, aside from HSQLDB and Oracle, virtually 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:
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 strategy to entry LAST_UPDATE right here. We'll
// should run one other question
}
}
Many JDBC drivers assist this indirectly, however not all of them assist 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 should. With jOOQ, the entire above usually works like this, on all dialects, a minimum of while you’re inserting a single row:
ActorRecord actor
ctx.insertInto(ACTOR, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
.values("John", "Doe")
.returning()
.fetchOne();
[ad_2]
