Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
IDENTITY values
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Many databases support the concept of IDENTITY values, or SEQUENCE-generated key values. This is reflected by JDBC's getGeneratedKeys() method. jOOQ abstracts using this method as many databases and JDBC drivers behave differently with respect to generated keys. Let's assume the following SQL Server BOOK table:
CREATE TABLE book ( ID INTEGER IDENTITY(1,1) NOT NULL, -- [...] CONSTRAINT pk_book PRIMARY KEY (id) )
If you're using jOOQ's code generator, the above table will generate a org.jooq.UpdatableRecord with an IDENTITY column. This information is used by jOOQ internally, to update IDs after calling store():
BookRecord book = create.newRecord(BOOK);
book.setTitle("1984");
book.store();
// The generated ID value is fetched after the above INSERT statement
System.out.println(book.getId());
Database compatibility
DB2, Derby, HSQLDB, Ingres
These SQL dialects implement the standard very neatly.
id INTEGER GENERATED BY DEFAULT AS IDENTITY id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
H2, MySQL, Postgres, SQL Server, Sybase ASE, Sybase SQL Anywhere
These SQL dialects implement identites, but the DDL syntax doesn’t follow the standard
-- H2 mimicks MySQL's and SQL Server's syntax ID INTEGER IDENTITY(1,1) ID INTEGER AUTO_INCREMENT -- MySQL and SQLite ID INTEGER NOT NULL AUTO_INCREMENT -- Postgres serials implicitly create a sequence -- Postgres also allows for selecting from custom sequences -- That way, sequences can be shared among tables id SERIAL NOT NULL -- SQL Server ID INTEGER IDENTITY(1,1) NOT NULL -- Sybase ASE id INTEGER IDENTITY NOT NULL -- Sybase SQL Anywhere id INTEGER NOT NULL IDENTITY
For databases where IDENTITY columns are only emulated (e.g. Oracle prior to 12c), the jOOQ generator can also be configured to generate synthetic identities.
Feedback
Do you have any feedback about this page? We'd love to hear it!