Insert Returning Query For MySQL in JOOQ

Question

am trying to use the following code to get the auto generated id . My back end is MySQL. Code looks like this

Record record = create.insertInto(CANDIDATE, CANDIDATE.FIRST_NAME,      
       CANDIDATE.LAST_NAME,CANDIDATE.EXTRACTED_NAME)
       .values("Charlotte", "Roche","Charlotte Roche")
       .returning(CANDIDATE.ID)
       .fetchOne();

System.out.println(record.getValue(CANDIDATE.ID));

I am getting NullPointerException. I took a look at http://www.jooq.org/javadoc/latest/org/jooq/InsertReturningStep.html . It says

Derby, H2, Ingres, MySQL, SQL Server only allow for retrieving IDENTITY column values as "generated key". If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.

As per my understanding in Mysql auto_increment works as IDENTITY. Can anybody please throw some light on how to achieve this for MySQL

I have taken a look at this SO Question on a similar topic and tried following

Result<?>  record =
            create.insertInto(CANDIDATE, CANDIDATE.FIRST_NAME, CANDIDATE.LAST_NAME,CANDIDATE.EXTRACTED_NAME)
                  .values("Charlotte", "Roche","Charlotte Roche")
                  .returning(CANDIDATE.ID)
                  .fetch();

            System.out.println(record.size());

Though it inserts record in the backend but it prints the record.size() as zero

Answer 1

I'm know that I'm late for the party. But I hope I can help someone with similar problem,

Derby, H2, Ingres, MySQL, SQL Server only allow for retrieving IDENTITY column values as "generated key". If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.

The words "generated key" is the problem. You can check if your table id is AUTO_INCREMENT or not by using SHOW CREATE TABLE $table_name. I think it is not.

P/s: I'm using MySQL

Answer 2

Just did a test inserting a record and retrieving the generated id from within a Spring service without any problem.

So yes, auto_increment in MySQL works as IDENTITY with jOOQ.

The MySQL table looks like this:

CREATE TABLE persons (
    `id` mediumint(9) NOT NULL AUTO_INCREMENT,
    `first_name` varchar(64) NOT NULL,
    `last_name` varchar(64) NOT NULL,
    primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the service like this:

public Result<PersonsRecord> insertPerson(String firstName, String lastName) {
    Result<PersonsRecord> result =
        dsl
            .insertInto(
                    PERSONS,
                    PERSONS.FIRST_NAME,
                    PERSONS.LAST_NAME)
            .values(
                    firstName,
                    lastName)
            .returning(PERSONS.ID)
            .fetch();
    logger.debug("Person ID: " + result.getValue(0, PERSONS.ID));
    return result;
}

The generated id is available straight away after executing the insert:

Person ID: 4