Hi Matt, Thanks for your message. First, I'd like to recommend you don't run a sequence fetching round trip per row. Otherwise, you're not really profiting from your bulk insertion with that many fetches. jOOQ allows you to fetch a set of sequence values in one go, see: https://blog.jooq.org/how-to-fetch-sequence-values-with-jooq/
But you can always just put the seq.nextval() call in your INSERT .. VALUES clause, as that allows for expressions to be used. If you want to trigger DEFAULT behaviour, then instead of putting an explicit NULL value there, you should put an explicit DEFAULT expression in that row. You can do so using jOOQ's DSL::default_ method. Of course, I don't know what sequence.map(...) does, and when it can produce an Optional.empty(), so it might as well be that instead of embedding this decision in your internal loop, you just have 2 completely different INSERT statements, depending on that sequence.map() decision? I hope this helps, Lukas On Mon, Dec 19, 2022 at 10:15 AM Matthias Keller <[email protected]> wrote: > Hi > We're currently struggling to insert a list of data into a table having an > auto-generated ID (or using a sequence on Oracle). Because our code must be > runnable both on Oracle and other DBs (for example H2, SQL Server, > MariaDB), we cannot completely optimizie this query. > The problem is the following, assuming we have to insert data in a table > having a PK column (numerical, autogenerated on most DBs and using a > sequence on oracle). > > For example: > > db.insertInto(TABLE).columns(PK, COL) > .valuesOfRows( > rows.stream().map(rows -> DLS.row( > sequence.map(seq -> > db.dsl().fetchValue(seq.nextval())).orElse(null), // <- does not work for > example on H2 > rows.col())) > > > However, this does not work for example for H2, as inserting NULL in that > explicit NON-NULL column doesn't seem to trigger the autogeneration of the > value. > > Using .insertInto(TABLE).set(..).set(..).newRecord() would work, as we > could conditionally add the .set. However, the different return types makes > this very very hard to use in a loop, as newRecord has to be called > rows.size()-1 times. > > Is there a better way to insert such dynamic data and adding or not adding > the sequence as needed? > Thanks Matt > > -- > 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]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/8f0b2a7d-009e-4dab-9210-3308b55ef84dn%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/8f0b2a7d-009e-4dab-9210-3308b55ef84dn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5gtcC2CeoBFsa7zbmz%3DJRfLE-9Qs_GUZwZQDyRp__HpQ%40mail.gmail.com.
