Jooq: Add multi Row[N] or Record[N] VALUES support

[lukaseder]

The INSERT .. VALUES API is quite useful for multi-row inserts as well:

ctx.insertInto(T)
   .columns(T.C1, T.C2, T.C3)
   .values(1, 2, 3)
   .values(4, 5, 6)
   .values(7, 8, 9)
   .execute();

But that works well only for constant numbers of rows. When the rows are determined dynamically, users have to assign the InsertValuesStep type to a local variable and loop through their rows, copying values to the DSL API, which is ugly.

We should have similar API like DSL.values() for the INSERT statement as well. Hypothetical syntax:

Integer[][] values = {{ 1, 2, 3 }, { 4, 5, 6 }, { 7, 8, 9 }};
ctx.insertInto(T)
   .columns(T.C1, T.C2, T.C3)
   .values(values)
   .execute();

The method name probably cannot be overloaded easily, because of the existing values(Collection<?>) overload.

Several variants of this new API will be made available, supporting arrays, collections, etc, perhaps taking inspiration from the Loader API.

[dmoebius]

How about a new values() method which accepts a stream, e.g. for the various InsertValuesStepN classes:

values(Stream<RowN> rows)

Could be used like that:

Collection<A> mycoll = ...;
ctx.insertInto(T)
    .columns(T.C1, T.C2, T.C3)
    .values(mycoll.stream().map(a -> DSL.row(a.c1, a.c2, a.c3)))
    .execute()

Advantage: type safe, user is forced to take care about proper value mapping.

[dmoebius]

Or, if a stream is not acceptable, use a differently named method rowValues(Collection<RowN>) instead, so that it doesn't collide with values(Collection<?>).

[lukaseder]

Regarding the Stream part, that will be done as part of https://github.com/jOOQ/jOOQ/issues/5133. It was delayed among other reasons because I wanted to be sure that it is acceptable for an API to accept a Stream (most APIs only return Stream). Background info here: https://twitter.com/lukaseder/status/897914723299143681

The right choice of <T> type will be a bit tricky. All of these are good and reasonable candidates:

• Row[N]
• Record[N]
• Map
• Object[]

The design of this API needs careful thought...

[Diagoras]

@lukaseder I've got an issue where I want to insert a collection of records, but the API seems to interpret that as a collection of fields. Is that covered by this issue as well, or do you think that would be a new one? For example:

final List<FooRecord> foos = ...
dsl.insertInto(FOO)
     .values(foos)
     .execute();

[lukaseder]

@Diagoras That would be covered by this issue as well.

[psilent]

Looking forward to this feature. I'm also trying to insert a collection of records. I'm using the approach of assigning the InsertValuesStep type to a local variable and looping through rows. This gives me significantly better performance over the loader API or .batchInsert() (I'm not sure why) but I will occasionally run into an error with MySQL of "packet size too large". I haven't been able to find a jOOQ option for determining this size limit ahead of time and restricting the number of times I call .values() so instead I'm calling .execute() in a try block and using a smaller batch size when I get that size exception. I understand I can configure max_allowed_packet on the server but that just pushes the problem back. Any suggestions or will this have to do until this feature is implemented?

[lukaseder]

Any suggestions or will this have to do until this feature is implemented?

No, you've already listed a few options there

[davinkevin]

Hi,

Just to let you know, I use this pattern in Kotlin to do this kind of thing:

query.insertInto(PODCAST_TAGS, PODCAST_TAGS.PODCASTS_ID, PODCAST_TAGS.TAGS_ID )
                .apply { tags.forEach { values(id, it.id) } }
                .execute()

That's allowing me to insert multiple values without storing and modifying an intermediate query.

[lukaseder]

That's a really cool technique. Thanks for sharing, @davinkevin

[jsarma]

Here's the workaround I'm using in java to get around this problem. You can use a lambda to create an implied type so you don't need to type it out.

    Integer[][] values = {{ 1, 2, 3 }, { 4, 5, 6 }, { 7, 8, 9 }};

    Optional<Integer> result = Optional.of(context.insertInto(table("t"))
       .columns(field("f1"), field("f2"), field("f3"))
    ).map(statement -> {
      for (Integer[] value: values) {
        statement.values(ImmutableList.of(value));
      }
      return statement.execute();
    });

[KevinDuringWork]

Scala workaround, not sure if it's terribly pleasant

```scala
data.foldLeft(
orm.insertInto(T).columns(T.F1, T.F2)
)((stmt, v) => {
stmt.values(v.v1, v.v2)
}).execute
````

[hherman1]

Bump... any plans to implement something to solve this?

[knutwannheden]

While DSLContext#batch() with its BatchBindStep basically suffers from the same limitation, I think it is worth mentioning here as an alternative, since a PreparedStatement#addBatch()-based approach may often be a good alternative.

Further, DSLContext#batch() does in fact have an overload which allows performing a dynamic multi-row INSERT with a single API method. See BatchBindStep#bind(Object[]...) and the convenience methods DSLContext#batch(Query, Object[]...) and DSLContext#batch(String, Object[]...).

[RabindraRakshit]

Here's the workaround I'm using in java to get around this problem. You can use a lambda to create an implied type so you don't need to type it out.

    Integer[][] values = {{ 1, 2, 3 }, { 4, 5, 6 }, { 7, 8, 9 }};

    Optional<Integer> result = Optional.of(context.insertInto(table("t"))
       .columns(field("f1"), field("f2"), field("f3"))
    ).map(statement -> {
      for (Integer[] value: values) {
        statement.values(ImmutableList.of(value));
      }
      return statement.execute();
    });

@lukaseder Do you suggest this meanwhile? @jsarma I would love to know if you did a load test using this? I am planning to use this in an api where it can go upto 800 ops easily.

[jsarma]

@RabindraRakshit No, I never load tested it, but I would guess IO to the SQL server would be the bottleneck, not whether you're using lambdas and maps in memory or not.

Also, Java 11 now has a var type. I haven't tried this for this case, but I imagine this could be used as a more effective solution than the optional trick I posted.

[lukaseder]

@lukaseder Do you suggest this meanwhile?

I don't suggest any specific approach. This issue discusses the option of adding convenience API for something that can already be done in many different, more laborious ways. I don't see why I would have a preference here.

@jsarma I would love to know if you did a load test using this? I am planning to use this in an api where it can go upto 800 ops easily.

Agreed with @jsarma. When looking at jOOQ in isolation, the big bottleneck in all benchmarks is always the string builder, see e.g. #10124. But that overhead is dwarved compared to the I/O bound operation when actually executing the query.

[baev]

What about adding a new values(RowN[]) method (as in DSL.values)? We already have a bunch of RowN[] toRowN(Collection data) utility methods in our codebase that used for using with DSL.values;

Speaking of which, it maybe worth of adding such of utility methods into JOOQ: (Edited by @lukaseder, content moved to https://github.com/jOOQ/jOOQ/issues/10775)

[lukaseder]

What about adding a new values(RowN[]) method (as in DSL.values)?

That's actually not a bad idea! I'll investigate the feasibility of this.

[lukaseder]

Speaking of which, it maybe worth of adding such of utility methods into JOOQ:

May I ask you to create a new feature request for this? I don't think this is related to this INSERT feature request here, and I'd like to track things separately. E.g. I can implement your other idea, but perhaps not this one. What would closing the ticket mean, then?

[lukaseder]

What about adding a new values(RowN[]) method (as in DSL.values)?

That's actually not a bad idea! I'll investigate the feasibility of this.

I think that one of the reasons why this hadn't been done yet is the fact that this method cannot be annotated as @SafeVarargs

[lukaseder]

Well, re-iterating the possibility of adding an "overload" by a different name (and thus avoiding the semantic issues caused by values(Collection<?>)), we can offer four new "overloads" (showing an example for InsertValuesStep2 only):

// These will produce warnings at the call site, because of potential heap pollution
InsertValuesStep2<R, T1, T2> valuesOfRows(Row2<T1, T2>... values);
InsertValuesStep2<R, T1, T2> valuesOfRecords(Record2<T1, T2>... values);

// These will be just fine, and probably more popular anyway
InsertValuesStep2<R, T1, T2> valuesOfRows(Collection<? extends Row2<T1, T2>> values);
InsertValuesStep2<R, T1, T2> valuesOfRecords(Collection<? extends Record2<T1, T2>> values);

[lukaseder]

~In case users accidentally use values(Object...) or values(Collection<?>), we'll have to check the array's or collection's first element to see if it's a Row or Record~. Might be risky. It could be a UDTRecord or some other conflicting value...

[lukaseder]

Done. This will ship in jOOQ 3.15.0.

[jonathan-meier]

Looking forward to jOOQ 3.15.0! Meanwhile, I'm using this foldLeft utility:

public static <T, U> U foldLeft(Stream<T> s, U i, BiFunction<U, T, U> f) {
    return s.<Function<U, U>>map(e -> r -> f.apply(r, e))
        .reduce(Function.identity(), Function::andThen).apply(i);
}

with

foldLeft(recordsToInsertStream, jooqContext.insertInto(TABLE).columns(TABLE.COLUMN1, TABLE.COLUMN2),
        (insertStep, recordToInsert) ->
            insertStep.values(recordToInsert.getColumn1(), recordToInsert.getColumn2()))
    .execute();

to avoid declaring a local InsertValueStep variable and looping.

[lukaseder]

It seems this didn't get implemented on InsertValuesStepN