One of jOOQ’s major features is to take the most useful SQL constructs and clauses from any RDBMS and make them available to other SQL dialects, as well. This had been done previously with MySQL’s
INSERT .. ON DUPLICATE KEY UPDATE
construct, which can be easily simulated with the more powerful SQL standard
MERGE
statement, or using procedural SQL blocks where this is supported.
A recent request made me think about Postgres’
INSERT .. RETURNING
clause, which is probably the most intuitive and concise way of returning generated keys from an insert statement. The importance of doing that becomes clear in the context of a jOOQ UpdatableRecord, which, when inserted, should refresh its IDENTITY, or Primary Key value. With jOOQ 1.6.4, this was done in a bit “risky” way, by just fetching the MAX(PK) value from the table, immediately after insert. Obviously, this can go terribly wrong in a highly concurrent system, when other transactions are committed earlier.
But how to map
INSERT .. RETURNING
to other RDBMS? Here’s a quick overview over what’s supported in which database / JDBC driver:
Vendor-specific SQL syntax support
On Postgres and DB2, you can also execute this for INSERT statements:
ResultSet rs = statement.executeQuery();
The SQL syntax to fetch a java.sql.ResultSet from an INSERT statement works like this:
-- Postgres
INSERT INTO .. RETURNING *
-- DB2
SELECT * FROM FINAL TABLE (INSERT INTO ..)
Oracle also knows of a similar clause
INSERT INTO .. RETURNING INTO ?, ?, ...
This PL/SQL extension cannot be executed easily with standard JDBC, though. Either, it has to be wrapped in a PL/SQL block and executed as a java.sql.CallableStatement, or by casting the prepared statement into an
OraclePreparedStatement
and registering return values, as described here:
https://stackoverflow.com/questions/682539/return-rowid-parameter-from-insert-statement-using-jdbc-connection-to-oracle
Optimal JDBC support
Some RDBMS have “optimal” support for returning values after an INSERT statement. By “optimal”, I mean that all table columns can be returned, regardless of whether they are actual keys or not. These RDBMS are: HSQLDB, Oracle, DB2.
If this is supported by the JDBC driver, then the simulation of the Postgres
INSERT .. RETURNING
clause is very simple, as the requested fields can be passed at prepared statement initialisation time:
// Watch out for case-sensitivity!
String[] columnNames = // [...] transform RETURNING clause
PreparedStatement stmt = connection.prepareStatement(sql, columnNames);
Limited JDBC support
Other RDBMS have “limited” support for returning values. This means that only generated IDENTITY (AUTO_INCREMENT) values will be returned. This applies to: Derby, H2, MySQL, SQL Server.
PreparedStatement stmt = connection.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
If more than the IDENTITY column value is requested in the simulated
INSERT .. RETURNING
clause, then an additional SELECT statement has to be issued right after. If transactions are properly handled by client code (i.e. the SELECT will run in the same transaction as the INSERT), then no race conditions can occur and the behaviour of this is correct.
No JDBC support
Unfortunately, there are also JDBC drivers that do not support returning values from INSERT statements. The affected RDBMS are: Sybase, SQLite. In a future version of jOOQ, the
INSERT .. RETURNING
clause can be simulated in three steps:
- INSERT
- Fetch Sybase @@identity / SQLite last_insert_rowid()
- Fetch other requested columns
Like this:
Like Loading...
Are you aware of a way to mimic “Fetch Sybase @@identity” in H2 for unit testing?
I’d say, the H2 IDENTITY() function closely corresponds to Sybase’s @@identity variable:
http://www.h2database.com/html/functions.html?highlight=identity&search=identity#identity
Have you had any luck with have SQL using @@IDENTITY in unit tests against H2?
Stated another way, is there a way to mimic Sybase syntax with H2 (or any other in memory database)?
I was kind of hoping “CREATE ALIAS @@IDENTITY for IDENTITY()” would work but H2 did not like that.
http://www.h2database.com/html/grammar.html#create_alias
Thanks for the prompt response.
I’m afraid you’ll have to ask Thomas Mueller directly, on the H2 user group, for more details. Or maybe, you might be able to patch H2 yourself. I’d advise against it, though. Maybe you could try using jOOQ instead? It’ll handle fetching of identity values for you, compatibly between H2 and Sybase…
Please edit article and leave some simple note for hsqldb, that second parameter of prepareStatement (array of column names) is case sensitive. It is not same as in postgresql jdbc, etc… (see https://sourceforge.net/p/hsqldb/bugs/1364/). You you usually get “java.sql.SQLSyntaxErrorException: user lacks privilege or object not found” error in standard workflow and than spend some hours tracking this thing down.
Thanks. It certainly makes sense to add this remark.
On the other hand, users should always be wary of case sensitivity in SQL / Java. It’s a shame that most SQL databases chose to be case insensitive by default, with respect to table / column names…