Home > Enterprise >  Duplicate Key with CascadeType.ALL while inserting multiple tables in Spring
Duplicate Key with CascadeType.ALL while inserting multiple tables in Spring

Time:01-17

I have a Spring application and in one of the Controllers lies a POST method that allows a user to create multiple entities at once, since they're all related with one main entity, EncodingResult.

After testing a bit, it seemed that this caused the following error: object references an unsaved transient instance. Looking around a bit, I found that the solution to it was to use CascadeType.ALL on the attributes that have @JoinColumn annotations in this main class, as such:

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "codec", referencedColumnName = "commit_hash", nullable = false)
    private Codec codec;

The Codec side is as follows:

    (...)
    
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "codec")
    private Set<EncodingResult> associatedResults = new HashSet<>();

    @Column(name = "commit_hash", nullable = false, unique = true)
    // indicates the precise version of the codec
    private String commitHash;

    (...)

This, however, seems to cause another error: PSQLException: ERROR: duplicate key value violates unique constraint "uk_paitxovhx3j95tuaq9krkk9qh" Detail: Key (commit_hash)=(ec44ee022959410f9596175b9424d9fe1ece9bc8) already exists.

To my understanding, this must be because the CascadeType.PERSIST tries to update the related entities when the parent is updated no matter what (which would make sense, given that in the first POST attempt the system works fine, but in the subsequent ones, which do not save Codec, for example, if there is already one such entity with the given commitHash it doesn't), but I am not sure if this is correct.

Either way, how can I make sure that this operation succeeds? Is it even possible to do what I am trying to do here?

Below is the JSON body being used:

{
    "codec": {
        "name": "vvcodec",
        "commitHash": "ec44ee022959410f9596175b9424d9fe1ece9bc8",
        "codecAttrs": "--threads 1",
        "preset": ""
    },
    "video": {
        "name": "bowing_cif",
        "resolution": "352x288",
        "fps": 29.97,
        "nFrames": 300,
        "format": "y4m",
        "uniqueAttrs": "fps29.97_nf300_fy4m_r352x288_nbowing_cif"
    },
    "encodingConfig": {
        "uniqueAttrs": "qp27_nf30_nt8_ca",
        "qp": 27,
        "nFrames": 30,
        "nThreads": 8,
        "codecAttrs": ""
    },
    "ypsnr": 48.2012,
    "upsnr": 49.5337,
    "vpsnr": 51.1360,
    "yuvpsnr": 48.749,
    "bitrate": 196.1958,
    "time": 0.595,
    "energyConsumption": 78.32 
}

EDIT: I suppose this will also help - my implementation of the @Service layer for the given method:

public EncodingResult saveEncodingResult(EncodingResult encodingResult) {
        var video = encodingResult.getVideo();
        var encodingConfig = encodingResult.getEncodingConfig();
        var codec = encodingResult.getCodec();

        if (videoRepository.findByUniqueAttrs(video.getUniqueAttrs()).isEmpty())
            videoRepository.save(video);
        if (encodingConfigRepository.findByUniqueAttrs(encodingConfig.getUniqueAttrs()).isEmpty())
            encodingConfigRepository.save(encodingConfig);
        if (codecRepository.findByCommitHash(codec.getCommitHash()).isEmpty())
            codecRepository.save(codec);

        return encodingResultRepository.save(encodingResult);
    }

CodePudding user response:

Do you receive EncodingResult in your controller method? If so, hibernate has no idea that Codec already exists because you're creating a new Codec object every time (well, spring does, by deserializing json). Upon save, hibernate tries to insert new Codec because of CascadeType.ALL instead of using an existing entity.

Typically, you would fetch an existing entity from the database, update it's state and then save it - that is unless you're creating a new entity.

Second thing, if EncodingResult is a child of Codec it really shouldn't have CascadeType.ALL on that relation - apart from being able to insert new Codec when saving EncodingResult, you're also specifying that REMOVE operation will be cascaded from EncodingResult to Codec.

  • Related