How do I get the value of the last auto-generated key with Jooq?

[FractalizeR]

Hello.

I have a primary key in the table (auto-incrementing). How do I get
the last auto-generated value for it? JDBC has getGeneratedKeys()
method of the statement. And MySQL itself has LAST_INSERT_ID()
function, but jooq seems to use neither?

http://dev.mysql.com/tech-resources/articles/autoincrement-with-connectorj.html

[Lukas Eder]

Hello Vladislav,

> I have a primary key in the table (auto-incrementing). How do I get
> the last auto-generated value for it? JDBC has getGeneratedKeys()
> method of the statement. And MySQL itself has LAST_INSERT_ID()
> function, but jooq seems to use neither?

That is not yet supported, as most RDBMS support the concept of
sequences. However, with MySQL, there currently isn't a way to read
generated IDs with jOOQ. I will schedule a solution for the next
release 1.5.8:
https://sourceforge.net/apps/trac/jooq/ticket/416

In the mean time, you can use plain SQL fields to select the
LAST_INSERT_ID() from MySQL, as a workaround. Something like this:

Field<Integer> ID = create.plainSQLField("LAST_INSERT_ID()", Integer.class);
create.select(ID).fetchOne(ID);

Regards
Lukas

[FractalizeR]

Ok, thanks a lot.

[Lukas Eder]

I have been looking into this a little bit. The following RDBMS have
some sort of IDENTITY column concept. This is NOT the same as a
primary key. While primary keys (or any unique keys) are allowed to
span several columns, a table must NOT have more than one IDENTITY
column. This concept has been formally standardised in SQL:2003:
http://en.wikipedia.org/wiki/SQL:2003

And it's available in almost all of jOOQ's supported RDBMS:

- DB2's IDENTITY clause
- Derby's IDENTITY clause
- H2's AUTO_INCREMENT or IDENTITY clauses
- HSQLDB's IDENTITY clause
- MySQL's AUTO_INCREMENT clause or SERIAL column type
- Oracle's sequences and triggers (not sure if this can be used)
- Postgres' SERIAL column type (and sequences)
- SQLite's AUTOINCREMENT clause (not sure if this can be used)
- SQL Server's IDENTITY clause
- Sybase's IDENTITY clause

In general, it can be said that MySQL's AUTO_INCREMENT concept is NOT
the standard, but I can imagine that the standard can be mapped to
MySQL's IDENTITY variant. These feature additions will be on the
roadmap and hopefully ready for 1.5.9:

Source code generation for IDENTITY columns (similar to 1.5.9's
generation of unique and primary keys):
https://sourceforge.net/apps/trac/jooq/ticket/495

Factory enhancement, to fetch IDENTITY values:
https://sourceforge.net/apps/trac/jooq/ticket/416

Automatic refresh of IDENTITY values in UpdatableRecord:
https://sourceforge.net/apps/trac/jooq/ticket/496

[FractalizeR]

Thanks ;)