R2DBC: Why are RowsFetchSpec operators (.all(),.first(),.one()) signal onComplete although record is stored in database?

Question

I am using DatabaseClient for building a custom Repository. After I insert or update an Item I need that Row data to return the saved/updated Item. I just can´t wrap my head around why .all(), .first(), .one() are not returning the Result Map, although I can see that the data is inserted/updated in the database. They just signal onComplete. But .rowsUpdated() returns 1 row updated. I observed this behaviour with H2 and MS SQL Server. I´m new to R2dbc. What am I missing? Any ideas?

    @Transactional
    public Mono<Item> insertItem(Item entity){
        return dbClient
                .sql("insert into items (creationdate, name, price, traceid, referenceid) VALUES (:creationDate, :name, :price, :traceId, :referenceId)")
                .bind("creationDate", entity.getCreationDate())
                .bind("name", entity.getName())
                .bind("price", entity.getPrice())
                .bind("traceId", entity.getTraceId())
                .bind("referenceId", entity.getReferenceId())
                .fetch()
                .first() //.all() //.one()
                .map(Item::new)
                .doOnNext(item -> LOGGER.info(String.format("Item: %s", item)));
    }

The table looks like this:

CREATE TABLE [dbo].[items](
    [creationdate] [bigint] NOT NULL,
    [name] [nvarchar](32) NOT NULL,
    [price] [int] NOT NULL,
    [traceid] [nvarchar](64) NOT NULL,
    [referenceid] [int] NOT NULL,   
 PRIMARY KEY (name, referenceid)
)

Thanks!

score 1 · Accepted Answer · answered Feb 05 '22 at 04:29

This is the behavior of an insert/update statement in database, it does not return the inserted/updated rows. It returns the number of inserted/updated rows. It may also return some generated values by the database (such as auto increment, generated uuid...), by adding the following line:

.filter(statement -> statement.returnGeneratedValues())

where you may specify specific generated columns in parameter. However this has limitations depending on the database (for example MySql can only return the last generated ID of an auto increment column even if you insert multiple rows).

If you want to get the inserted/updated values from database, you need to do a select.

R2DBC: Why are RowsFetchSpec operators (.all(),.first(),.one()) signal onComplete although record is stored in database?

1 Answers1