Insert Query using @Query in R2DBCRepository SpringWebFlux not Working

Question

Seems Composite Key/@Embedded are not supported in R2DBC - link , I am trying to insert data using @Query annotation like below.

      @Repository("testRepository")
      public interface TestRepository extends R2dbcRepository<Test, testEmbeddedId> {
    
      @Modifying
      @Query(value = "insert into test(id, name, status, created_date) VALUES ('b128f97d-d52c-4677-8746-00e2959c9ec6', 'c', 'd', '2022-10-28')")
      @Transactional
      void insertData();
    }

But this is failed without giving any error. Any idea about this issue or any alternative approach here.

You do not seem to use composite keys though. How does your entity look like? — Dominik, Dec 05 '22 at 01:57

score 0 · Accepted Answer · answered Dec 05 '22 at 02:08

I think that the signature of your method insertData should return a Mono<Test> on which you should subscribe to execute the insert statement, i.e. declare method insertData as

@Modifying
@Query(value = "insert into test(id, name, status, created_date) VALUES ('b128f97d-d52c-4677-8746-00e2959c9ec6', 'c', 'd', '2022-10-28')")
@Transactional
Mono<Test> insertData();

and then execute it as

insertData().subscribe();

This worked for me with a simple primary key.

Thanks Dominik, it worked. Both id, and created_date fields are primary keys here. — lahirumw, Dec 23 '22 at 07:33

score 0 · Answer 2 · answered Dec 10 '22 at 08:24

Suggestion - move the @Transactional higher up to the service method calling the insert. Being reactive, nothing will happen until you subscribe. (as per Dominik). Setup these two properties in the application.properties(.yaml), change the level to DEBUG, and then you will at least see the SQL when it gets executed.

org.springframework.r2dbc.connection: INFO
org.springframework.r2dbc: INFO

I also prefer to pass the values to use as variables like this:

 @Query(FLAG_OLDER_DOCUMENTS)
 Mono<Void> flagOlderDocumentsAsReplaced(Long customerId, String documentType, String documentSubType);

 String FLAG_OLDER_DOCUMENTS = """
              UPDATE customer_documents cd
                 SET cd.document_status = 'REPLACED'
                 WHERE cd.customer_id = :customerId
                 AND cd.document_type = :documentType
                 AND (:documentSubType is null or cd.document_sub_type = :documentSubType)
         """;

Thanks Andre. You are correct Transactional should be in service layer. — lahirumw, Dec 23 '22 at 07:35

Insert Query using @Query in R2DBCRepository SpringWebFlux not Working

2 Answers2