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-12 01:55:42Z

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, 2023 at 1:55

answered Jan 11, 2023 at 13:46

Hantsy

9,61111 gold badges75 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baal Over a year ago

Thanks for the answer, I already solve this problem when I change returning type of Mono<Void> to Mono<Integer> 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<Void> ?

Hantsy Over a year ago

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 spec to under the publisher API.

Hantsy Over a year ago

Returning a Mono<Void> 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.

Collectives™ on Stack Overflow

Spring R2DBC DatabaseClient execute custom delete query in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest