9

I need to insert multiple rows into one table in one batch. In DatabaseClient i found insert() statement and using(Publisher objectToInsert) method which has multiple objects as argument. But would it insert them in one batch or not? Another possible solution is connection.createBatch(), but it has a drowback : I cannot pass my Entity object there and i cannot generate sql query from the entity.

So, is it possible to create batch insert in r2dbc?

voipp
  • 1,243
  • 3
  • 18
  • 31

3 Answers3

8

There is no direct support till now, but I found it is possible to use Connection to overcome this barrier simply, check out this issue, spring-data-r2dbc#259

The statement has a add to repeat to bind parameters.

The complete codes of my solution can be found here.

        return this.databaseClient.inConnectionMany(connection -> {

            var statement = connection.createStatement("INSERT INTO  posts (title, content) VALUES ($1, $2)")
                    .returnGeneratedValues("id");

            for (var p : data) {
                statement.bind(0, p.getTitle()).bind(1, p.getContent()).add();
            }

            return Flux.from(statement.execute()).flatMap(result -> result.map((row, rowMetadata) -> row.get("id", UUID.class)));
        });

A test for this method.

@Test
    public void testSaveAll() {

        var data = Post.builder().title("test").content("content").build();
        var data1 = Post.builder().title("test1").content("content1").build();

        var result = posts.saveAll(List.of(data, data1)).log("[Generated result]")
                .doOnNext(id->log.info("generated id: {}", id));

        assertThat(result).isNotNull();
        result.as(StepVerifier::create)
                .expectNextCount(2)
                .verifyComplete();
    }

The generated ids are printed as expected in the console.

...
2020-10-08 11:29:19,662 INFO [reactor-tcp-nio-2] reactor.util.Loggers$Slf4JLogger:274 onNext(a3105647-a4bc-4986-9ad4-1e6de901449f)
2020-10-08 11:29:19,664 INFO [reactor-tcp-nio-2] com.example.demo.PostRepositoryTest:31 generated id: a3105647-a4bc-4986-9ad4-1e6de901449f
//.....
2020-10-08 11:29:19,671 INFO [reactor-tcp-nio-2] reactor.util.Loggers$Slf4JLogger:274 onNext(a611d766-f983-4c8e-9dc9-fc78775911e5)
2020-10-08 11:29:19,671 INFO [reactor-tcp-nio-2] com.example.demo.PostRepositoryTest:31 generated id: a611d766-f983-4c8e-9dc9-fc78775911e5
//......

Process finished with exit code 0
Hantsy
  • 8,006
  • 7
  • 64
  • 109
  • is this solution uses batch? – jzqa Apr 19 '21 at 12:52
  • 1
    Note that some drivers (mariadb for example) require question marks "?, ?" instead of "$1, $2" for the binding to work. – Readren Jul 11 '21 at 04:08
  • 1
    @Readren every DB has its own parameter placeholder in the SQL, eg. mssql use a `@` prefix, pg use a `$`, etc. – Hantsy Jul 11 '21 at 04:17
  • 3
    An important note: to make sure that the inserts actually do happen, it is **required** to call `getRowsUpdated()` or `map(...)` on **each** returned `Result` and include their results in the reactive chain. It is not enough to just do something like `Flux.from(statement.execute()).then()`. – Roman Puchkovskiy Jul 12 '21 at 13:21
7

There are two questions:

Would DatabaseClient.insert() insert them in one batch or not?

Not a batch.

Is it possible to create batch insert in r2dbc? (except Connection.createBatch())

No, use Connection.createBatch() is only one way to create a Batch for now.

See also issues:

Honza Zidek
  • 9,204
  • 4
  • 72
  • 118
Mirro Mutth
  • 156
  • 4
  • If the INSERT commands are the same, `Connection.createStatement` is better than `Connection.createBatch`. See https://r2dbc.io/spec/0.8.5.RELEASE/spec/html/#statements.batching – Honza Zidek Oct 22 '21 at 03:22
0

Unfortunately still no built-in support. Only issue I had with the answer of @Hantsy was that I got the error No unfinished bindings!.

Below is an adjusted working solution (No statement.add() on the last binding!

    public class Post {
        @Id
        private Integer id;
        private String title;
        private String post;
    }
    
    public Flux<Post> saveAll(List<Post> postList) {
        return databaseClient.inConnectionMany(connection -> {
            Statement statement = connection.createStatement("insert into POST (TITLE, POST) " +
                            " VALUES ($1, $2)")
                    .returnGeneratedValues("ID", "TITLE", "POST");
            for (int i = 0; i < postList.size(); ) {
                Post post = postList.get(i);
                statement.bind(0, post.getTitle())
                        .bind(1, post.getPost());
                if (++i < postList.size()) {
                    statement.add();
                }
            }

            return Flux.from(statement.execute())
                    .flatMap(result -> (Flux<Post>) result.map((row, rowMetadata) -> {
                        Integer id = row.get("ID", Integer.class);
                        String post = row.get("POST", String.class);
                        String title = row.get("TITLE", String.class);
                        return new Post(id, post, title);
                    }));
        });
    }
Ghostwritertje
  • 31
  • 4