Dealing with duplicate keys [Was: Newbie API questions: inserting POJOs and dealing with duplucate keys]

[Lukas Eder]

Again... splitting the original thread in two. Comments inline:

2014/1/18 sergivs <sergei....@gmail.com>

I found UpdataleRecord.store() in the API. That will work for updates, which will be helpful. Is there a similar convenient way of doing an equivalent of INSERT ... ON DUPLICATE KEY IGNORE using a Record instance created from a POJO?

P.S. actually, I just tried it and read the API description more carefully. It won't do an update if the UpdatableRecord was loaded from a POJO. So this question also remains open.

You're right, I've missed the point that the UpdatableRecord is loaded from a POJO. In that case, storing the record will always produce an INSERT.

So, unfortunately, there is currently no convenient way to write something along the lines of

record.insertOnDuplicateKeyUpdate();

There probably is room for improvement in that area, although we should be careful not to introduce new API that creates confusion around the various UpdatableRecord features that are already available. I have registered a feature request for this:

https://github.com/jOOQ/jOOQ/issues/2961

Cheers

Lukas

[sergivs]

Thanks!

A method like record.insertOnDuplicateKeyUpdate() would imply that all (non-key) fields should be updated from the given record instance. So it is not as comprehensive as the other, explicut aporoach, but I think it would be quite useful in some cases.

insertOnDuplicateKeyIgnore() would also be useful.

[Lukas Eder]

2014/1/18 sergivs <sergei....@gmail.com>

Thanks!

A method like record.insertOnDuplicateKeyUpdate() would imply that all (non-key) fields should be updated from the given record instance. So it is not as comprehensive as the other, explicut aporoach, but I think it would be quite useful in some cases.

Yes, it would be useful in many cases. It reminds me of H2's MERGE statement, which does precisely that:

http://www.h2database.com/html/grammar.html#merge

 

insertOnDuplicateKeyIgnore() would also be useful.

True, that should be added as well, for consistency reasons. I've updated the issue title

Cheers

Lukas 

[Lukas Eder]

jOOQ 3.14 will support UpdatableRecord.merge() for this purpose: https://github.com/jOOQ/jOOQ/issues/2961