javapostgresqlspring-bootsql-updateruntime-error

Spring error "Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)" when I try to update

In spring boot with Java I have an Entity class that contains a list of elements. I want to create an endpoint (POST? or PATCH?) from which I can add a new element to those present:

import java.time.OffsetDateTime;
import java.util.List;
import java.util.Set;
import java.util.UUID;
import jakarta.persistence.*;

import lombok.*;
import org.apache.commons.lang3.StringUtils;
import org.cnr.plantvocdb.enums.LeafHabitus;
import org.cnr.plantvocdb.enums.PlantsRanks;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Table(name = "plants_voc")
public class PlantEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name="id", length = 50, nullable = false, updatable = false)
    private UUID id;

    @Column(name="ipni", length = 50)
    private String ipni;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_plain", length = 50)
    private String fullNamePlain;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_no_authors_plain", length = 50)
    private String fullNameNoAuthorsPlain;

    @Setter(AccessLevel.NONE)
    @Column(name="plant_name", length = 30, nullable = false)
    private String name;

    @Setter(AccessLevel.NONE)
    @Column(name="family", length = 30, nullable = false)
    private String family;

    @Setter(AccessLevel.NONE)
    @Column(name="genus", length = 30, nullable = false)
    private String genus;

    @Setter(AccessLevel.NONE)
    @Column(name="species", length = 30, nullable = false)
    private String species;

    @Column(name="valid_nomenclature")
    private boolean validNomenclature;

    @Column(name="rank", length = 20)
    @Enumerated(EnumType.STRING)
    private PlantsRanks rank;

    @Column(name="leaf_habitus", length = 20)
    @Enumerated(EnumType.STRING)
    private LeafHabitus leafHabitus;

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = CascadeType.ALL,
            orphanRemoval = true,  // Ensures childs are deleted when plant is deleted
            mappedBy = "plant"
    )
    private List<PlantEmitterEntity> emitter;

    @ElementCollection
    @CollectionTable(
            name = "synonyms", // Nome della tabella intermedia
            joinColumns = @JoinColumn(name = "fk_synonyms_plant_id"),
            foreignKey = @ForeignKey(name = "FK_synonyms_plant")
    )
    @Column(name="synonyms")
    private Set<String> synonyms;

    @Column(name="created_datetime_utc", updatable = false) // creation_datetime_utc
    private OffsetDateTime createdDatetimeUTC;

    @Column(name="updated_datetime_utc")  // last_modified_datetime_utc
    private OffsetDateTime updatedDatetimeUTC;

    public void setName(String name) {

        this.name = StringUtils
                .normalizeSpace(name.toLowerCase());
    }

    public void setFamily(String family) {

        this.family = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(family.toLowerCase()));
    }

    public void setGenus(String genus) {

        this.genus = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(genus.toLowerCase()));
    }

    public void setSpecies(String species) {

        this.species = StringUtils
                .normalizeSpace(species.toLowerCase());
    }

    public void setFullNamePlain(String fullNamePlain) {
        this.fullNamePlain = StringUtils
                .normalizeSpace(fullNamePlain);
    }

    public void setFullNameNoAuthorsPlain(String fullNameNoAuthorsPlain) {
        this.fullNameNoAuthorsPlain = StringUtils
                .normalizeSpace(fullNameNoAuthorsPlain);
    }

    public void addEmitter(PlantEmitterEntity newEmitter) {
        emitter.add(newEmitter);
    }

}

The Entity class method addEmitter was specifically created to do this, that is, to add a new PlantEmitterEntity object to the existing ones

import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(name="emitters")
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class PlantEmitterEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name="emits", length = 50)
    private boolean emits;

    @Column(name="doi")
    private String doi;

    @ManyToOne(
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL
    )
    @JoinColumn(
            name = "fk_emitters_plant_id",
            nullable = false,
            updatable = true,
            insertable = true,
            foreignKey = @ForeignKey(name = "FK_emitters_plant"))
    private PlantEntity plant;

}

For this purpose, in the controller I created a special endpoint

    import io.swagger.v3.oas.annotations.Operation;
    import io.swagger.v3.oas.annotations.media.Content;
    import io.swagger.v3.oas.annotations.media.Schema;
    import io.swagger.v3.oas.annotations.responses.ApiResponse;
    import io.swagger.v3.oas.annotations.responses.ApiResponses;
    import io.swagger.v3.oas.annotations.tags.Tag;
    import jakarta.validation.Valid;
    import org.apache.commons.lang3.StringUtils;
    import org.cnr.plantvocdb.dto.PlantInfoDTO;
    import org.cnr.plantvocdb.dto.RequestPlantDTO;
    import org.cnr.plantvocdb.dto.ResponsePlantDTO;
    import org.cnr.plantvocdb.enums.LeafHabitus;
    import org.cnr.plantvocdb.enums.PlantsEmitterType;
    import org.cnr.plantvocdb.enums.PlantsRanks;
    import org.cnr.plantvocdb.exceptions.ErrorResponseDTO;
    import org.cnr.plantvocdb.exceptions.PlantNotFoundException;
    import org.cnr.plantvocdb.service.PlantsService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.MediaType;
    import org.springframework.http.ResponseEntity;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.bind.annotation.*;
    
    
    import java.text.MessageFormat;
    import java.util.List;
    import java.util.Optional;
    import java.util.UUID;
    
    @RestController
    @RequestMapping("/api/v1/isoprene")
    //@Tag(name = "Plant isoprene APIs")
    public class PlantsController {
    
        private final PlantsService service;
    
        @Autowired
        public PlantsController(PlantsService service) {
    
            this.service = service;
        }
    
    
        /*
        *
        * Endpoints POST
        *
        * */
    
        @Tag(name = "Collection of methods used for creating data")
        @PostMapping("/plants/emitter")
        public ResponseEntity<ResponsePlantDTO> addEmitterById(
                @RequestParam(value = "id") UUID id,
                @RequestParam(value = "emits") boolean emits,
                @RequestParam(value = "doi") String doi
        ){
            Optional<ResponsePlantDTO> optionalResponsePlantDTO = service.findPlantById(id);
            if(optionalResponsePlantDTO.isPresent()){
                ResponsePlantDTO plantDTO = optionalResponsePlantDTO.get();
                service.addEmitter(plantDTO, emits, doi);
    
            }
    
           //TODO: end with updated resource or Exeception            
           return null;
        }
    }

In service I do two operations. The first I delete the record using its ID and the second I save the record again (with the added element) always using its ID

import org.apache.commons.lang3.StringUtils;
import org.cnr.plantvocdb.dto.PlantEmitterDTO;
import org.cnr.plantvocdb.dto.PlantInfoDTO;
import org.cnr.plantvocdb.dto.RequestPlantDTO;
import org.cnr.plantvocdb.dto.ResponsePlantDTO;
import org.cnr.plantvocdb.entity.PlantEmitterEntity;
import org.cnr.plantvocdb.entity.PlantEntity;
import org.cnr.plantvocdb.enums.LeafHabitus;
import org.cnr.plantvocdb.enums.PlantsEmitterType;
import org.cnr.plantvocdb.enums.PlantsRanks;
import org.cnr.plantvocdb.repository.PlantsRepository;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

@Service
public class PlantsService {

    private final PlantsRepository repository;
    private final ModelMapper mapper;


    @Autowired
    public PlantsService(PlantsRepository repository,
                         ModelMapper mapper) {
        this.repository = repository;
        this.mapper = mapper;

    }

    // POST
    public ResponsePlantDTO addEmitter(
            ResponsePlantDTO plantDTO,
            boolean emits,
            String doi
    ){
        PlantEntity plantEntity = mapper.map(plantDTO, PlantEntity.class);
        plantEntity.setUpdatedDatetimeUTC(OffsetDateTime.now(ZoneOffset.UTC));
        plantEntity.addEmitter(PlantEmitterEntity
                .builder()
                .emits(emits)
                .doi(doi)
                .build()
        );
        // iterate over the set to add plantEntity
        plantEntity.getEmitter().forEach(it -> it.setPlant(plantEntity));
        // delete plant by ID in order to save the updated plant
        repository.deleteById(plantEntity.getId());
        PlantEntity updatedPlantEntity = repository.save(plantEntity);
        // TODO: return new object
        return null;   
    }
}

When I run the service I get this error when I try to create the object with the updated list

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [org.cnr.plantvocdb.entity.PlantEntity#9333c963-bba1-4780-a16c-01a1d6ce7920]

Solution

  • Instead of deleting and re-saving, simply update the object and add a new element to the list.

    Updated addEmitter() Method in PlantsService.

    public ResponsePlantDTO addEmitter(UUID plantId, boolean emits, String doi) {
    // Find the existing record in the database
    Optional<PlantEntity> optionalPlant = repository.findById(plantId);
    if (optionalPlant.isEmpty()) {
        throw new PlantNotFoundException("Plant with ID " + plantId + " not found.");
    }

    // Retrieve the entity from the database
    PlantEntity plantEntity = optionalPlant.get();
    plantEntity.setUpdatedDatetimeUTC(OffsetDateTime.now(ZoneOffset.UTC));

    // Create a new PlantEmitterEntity object
    PlantEmitterEntity newEmitter = PlantEmitterEntity.builder()
            .emits(emits)
            .doi(doi)
            .plant(plantEntity) // Associate it with the existing plant
            .build();

    // Add the new emitter to the list
    plantEntity.getEmitter().add(newEmitter);

    // Save the updated entity (no need to delete it first)
    PlantEntity updatedPlant = repository.save(plantEntity);

    // Convert back to DTO and return
    return mapper.map(updatedPlant, ResponsePlantDTO.class);
}

    Controller

    Now, the controller should pass plantId to the service method:

    @PostMapping("/plants/emitter")
public ResponseEntity<ResponsePlantDTO> addEmitterById(
        @RequestParam(value = "id") UUID id,
        @RequestParam(value = "emits") boolean emits,
        @RequestParam(value = "doi") String doi
) {
    ResponsePlantDTO updatedPlant = service.addEmitter(id, emits, doi);
    return ResponseEntity.ok(updatedPlant);
}

    Why is POST used here?

    1. Adding an element to a collection rather than modifying an existing resource
    1. PATCH is meant for partial updates of fields, not for extending a list

    Thus, POST is the logical choice because it aligns with HTTP semantics and RESTful standards.

    UPDATE:

    Maybe the issue is here:

    PlantEntity plantEntity = mapper.map(plantDTO, PlantEntity.class);

    You’re taking a DTO, mapping it into a new PlantEntity object, and then immediately deleting the old one from the database:

    repository.deleteById(plantEntity.getId());
PlantEntity updatedPlantEntity = repository.save(plantEntity);

    The problem is that Hibernate sees this object as new, and because of orphanRemoval = true, it’s likely removing all associated PlantEmitterEntity records.

    Instead of creating a new PlantEntity, try retrieving it from the database, adding the new emitter