reactive repository throws exception when saving a new object

Question

I am using r2dbc, r2dbc-h2 and experimental spring-boot-starter-data-r2dbc

implementation 'org.springframework.boot.experimental:spring-boot-starter-data-r2dbc:0.1.0.M1'
implementation 'org.springframework.data:spring-data-r2dbc:1.0.0.RELEASE' // starter-data provides old version
implementation 'io.r2dbc:r2dbc-h2:0.8.0.RELEASE'
implementation 'io.r2dbc:r2dbc-pool:0.8.0.RELEASE'

I have created reactive repositories

public interface IJsonComparisonRepository extends ReactiveCrudRepository<JsonComparisonResult, String> {}

Also added a custom script that creates a table in H2 on startup

@SpringBootApplication
public class JsonComparisonApplication {
    public static void main(String[] args) {
        SpringApplication.run(JsonComparisonApplication.class, args);
    }

    @Bean
    public CommandLineRunner startup(DatabaseClient client) {
        return (args) -> client
            .execute(() -> {
                var resource = new ClassPathResource("ddl/script.sql");
                try (var is = new InputStreamReader(resource.getInputStream())) {
                    return FileCopyUtils.copyToString(is);
                } catch (IOException e) {
                    throw new RuntimeException(e);
                } })
            .then()
            .block();
    }
}

My r2dbc configuration looks like this

@Configuration
@EnableR2dbcRepositories
public class R2dbcConfiguration extends AbstractR2dbcConfiguration {
    @Override
    public ConnectionFactory connectionFactory() {
        return new H2ConnectionFactory(
            H2ConnectionConfiguration.builder()
                .url("mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE")
                .username("sa")
                .build());
    }
}

My service where I perform the logic looks like this

@Override
public Mono<JsonComparisonResult> updateOrCreateRightSide(String comparisonId, String json) {
    return updateComparisonSide(comparisonId, storedComparisonResult -> {
        storedComparisonResult.setRightSide(json);
        return storedComparisonResult;
    });
}

private Mono<JsonComparisonResult> updateComparisonSide(String comparisonId,
                                                        Function<JsonComparisonResult, JsonComparisonResult> updateSide) {
    return repository.findById(comparisonId)
        .defaultIfEmpty(createResult(comparisonId))
        .filter(result -> ComparisonDecision.NONE == result.getDecision()) // if not NONE - it means it was found and completed
        .switchIfEmpty(Mono.error(new NotUpdatableCompleteComparisonException(comparisonId)))
        .map(updateSide)
        .flatMap(repository::save);

}

private JsonComparisonResult createResult(String comparisonId) {
    LOGGER.info("Creating new comparison result: {}.", comparisonId);
    var newResult = new JsonComparisonResult();
    newResult.setDecision(ComparisonDecision.NONE);
    newResult.setComparisonId(comparisonId);
    return newResult;
}

The domain looks like this

@Table("json_comparison")
public class JsonComparisonResult {
    @Column("comparison_id")
    @Id
    private String comparisonId;
    @Column("left")
    private String leftSide;
    @Column("right")
    private String rightSide;
    // @Enumerated(EnumType.STRING) - no support for now
    @Column("decision")
    private ComparisonDecision decision;
    private String differences;

The problem is that when I try to add any object to the database it fails with the exception

org.springframework.dao.TransientDataAccessResourceException: Failed to update table [json_comparison]. Row with Id [4] does not exist.
    at org.springframework.data.r2dbc.repository.support.SimpleR2dbcRepository.lambda$save$0(SimpleR2dbcRepository.java:91) ~[spring-data-r2dbc-1.0.0.RELEASE.jar:1.0.0.RELEASE]
    at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:96) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:73) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.MonoUsingWhen$MonoUsingWhenSubscriber.deferredComplete(MonoUsingWhen.java:276) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.FluxUsingWhen$CommitInner.onComplete(FluxUsingWhen.java:536) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:1858) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.Operators.complete(Operators.java:132) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:45) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]
    at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52) ~[reactor-core-3.3.1.RELEASE.jar:3.3.1.RELEASE]

For some reason during save in SimpleR2dbcRepository library class it doesn't consider the objectToSave as new, but then it fails to update as it is in reality doesn't exist.

// SimpleR2dbcRepository#save
@Override
@Transactional
public <S extends T> Mono<S> save(S objectToSave) {

    Assert.notNull(objectToSave, "Object to save must not be null!");

    if (this.entity.isNew(objectToSave)) { // not new
        ....
    }
}

Why it is happening and what is the problem?

Answer 1

Removing @ID issued insert statement

Answer 2

May be you should consider this: Choose data type of your id/Primary Key as INT/LONG and set it to AUTO_INCREMENT (something like below):

CREATE TABLE PRODUCT(id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, modelname VARCHAR(30) , year VARCHAR(4), owner VARCHAR(50)); In your post request body, do not include id field.

Answer 3

You have to implement Persistable because you’ve provided the @Id. The library needs to figure out, whether the row is new or whether it should exist. If your entity implements Persistable, then save(…) will use the outcome of isNew() to determine whether to issue an INSERT or UPDATE.

For example:

public class Product implements Persistable<Integer> {

    @Id
    private Integer id;
    private String description;
    private Double price;

    @Transient
    private boolean newProduct;

    @Override
    @Transient
    public boolean isNew() {
        return this.newProduct || id == null;
    }

    public Product setAsNew() {
        this.newProduct = true;
        return this;
    }
}

Answer 4

TL;DR: How should Spring Data know if your object is new or whether it should exist?

Relational Spring Data Repositories (both, JDBC and R2DBC) must differentiate on [Reactive]CrudRepository.save(…) whether the given object is new or whether it exists in your database. Performing a save(…) operation results either in an INSERT or UPDATE statement. Issuing the wrong statement either causes a primary key violation or a no-op as standard SQL does not have a way to express an upsert.

Spring Data JDBC|R2DBC use by default the presence/absence of the @Id value. Generated primary keys are a widely used mechanism. If the primary key is provided, the entity is considered existing. If the id value is null, the entity is considered new.

Read more in the reference documentation about Entity State Detection Strategies.