Can jOOQ support composite primary keys if auto-increment field isn’t the first one

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)
)

JavaScript
​x
 
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

JavaScript
 
 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));

JavaScript
 
            // 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?

Answer

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)

Advertisement

Answer