I’m using jOOQ (3.14.11) to manage a table defined (in H2
or MYSQL) as:
CREATE TABLE example_one ( group_id VARCHAR(36) NOT NULL, pawn_id INT UNSIGNED AUTO_INCREMENT NOT NULL, some_unimportant_attribute INT UNSIGNED DEFAULT 0 NOT NULL, another_uniportant_attribute VARCHAR(36), CONSTRAINT pk_example_one PRIMARY KEY (group_id, pawn_id) )
Note that in this SQL, the primary key specifies the (group, pawn) IDs in that order but it is the
pawn_id, the second one, which is the auto-increment/identity column.
It appears that jOOQ doesn’t like this arrangement. When I try to use the Record objects to insert a new row, it will not return back to me the “pawnID” value:
ExampleOneRecord r = create.newRecord(EXAMPLE_ONE); r.setGroup("a group identity"); r.store(); assert r.getPawnId() != null; // <---- FAILS test
Diving into the code, the suspect seems to be in AbstractDMLQuery.java method executeReturningGeneratedKeysFetchAdditionalRows which has this bit of logic:
// Some JDBC drivers seem to illegally return null // from getGeneratedKeys() sometimes if (rs != null) while (rs.next()) list.add(rs.getObject(1));
The call to
rs.getObject(1) seems to be assuming that the generated column will always be the first column of the primary key.
Is there any way to convince jOOQ otherwise?
This is a bug in jOOQ 3.15.1 for H2: https://github.com/jOOQ/jOOQ/issues/12192
It has been fixed in jOOQ 3.16.0 for H2 2.0.202, which now supports the powerful data change delta table syntax, allowing for much easier fetching of generated values from a DML statement (it was implemented before that H2 version, but had a significant bug: https://github.com/h2database/h2database/issues/2502)