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 <[email protected]>: > Hi Lukas, > > On 29 March 2017 at 02:00, Lukas Eder <[email protected]> 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 -- 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.
