INSERT..RETURNING is not working in JOOQ

Question

I have a MariaDB database and I'm trying to insert a row in my table users. It has a generated id and I want to get it after insert. I have seen this but it's not working for me:

public Integer addNewUser(String name) {
    Record record = context.insertInto(table("users"), field("name"))
        .values(name)
        .returning(field("id"))
        .fetchOne();
    return record.into(Integer.class);
}

New row is inserted but record is always null. I'm not using JOOQ code generation.

Answer 1

This is a known limitation in jOOQ 3.9: https://github.com/jOOQ/jOOQ/issues/2943

You currently cannot use the RETURNING clause in jOOQ when using plain SQL tables and fields (as opposed to generated ones), because jOOQ needs to know the identity column name to bind to JDBC (in most databases), and that meta data is not available from your table("users") object.

Unfortunately, passing the ID column to the RETURNING clause isn't sufficient, because there's no guarantee that this is the identity column. You might also pass several columns to the RETURNING clause, in case of which jOOQ wouldn't know which one would be the identity column.