Spring data R2DBC, saveAll() in one sql query

Question

In ReactiveCrudRepository we have saveAll methods

<S extends T> reactor.core.publisher.Flux<S> saveAll(org.reactivestreams.Publisher<S> entityStream)
<S extends T> reactor.core.publisher.Flux<S> saveAll(Iterable<S> entities)

When we save collection with N elements, N queries are made not one. For example if we save collection with 2 elements, we get 2 queries like :

INSERT INTO test(id, value) VALUES (111, "value1");
INSERT INTO test(id, value) VALUES (222, "value2");

Is there a way to make this in one SQL query?

INSERT INTO test(id, value) VALUES  (111, "value1"), (222, "value2");

It may be needed to map to Mono, but this is ok for me in this case.

score 1 · Accepted Answer · answered Nov 09 '21 at 16:56

Unfortunately I think this is not currently supported. Looking in the spring-data-relational library, the Insert statement accept a list of values, but each value corresponding to one column, no way to add different rows.

If you look deeper in this library, the InsertBuilder interface declares methods like this one:

         /**
         * Add one or more {@link Expression values} to the {@code VALUES} list. Calling this method multiple times will add
         * a {@link Expression values} multiple times.
         *
         * @param values the values.
         * @return {@code this} builder.
         * @see Column
         */
        @Override
        InsertValuesWithBuild values(Expression... values);

And the implementation store those values into a list of expressions (class DefaultInsertBuilder):

/**
 * Default {@link InsertBuilder} implementation.
 *
 * @author Mark Paluch
 * @since 1.1
 */
class DefaultInsertBuilder
        implements InsertBuilder, InsertBuilder.InsertIntoColumnsAndValuesWithBuild, InsertBuilder.InsertValuesWithBuild {

    private @Nullable Table into;
    private List<Column> columns = new ArrayList<>();
    private List<Expression> values = new ArrayList<>();

[...]

Then, the renderer class (converting the statement into an SQL string) is adding those values into VALUES(...) each value separated by a comma (class InsertStatementVisitor):

        if (segment instanceof Insert) {

            builder.append("INSERT");

            builder.append(" INTO ").append(into);

            if (columns.length() != 0) {
                builder.append(" (").append(columns).append(")");
            }

            builder.append(" VALUES (").append(values).append(")");

            return Delegation.leave();
        }

with the values filled here in the ValuesVisitor class:

            if (first) {
                first = false;
            } else {
                builder.append(", ");
            }

            builder.append(this.current.getRenderedPart());

So the code of spring-data-relational shows that having a SQL like VALUES(...),(...) is not possible for now.

Spring data R2DBC, saveAll() in one sql query

1 Answers1