Spring R2DBC DatabaseClient execute custom delete query in PostgreSQL

Question

Hi im trying to delete user by his id from a table in Postgres database. Im using Spring and R2DBC and trying to use DatabaseClient.execute("sqlCommand") for my custom delete query:

import org.springframework.data.r2dbc.core.DatabaseClient;

@Service
@Transactional
public class CustomSqlService {
    private final DatabaseClient databaseClient;


  public Mono<Void> deleteWithCustomSql(String sql) {
            databaseClient.execute(sql)
            return Mono.empty();
    }

Where sql is "DELETE FROM user_table WHERE user_id = 1;"

Method in the Controller:


@RestController
@RequestMapping("postgres/")
@Timed
public class PostgresController {

// omitted code


   @DeleteMapping(path = "/delete_user/{userId}")
    public Mono<Void> deleteUser(@PathVariable("userId") Long userId)  {
       return customSqlService.deleteWithCustomSql("DELETE FROM user_table WHERE user_id = " + userId);
    }

But when I test it, command is not working. When i debug i can see there's MonoOnResumeError in the result from .execute().

I have other methods that perform insert and select statements in the same fashion and they work well.

The test I have for it:

 @Test
    void shouldDeleteDataFromTable() {
        User user = User.builder()
                .userId(1L)
                .sessionId(2L)
                .timestamp(10L)
                .build();

        webTestClient
                .post()
                .uri("/postgres/save_user")
                .contentType(MediaType.APPLICATION_JSON)
                .accept(MediaType.APPLICATION_JSON)
                .body(Mono.just(user), User.class)
                .exchange()
                .expectStatus().isOk()

     webTestClient
             .delete()
             .uri("/postgres/delete_user/1")
             .exchange()
             .expectStatus()
             .isOk();
      
        userRepository.findAll().take(1).as(StepVerifier::create)
                .expectNextCount(0)
                .verifyComplete();

How to correctly use databaseClient.execute() for custom delete query in PostgreSQL ?

Hantsy · Accepted Answer · 2023-01-12T01:55:42.990

1

Hope you are using the latest R2dbc 1.0 and Spring Data R2dbc(managed by Spring Boot 3.0).

Your method deleteWithCustomSql does not work. There is no subscription on the databaseCLient.exectue, the sql is never be executed and return a result.

Try to change to the following, move sql here, and use bind to bind a parameter to sql.

 public Mono<Long> deleteByUserId(Long userId) {
     return databaseClient.sql("DELETE FROM user_table WHERE user_id = :userId")
         .bind("userId", userId)
         .fetch()
         .rowsUpdated();
}

In the controller, changed to the following.

@DeleteMapping(path = "/users/{userId}")
public Mono<ResponseEntity> deleteUser(@PathVariable("userId") Long userId)  {
    return customSqlService.deleteByUserId(userId)
        .map(deleted -> {
            if(deleted>0) return noContent().build();
            else return notFound().build();
         });
}

Check my working example of delete operations, which demonstrates how to bind a parameter in sql, it is based on the latest Spring Data R2dbc/Postgres.

edited Jan 12 '23 at 01:55

answered Jan 11 '23 at 13:46

Hantsy

8,006
7
64
109

Thanks for the answer, I already solve this problem when I change returning type of Mono to Mono but I don't get why is that. Can You provide a link to some docs about subscription to operators like databaseClient.execute and why do I need to return some type and not the Mono ? – Baal Jan 12 '23 at 00:19
When client to the API endpoint, it will create a subscription, but your `dbClient.execute` is never subscribed by the client, read the [Reactive Streams](https://www.reactive-streams.org/) spec to under the publisher API. – Hantsy Jan 12 '23 at 01:48
Returning a `Mono` is so easy, you can `flatMap`, `then`, etc. method to convert or shift to a new publisher if you know well the APIs of [Reactor](https://projectreactor.io/). – Hantsy Jan 12 '23 at 01:50

Spring R2DBC DatabaseClient execute custom delete query in PostgreSQL

1 Answers1