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 2017-03-28 22:10 GMT+02:00 Hilco Wijbenga <[email protected]>: > 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 > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
