Insert returned query for MySQL into JOOQ
I am trying to use the following code to get an auto generated id. My end is MySQL. The 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 a NullPointerException. I took a look at http://www.jooq.org/javadoc/latest/org/jooq/InsertReturningStep.html . It says:
The Derby, H2, the Ingres, the MySQL, . Only SQL Server allows you to retrieve the IDENTITY column values as "generated key". If other fields are requested, the second expression is returned. The client code must guarantee the integrity of the transaction between the two statements.
As per my understanding in Mysql, auto_increment works like IDENTITY. Can anyone please tell me how to achieve this for MySQL
I have looked at this SO question on a similar topic and tried to follow
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());
Although it inserts the record into the backend, but it prints record.size () as zero
source to share
I know I'm late for the party. But I hope I can help someone with a similar problem,
Derby, H2, Ingres, MySQL, SQL Server only allow retrieving IDENTITY column values as " generated key ". If other fields are requested, the second expression is returned. The client code must guarantee the integrity of the transaction between the two statements.
Words "generated key"
are a problem. You can check if your table id is AUTO_INCREMENT
or not using SHOW CREATE TABLE $table_name
. I think this is not the case.
P / s: I am using MySQL
source to share
There may be a problem with the transaction. The insert may not yet store these values in the database, so nothing is being fetched.
Also I think IDENTITY in MySQL case is not AUTO_INCREMENT but PRIMARY KEY (...) https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
source to share
Just passed the test by inserting a record and getting the generated id from the Spring service without any problem.
So yes, auto_increment in MySQL works like 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 a 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 identifier is available immediately after performing the insert:
Person ID: 4
source to share