Re: How to retrieve INSERTed object with DAO insert method

[Lukas Eder]

Hi Francesco,

> I am using the generated POJOs and DAOs to retrieve and store objects in my
> database. I know that, for "regular" INSERTs, I can use the returning()
> method, but how can I retrieve the inserted object? There is a sequence for
> the ID, which is automatically generated by the database and I would like to
> get back.
> I would like to know if I can avoid using DAOs and do a simple
> factory.insert(TABLE, TABLERecord) so I can use the returning() right there.

Yes you can. I've just noticed that the API for such operations is a
bit clumsy, though. You'll have to resort to using jOOQ's internal
model (a.k.a. non-DSL API, classic API, model API) for that. Here's an
example:

TableRecord record = // ... your record
InsertQuery<TableRecord> insert = factory.insertQuery(TABLE);
insert.addRecord(record);
insert.setReturning();
insert.execute();
TableRecord result = insert.getReturnedRecord();

Inserting (and updating) pre-existing Records through the DSL API
should be possible, though. I've added feature request #2199 for this:
https://github.com/jOOQ/jOOQ/issues/2199

Cheers
Lukas

[Francesco Pontillo]

Ok, I just solved this by instantiating a new object from my Factory:

TABLERecord r = factoryObj.newRecord(TABLE);

r.set(...);

r.store();

// r is already updated with the automatically generated ID

System.out.println(r.ID); 

I had an error on the primary key being null, probably because I instantiated a record as a regular Object: new TABLERecord(), and in doing so the primary key was automatically set to null, leading to the error.

I hope my mistake will be useful to others.

P.S. I wrote the reply before your post. I tried using the InsertQuery class, but I had the same error, possibly for the same reason (the ID was set to dirty, from what I have understood from the docs and other posts here).

Thank you!

[Lukas Eder]

Hello,

> TABLERecord r = factoryObj.newRecord(TABLE);
> r.set(...);
> r.store();
> // r is already updated with the automatically generated ID

True, that is another (probably better) option

> I had an error on the primary key being null, probably because I
> instantiated a record as a regular Object: new TABLERecord(), and in doing
> so the primary key was automatically set to null, leading to the error.
>
> I hope my mistake will be useful to others.

Hmm, in general, this should work... So, let's see if it was "your
mistake" or a bug. Do you use a serial / auto_increment / sequence
with trigger for your primary key? Can you show the table's DDL and
the Java code that caused the null-related error?

Cheers
Lukas

[Francesco Pontillo]

My DDL is the following:

CREATE TABLE user_session

(

  id bigserial NOT NULL,

  id_user integer NOT NULL,

  ...

)

What I was doing was get a custom type of object from a JSON, deserialize it and then convert it into a Record. In the conversion step (MyObject --> Record) I didn't check for the null value on the PK (which is null when the client wants to add a new object to the collection), never realizing that by setting the value to null would set the flag of the column to dirty. I assumed that the operation was somewhat idempotent under all aspects. Turns out I was wrong :).

[Lukas Eder]

> My DDL is the following:
>
> CREATE TABLE user_session
> (
> id bigserial NOT NULL,
> id_user integer NOT NULL,
> ...
> )
>
>
> What I was doing was get a custom type of object from a JSON, deserialize it
> and then convert it into a Record. In the conversion step (MyObject -->
> Record) I didn't check for the null value on the PK (which is null when the
> client wants to add a new object to the collection), never realizing that by
> setting the value to null would set the flag of the column to dirty. I
> assumed that the operation was somewhat idempotent under all aspects. Turns
> out I was wrong :).

I see. Well, this issue has been discussed once or twice :-)

It turns out that for me, the best option in the short run was to
expose the dirty flags to the outside of records, such that users can
at least explicitly influence this behaviour in jOOQ.

Cheers
Lukas

[Francesco Pontillo]

I'd rather not touch my generated classes at all, the thing is I didn't know about dirty flags so I was doing everything unaware of the issues this behavior may cause. 

Thank you!