How to insert a record and have its primary key set?

[Hilco Wijbenga]

Hi all,

I'm trying to (batch) insert a set of records and have their primary
keys set afterwards. I have tried with both 3.6.4 and 3.9.1. We use
MySQL.

I started out with

@SuppressWarnings({ "unchecked", "rawtypes" })
final Class<UpdatableRecord<?>> updatableRecordClass = (Class)
UpdatableRecord.class;
DSL
.using(configuration)
.batchInsert(records.toJavaArray(updatableRecordClass))
.execute();

(in the #run of a TransactionalRunnable). This works fine, i.e. the
records show up in the database, but my UpdatableRecords still have a
"null" primary key.

After some more digging I found out about DSLContext#insertInto and
InsertReturningStep#returning. So I tried this:

for (final UpdatableRecord<?> record : records) {
final InsertSetStep<?> iss = DSL
.using(configuration)
.insertInto(record.getTable());
final List<Field<?>> changedFields = Lists.newArrayList();
final List<Object> values = Lists.newArrayList();
for (final Field<?> field : record.fields()) {
if (record.changed(field)) {
changedFields.add(field);
values.add(record.getValue(field));
}
}
iss
.columns(changedFields)
.values(values)
.returning(record.getTable().getPrimaryKey().getFields())
.execute();
}

Again, the records show up fine in the database but my
UpdatableRecords still do not have a primary key.

Finally, I noticed that InsertReturningStep uses #fetchOne so I tried
that instead of #execute but it returns null.

I am at a loss as to how to make this work. How do I get the primary
key of an UpdatableRecord I just inserted into said UpdatableRecord?

Cheers,
Hilco

[Lukas Eder]

Hi Hilco,

Thanks for your message. This is a known limitation in jOOQ. We unfortunately currently don't fetch generated IDs from batch statements. That's this feature request here:

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

Also, as far as I know, the MySQL JDBC driver doesn't support fetching multiple generated IDs from bulk statements (i.e. statements with several records in the jOOQ VALUES() clause).

So, I don't see an easy way around this, unless you have some other means of unambiguously identifying the records that you've just inserted, in case of which you could run an ordinary SELECT statement on them.

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hilco Wijbenga]

Hi Lukas,

On 29 March 2017 at 02:00, Lukas Eder <lukas...@gmail.com> wrote:
> Thanks for your message. This is a known limitation in jOOQ. We
> unfortunately currently don't fetch generated IDs from batch statements.
> That's this feature request here:
> https://github.com/jOOQ/jOOQ/issues/3327

Ah, okay, that's good to know.

> Also, as far as I know, the MySQL JDBC driver doesn't support fetching
> multiple generated IDs from bulk statements (i.e. statements with several
> records in the jOOQ VALUES() clause).

According to [1] that's sort of true. :-) Apparently, you can get back
the id of the *first* record inserted (which should allow you to
"calculate" the ids of the other records). So it would seem to be
possible. I'm going to try that.

Still, why didn't my second approach (DSLContext#insertInto) work?
According to the JavaDoc for #returning MySQL supports this? And
there's [2] which also seems to say that it should work. Or is it
still a bulk insert behind the scenes because they are all part of the
same TransactionalRunnable? (That would be a good thing, BTW.)

Cheers,
Hilco

[1] https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id
[2] https://github.com/jOOQ/jOOQ/issues/3140

[Lukas Eder]

Hi Hilco,

Sorry for the delay. I just realised that I still owe you answers on this discussion!

2017-03-29 17:46 GMT+02:00 Hilco Wijbenga <hilco.w...@gmail.com>:

Hi Lukas,

On 29 March 2017 at 02:00, Lukas Eder <lukas...@gmail.com> wrote:
> Thanks for your message. This is a known limitation in jOOQ. We
> unfortunately currently don't fetch generated IDs from batch statements.
> That's this feature request here:
> https://github.com/jOOQ/jOOQ/issues/3327

Ah, okay, that's good to know.

> Also, as far as I know, the MySQL JDBC driver doesn't support fetching
> multiple generated IDs from bulk statements (i.e. statements with several
> records in the jOOQ VALUES() clause).

According to [1] that's sort of true. :-) Apparently, you can get back
the id of the *first* record inserted (which should allow you to
"calculate" the ids of the other records). So it would seem to be
possible. I'm going to try that.

I absolutely recommend you not to do that! None of the databases I know of make any reasonable guarantees about

1) Order of insertion and identity attribution

2) Identity consecutiveness

While this approach will certainly work well in a development environment, it may blow up in production where you have concurrency, and thus race conditions for identity values.

Likewise, one should (almost) never use a statement like 

SELECT max(id) + 1 FROM some_table 

to find the next ID

 

Still, why didn't my second approach (DSLContext#insertInto) work?
According to the JavaDoc for #returning MySQL supports this? And
there's [2] which also seems to say that it should work. Or is it
still a bulk insert behind the scenes because they are all part of the
same TransactionalRunnable? (That would be a good thing, BTW.)

returning() is not the problem here as jOOQ supports it for single-row inserts in almost every database through one way or another.

But notice that the issue https://github.com/jOOQ/jOOQ/issues/3140, which you've referenced, was closed as "works for me". I could simply not reproduce the issue at the time, but there may well be a subtle issue hidden here.

Thanks,

Lukas