|previous : next|
INSERT .. RETURNING
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The Postgres database has native support for an INSERT .. RETURNING clause. This is a very powerful concept that is emulated for all other dialects using JDBC's getGeneratedKeys() method. Take this example:
// Add another author, with a generated ID Record record = create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .values("Charlotte", "Roche") .returning(AUTHOR.ID) .fetchOne(); System.out.println(record.getValue(AUTHOR.ID)); // For some RDBMS, this also works when inserting several values // The following should return a 2x2 table Result<?> result = create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .values("Johann Wolfgang", "von Goethe") .values("Friedrich", "Schiller") // You can request any field. Also trigger-generated values .returning(AUTHOR.ID, AUTHOR.CREATION_DATE) .fetch();
Some databases have poor support for returning generated keys after INSERTs. In those cases, jOOQ might need to issue another SELECT statement in order to fetch an @@identity value. Be aware, that this can lead to race-conditions in those databases that cannot properly return generated ID values. For more information, please consider the jOOQ Javadoc for the returning() clause.