Jooq: DAO should copy the inserted identity value back to the POJO on DAO.insert(P)

[chuchiperriman]

When you insert a POJO there is no way to get the inserted id using the generated dao classes. IMHO the best is assign the inserted id to the key field in the pojo. An example of a custom method in dao to do this:

public final void insertSettingId(QrsControlImpresos objeto) {
        QrsControlImpresosRecord record = using(configuration()).newRecord(QRS_CONTROL_IMPRESOS, objeto);
        record.insert();
        objeto.setId(record.getId());
    }

but in DaoImpl you are setting the id to the record created internally but not to the pojo

---

See also:

• http://stackoverflow.com/q/28748365/521799

[lukaseder]

I can see the need for obtaining an updated POJO after insertion through a jOOQ DAO. However:

• the suggested behaviour is not always desired (such a side-effect of calling a method might be surprising)
• this wouldn't work for immutable POJOs

An alternative is to return a new POJO from the method, containing the change, but that would be inconsistent with other API parts that return the number of affected rows.

I'll have to think about these and other options...

[lukaseder]

I don't see enough compelling options to implement this right now, in jOOQ 3.1. Postponing for a later release...

[walec51]

can't we just generate the following method in the daos:
pojo = dao.insertAndFetch(pojo);
?

[lukaseder]

@walec51: That's not a bad idea at all! A similar method could be implemented for update. It would allow for fetching not only ID values, but also other trigger-generated values.

@chuchiperriman : Would this be along the lines of your original feature request?

[chuchiperriman]

That's solution is not the same, insertAndFetch will execute two queries to the database. IMHO the default behaviour when you insert an object should be set only the ID to the inserted object. The insertAndFetch method is helpful but not the same.

[lukaseder]

insertAndFetch would internally translate to insertInto(...).values(...).returning(...), which again is emulated using JDBC's getGeneratedKeys(), if the JDBC driver supports that.

But you're right, it's not the same. I have created a separate issue for this: #3021

[sytolk]

insertAndFetch is still missing in generated DAOs.. its have lazy programmers here :)

[lukaseder]

insertAndFetch is still missing in generated DAOs

Yeah. But we're not 100% sure if this API is really sensible. I'm not very happy with the _AndFetch_ suffix. It looks as though there could be many other _AndDoSomething_ suffixes... Better ideas are very welcome!

[sytolk]

Do you mean that the method name insertAndFetch is not clear for the user that will internally translate to insertInto(...).values(...).returning(...) and set generated key to fetched object using JDBC's getGeneratedKeys() ?
I think that simple T insert(T object, Statement.RETURN_GENERATED_KEYS) with return T is clear that will return object with generated key.
The question is the same name like void insert() with parameter to return generated keys.. or different name. For me insert1() is ok too :)

[lukaseder]

@sytolk No, I mean that it is not clear whether from an API evolution perspective, it is a sensible thing to add a method that is so specific. For you, insert1() might be OK. For us, adding something like insert1() is equivalent to just throwing the whole product away. If you know what I mean :)

Long story short, this will need some more thought. But you have all the tools necessary already to extend jOOQ at your site.

[sytolk]

It will be good if insertAndFetch() will be replaced with observer insert().generatedKey().fetch() but in DAOs.. If 1 throw some kind of exeption its have 0 :)

[lukaseder]

@sytolk: I'm sorry, I'm not so sure what you're suggesting here. To return some sort of InsertResult from the insert() method?

[sytolk]

Yes it will be better for DAOs if insert() method is not void and return some sort of observer object without execute() method. User can write for example with DAOs insert().returning().fetch()

[lukaseder]

I will reuse the same argument and say that the void -> non-void change can be done only once. Afterwards, backwards-compatibility forces you to keep the contract intact. And I'm just not convinced of any such contract yet...

[sytolk]

@lukaseder how about to include dependency of https://github.com/ReactiveX/RxJava
looks like API is really sensible
http://reactivex.io/documentation/observable.html

[type0lang]

in this very moment i'm deciding wheter using jooq or throwing it away because of void insert(T t);

p.s. i.m.o. insertandfetch could be a bad idea if you believe to pursue a 2-round-trips strategy.

[sytolk]

I mean something like this in low level but with jooq functionality.. sql generation DAOs will be cool
https://github.com/davidmoten/rxjava-jdbc

[lukaseder]

@sytolk : I see. Yes, we've been thinking about this before as well, although I'm not 100% convinced that it's in-scope for jOOQ itself. You can already today achieve similar "asynchronous" flow using CompletableFuture, for instance:
http://blog.jooq.org/2014/09/23/asynchronous-sql-execution-with-jooq-and-java-8s-completablefuture/

But please, let's keep discussions on-topic. This issue is only about DAOs and inserted IDs. If you'd like to discuss new features in general, the best place would be the jOOQ User Group:
https://groups.google.com/d/forum/jooq-user

[sgrimm-sg]

How about something like dao.withReturning(callback).insert(...)? Then in Java 8 you could do something like

dao.withReturning(MyPojo::setId).insert(myPojo);

which doesn't seem too horrible. This kind of method chaining seems like it's consistent with jOOQ's API philosophy, and doesn't require any signature or semantics change for the existing dao.insert(myPojo) case.

[lukaseder]

@sgrimm-sg : That's an intriguing idea, although in its current form, it won't work. While the setter of the POJO seems like a useful discriminant to determine what to use in the RETURNING clause, the actual Field reference would be better, as it won't depend on a potential RecordMapper implementation as specified by DAO.mapper().

In addition, a mini-DSL will introduce an intermediary state that needs to be remembered until the terminal call (insert() in this case), which again assumes that myPojo is mutable. In the immutable case, insert would still need to return a new pojo.

[william-zh]

Do we have any update on this or insertAndFetch()? Either one may help solve this open request.

[lukaseder]

It would indeed be nice to have, but DAO improvements aren't our highest priority right now, I'm afraid.

[fedotxxl]

3 years and still no solution. Can you provide at least one solution of this problem?

[lukaseder]

3 years and still no solution

:-)

Just because there is an issue doesn't mean the issue is correct, meaningful, the way to go, etc. For example, we might actually be removing DAO support somewhen in the future... (see #5984)

DAOs have been a constant source of confusion, feature requests, etc. We might just have gotten DAOs wrong entirely.

Can you provide at least one solution of this problem?

Yes, you could use UpdatableRecord.store() directly

[billoneil]

Solution 2: Use the DSL
Solution 3: Don't be lazy and create your own customized DAO source code generator. It's not that hard. I added quite a few features in the DAO's for my own projects but I would never except the library maintainer to support all of them.

[lukaseder]

Solution 3: Don't be lazy and create your own customized DAO source code generator. I added quite a few features in the DAO's for my own projects but I would never except the library maintainer to support all of them.

I'm very curious about that. Would you mind listing some of those features that you implemented on your side?

[billoneil]

@lukaseder I shared it on the google group around a year ago you gave some good suggestions which I never got around to implementing. It's basically a query builder for the DAO's that allows slightly more complicated where clauses.

[lukaseder]

@billoneil Thanks for the hint. I forgot about that. For the record (and future visitors), here are some discussions on the user group:

• https://groups.google.com/forum/#!msg/jooq-user/h5-DKtu_TQY/LQdwapfKtvIJ
• https://groups.google.com/d/msg/jooq-user/aZ0CJwUfdkM/30sQgv35BwAJ

[robrez]

Would it be disagreeable if this were opted-into via an option in the code generator or via the configuration object instance provided to the dao?

I'm sure it is very likely that this is the way the Record objects work - but it's worth mentioning that it would be nice to see populated values for _all_ generated values eg: mydate date default current_date. The commentary so far seems to focus specifically on serial/auto-increment ids. I know that the pg driver (at least) supports any number of values via getGeneratedKeys.

It seems like a signature returning a pojo instance (instead of void) would have the benefit of working even in cases where immutablePojos where used in the code generator.

Regards

[lukaseder]

@robrez: This wouldn't be a code generator config, but a runtime setting, I suspect. All the type information is available anyway, and the code generator doesn't do much other than wiring the other classes together (which already have information about identity columns).

You're right, all the values should probably be retrieved by using INSERT .. RETURNING or UPDATE .. RETURNING

[vkuzel]

Just for your amusement, this is how it's done by others.

• ActiveJDBC - Because this is implementation of active record, the DO updates itself. So after you invoke .save() method you can get an id (and other updated values) from the DO.
• Apache Cayenne - Also updates DO. Call of CayenneContextExecutor.execute() method updates the object.
• Ebean - EbeanServer.save() method updates object's fields.
• MyBatis - It's generated *Mapper.updateByPrimaryKey() updates object's fields.
• Spring Data JPA - Spring's CRUD repository returns new instance of DO as a result of * CrudRepository.save() method.
• JPA's implementations - Modifies DO field.
• JDO's implementations - Modifies DO field.

[lukaseder]

Thanks for the listing, @vkuzel

[lukaseder]

OK everyone. I think we have enough voices here that request this feature. There are several steps to be done here, which hopefully find their way through 3.10:

• [x] We'll implement putting the identity value from the Record to the POJO by default.
• [x] If Setting.returnAllOnUpdatableRecord is active, it will also extend to DAO. This includes update() operations. In fact, the simplest solution would be to just copy all the Record values back to the POJO, regardless what they contain.
• [ ] Immutable POJOs are not affected from this change, of course. Perhaps, a follow-up feature request will cover that.
• [x] We'll add a new Setting.returnRecordToPojo (default = true) flag to disable this new feature. The flag might also affect non DAO usage. This has to be checked. This is https://github.com/jOOQ/jOOQ/issues/5790
• [x] Inserting or updating multiple POJOs is currently implemented via batch. Depending on whether (https://github.com/jOOQ/jOOQ/issues/3327) can be fixed in time, the new Setting.returnRecordToPojo flag, when enabled, would deactivate batching.
• [ ] If a DAO has a custom mapper(), that must be used to map the identity (and possibly other columns) to the POJO

(thanks also @vkuzel for referring to this issue from your blog post ;) )

[billoneil]

Will this be either a totally on or totally off feature? Or will there be a way to choose per call if the id is returned or not? For example in a very write heavy statement where you don't care about the returned id it could save the SELECT LAST_INSERT_ID(); per insert (mysql). If its exposed as the void insert() and Pojo insertReturning() it will give you the flexibility to choose but make the api more complex. What would the dao APIs look like or are you undecided still?

[lukaseder]

@billoneil: Good question. There are a lot of feature requests for new DAO methods (e.g. insertOrUpdate(), insertOrIgnore(), etc.). This means that having a new API for this feature here is not possible as we'd eventually need insertOrUpdateReturning(), insertOrIgnoreReturning(), etc.

The Setting.returnRecordToPojo will be a "totally on or totally off" feature for whatever you choose the Setting's (and its owning Configuration's) scope to be.

E.g. you can define a Setting for just one call to turn this off.

[billoneil]

sounds good 👍

[aram535]

Hi - Did this change happen to make it into 3.9.1? We're trying to track down a org.jooq.exception.NoDataFoundException

[lukaseder]

@aram535 No, this is a major change and will be implemented for 3.10 only, not any 3.9.x patch version. If in doubt, here are two sources to see what changed:

• https://www.jooq.org/notes.php/?version=3.9
• https://github.com/jOOQ/jOOQ/search?q=2536&type=Issues&utf8=%E2%9C%93 (you'd see a merge ticket)

[lukaseder]

@aram535: Interesting, would you mind opening a new bug? If there's a regression there, it may well be that ResultQuery.execute() no longer returns the right number of rows, which would cause this exception.

[lukaseder]

Postponing this until we know what we'll do with these DAOs (#5984)

[stangls]

we so need this!