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?

score 20 · Answer 1 · answered Dec 24 '19 at 12:56

20

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.

answered Dec 24 '19 at 12:56

mp911de

17,546
2
55
95

So basically if I use `@Id` I must not provide it manually or something? Because originally (before going reactive) everything worked fine. – lapots Dec 24 '19 at 12:58
You have various options of which an absent `@Id` is only one option. You can also implement the `Persistable` interface by providing your own `isNew()` implementation. – mp911de Dec 24 '19 at 13:00
hm, so basically `spring-data-jpa` used some custom version of `persistable` or `entityInformation`? – lapots Dec 24 '19 at 13:02
2

No, Spring Data JPA follows the same mechanism. It's JPA itself that recovers from a wrong `isNew()` hint. JPA has the notion of attached entities that help JPA to determine whether an entity is new. If JPA cannot determine whether an entity is new, it issues a `SELECT` before `INSERT`/`UPDATE` to synchronize with the database state. – mp911de Dec 24 '19 at 13:05
can I wrap my existing entity into some proxy and use it with `persistable` interface instead of implementing `persistable` for domain? – lapots Dec 24 '19 at 13:08
Thanks for this, in my case I was overriding my `isNew` implementation in my POJO and it was returning false for `isNew` when it really was new due to a logical error. – buddyp450 Nov 09 '20 at 20:09
@mp911de Could you please have a look at https://stackoverflow.com/q/72234642/2886891 ? – Honza Zidek May 13 '22 at 19:44

score 17 · Answer 2 · answered Jun 17 '20 at 11:09

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;
    }
}

score 0 · Answer 3 · answered Jul 22 '21 at 10:08

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.

score 0 · Answer 4 · answered Nov 15 '22 at 03:58

0

Removing @ID issued insert statement

answered Nov 15 '22 at 03:58

Murugadasan Dhandapani

21
4

2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '22 at 05:47

reactive repository throws exception when saving a new object

4 Answers4

Linked