Batch-Insertion of data with optional sequence

[Matthias Keller]

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

[Lukas Eder]

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

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/8f0b2a7d-009e-4dab-9210-3308b55ef84dn%40googlegroups.com.

[Matthias Keller]

Hi Lukas

Thanks for your quick reply and the hint with the fetching of multiple sequence values!

Sorry for not explaining the  'map' funktion above - here sequence is just an Optional<Sequence<Long>> meaning that depending on the DB used, it is present (oracle) or absent (all other DBs supporting some kind of auto increment).

Having two completely different inserts is what we do now, but since our real table has about 10 columns, this results in a lot of duplicated code, with just the difference, that in one case, we have a Row11 (Oracle, includes the PK with the sequence number) and in the other case a Row10 (all other DBs). What we would like to do it reduce this duplication.

I've seen the default methods, but have been unable to include them in such an insert clause using valuesOfRows, as this method takes Rows with concrete values as far as I understand it; or is it possible to have a default_() value inside a valuesOfRows() ? If yes, how exactly?

Thanks Matt

[Lukas Eder]

Just in case: Oracle 12c and later also support IDENTITY columns...

If you want to mix bind values with expressions, you just have to turn the bind values into an expression using DSL.val():

- https://stackoverflow.com/q/67522537/521799

- https://www.jooq.org/doc/latest/manual/sql-building/bind-values/

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5bb55c10-92df-4f48-bf8e-db7acdbf7ec7n%40googlegroups.com.

[Matthias Keller]

Hi Lukas

Thanks for your input. I think I also tried it using val() but wasn't successful, but your input that Oracle finally does support IDENTITY columns led to the decision to switch to them instead, rendering the problem here obsolete.

Thanks for your quick help!

Matt