Spring Boot后端删除Score记录失败，触发唯一键重复异常

开发Java Spring Boot后端，submitAnswers方法接收人格测验的问答JSON数组，为当前登录用户计算并分配人格得分。设计逻辑为：若用户已关联Score记录，先删除旧记录，但实际执行时旧记录未被删除，调用/submit-answers接口抛出java.sql.SQLIntegrityConstraintViolationException，提示“Duplicate entry '153' for key 'score.user_entity_id'”（153为当前用户ID）。

相关代码片段 #

QuizController代码 #

package com.wang.app.rest.Controller;

import com.wang.app.rest.Models.*;
import com.wang.app.rest.Repo.ScoreRepository;
import com.wang.app.rest.Repo.UserRepository;
import jakarta.annotation.PostConstruct;
import jakarta.transaction.Transactional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.security.core.annotation.AuthenticationPrincipal;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.wang.app.rest.service.ScoreCalculationService;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class QuizController {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private ScoreRepository scoreRepository;

    @Autowired
    private ScoreCalculationService scoreCalculationService;

    private Map<Integer, AnswerInfo> questionToMap = new HashMap<>();

    @PostConstruct
    public void initQuestionToMap() {
        questionToMap.put(1, new AnswerInfo(1, "+"));
        questionToMap.put(2, new AnswerInfo(2, "-"));
        questionToMap.put(3, new AnswerInfo(3, "+"));
        questionToMap.put(4, new AnswerInfo(4, "-"));
        questionToMap.put(5, new AnswerInfo(5, "+"));
        questionToMap.put(6, new AnswerInfo(1, "-"));
        questionToMap.put(7, new AnswerInfo(2, "+"));
        questionToMap.put(8, new AnswerInfo(3, "-"));
        questionToMap.put(9, new AnswerInfo(4, "+"));
        questionToMap.put(10, new AnswerInfo(5, "-"));
        questionToMap.put(11, new AnswerInfo(1, "+"));
        questionToMap.put(12, new AnswerInfo(2, "-"));
        questionToMap.put(13, new AnswerInfo(3, "+"));
        questionToMap.put(14, new AnswerInfo(4, "-"));
        questionToMap.put(15, new AnswerInfo(5, "+"));
        questionToMap.put(16, new AnswerInfo(1, "-"));
        questionToMap.put(17, new AnswerInfo(2, "+"));
        questionToMap.put(18, new AnswerInfo(3, "-"));
        questionToMap.put(19, new AnswerInfo(4, "+"));
        questionToMap.put(20, new AnswerInfo(5, "-"));
        questionToMap.put(21, new AnswerInfo(1, "+"));
        questionToMap.put(22, new AnswerInfo(2, "-"));
        questionToMap.put(23, new AnswerInfo(3, "+"));
        questionToMap.put(24, new AnswerInfo(4, "-"));
        questionToMap.put(25, new AnswerInfo(5, "+"));
        questionToMap.put(26, new AnswerInfo(1, "-"));
        questionToMap.put(27, new AnswerInfo(2, "+"));
        questionToMap.put(28, new AnswerInfo(3, "-"));
        questionToMap.put(29, new AnswerInfo(4, "+"));
        questionToMap.put(30, new AnswerInfo(5, "-"));
        questionToMap.put(31, new AnswerInfo(1, "+"));
        questionToMap.put(32, new AnswerInfo(2, "-"));
        questionToMap.put(33, new AnswerInfo(3, "+"));
        questionToMap.put(34, new AnswerInfo(4, "-"));
        questionToMap.put(35, new AnswerInfo(5, "+"));
        questionToMap.put(36, new AnswerInfo(1, "-"));
        questionToMap.put(37, new AnswerInfo(2, "+"));
        questionToMap.put(38, new AnswerInfo(3, "-"));
        questionToMap.put(39, new AnswerInfo(4, "+"));
        questionToMap.put(40, new AnswerInfo(5, "-"));
        questionToMap.put(41, new AnswerInfo(1, "+"));
        questionToMap.put(42, new AnswerInfo(2, "-"));
        questionToMap.put(43, new AnswerInfo(3, "+"));
        questionToMap.put(44, new AnswerInfo(4, "-"));
        questionToMap.put(45, new AnswerInfo(5, "+"));
        questionToMap.put(46, new AnswerInfo(1, "-"));
        questionToMap.put(47, new AnswerInfo(2, "+"));
        questionToMap.put(48, new AnswerInfo(3, "-"));
        questionToMap.put(49, new AnswerInfo(4, "+"));
        questionToMap.put(50, new AnswerInfo(5, "-"));
    }

    @PostMapping("/submit-answers")
    @Transactional
    public ResponseEntity<Score> submitAnswers(@RequestBody Map<Integer, Integer> answersData, @AuthenticationPrincipal CustomUserDetails customUserDetails) {
        UserEntity currentUserEntity = customUserDetails.getUserEntity();

        // If currentUserEntity is a new entity (not yet saved), save it first so it has an ID
        if (currentUserEntity.getId() == null) {
            userRepository.save(currentUserEntity);
        } else if (currentUserEntity.getScore() != null) {
//            Score oldScore = currentUserEntity.getScore();// Unlink from the score
//            currentUserEntity.setScore(null); // Save the user entity without the score///should delete it all
//            scoreRepository.delete(oldScore); // Delete old score from database
            Score scoreRemove = scoreRepository.findById(currentUserEntity.getScore().getId()).get();
            scoreRepository.delete(scoreRemove);
        }

        // Creating answer objects and associating them with the user
        List<Answer> answers = new ArrayList<>();
        for (Map.Entry<Integer, Integer> entry : answersData.entrySet()) {
            Integer questionId = entry.getKey();
            Integer answerValue = entry.getValue();

            Answer answer = new Answer(questionId, answerValue);
            answer.setUserEntity(currentUserEntity); // Associate the answer with the user
            answers.add(answer);  // Add the answer to the answers list
        }

        // Set answers for the user
        currentUserEntity.setAnswers(answers);

        // Calculating score based on answers
        Score score = (scoreCalculationService.calculateScore(answers, questionToMap));//buggy still if i do score.setUsereNTTIY...
//        score.setUserEntity(currentUserEntity);//the culprit here...
        score.setUserEntity(currentUserEntity); // Set the relationship in the Score entity first
        currentUserEntity.setScore(score);
        currentUserEntity.setUsername(customUserDetails.getUsername());

        // Save the user entity which should cascade the saves to Score and Answer
        userRepository.save(currentUserEntity);

        return ResponseEntity.ok(score);
   }
}

Score实体类代码 #

package com.wang.app.rest.Models;

import jakarta.persistence.*;

@Entity
public class Score {

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

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_entity_id")
    private UserEntity user;

    @Column
    private int Extraversion;

    @Column
    private int Agreeableness;

    private int Conscientiousness;

    private int EmotionalStability;

    private int Intellect;

    public void setId(Long id) {
        this.id = id;
    }

    public Long getId() {
        return id;
    }

    public int getExtraversion() {
        return Extraversion;
    }

    public void setExtraversion(int extraversion) {
        Extraversion = extraversion;
    }

    public int getAgreeableness() {
        return Agreeableness;
    }

    public void setAgreeableness(int agreeableness) {
        Agreeableness = agreeableness;
    }

    public int getConscientiousness() {
        return Conscientiousness;
    }

    public void setConscientiousness(int conscientiousness) {
        Conscientiousness = conscientiousness;
    }

    public int getEmotionalStability() {
        return EmotionalStability;
    }

    public void setEmotionalStability(int emotionalStability) {
        EmotionalStability = emotionalStability;
    }
    public int getIntellect() {
        return Intellect;
    }

    public void setIntellect(int intellect) {
        Intellect = intellect;
    }

    public UserEntity getUserEntity() {
        return user;
    }

    @Override
    public String toString() {
        return "Score{" +
                "extraversion=" + getExtraversion() +
                ", agreeableness=" + getAgreeableness() +
                ", conscientiousness=" + getConscientiousness() +
                ", emotionalStability=" + getEmotionalStability()+
                ", intellect=" +getIntellect() +
                '}';
    }
    public void setUserEntity(UserEntity user) {
        this.user = user;
    }
}

UserEntity实体类代码 #

package com.wang.app.rest.Models;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;

@Table(name = "users")
@Data
@NoArgsConstructor
@Entity
public class UserEntity {

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

    @OneToOne
    @JoinColumn(name="id")
    @MapsId
    private User user;

    private String username;

    private String password = "defaultPassword";

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    private List<Role> roles = new ArrayList<>();

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Answer> answers;

    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private Score score;

    public void setScore(Score score){
        this.score = score;
    }
}

原因分析 #

1. 双向OneToOne关联未正确维护：删除旧Score时仅调用了scoreRepository.delete(scoreRemove)，但未将currentUserEntity中的score引用置为null。Hibernate持久化上下文仍认为用户与旧Score存在关联，可能回滚删除操作或忽略逻辑。
2. 持久化上下文未刷新：删除操作后未强制刷新，导致数据库旧记录未立即删除，插入新Score时触发唯一键冲突。
3. 事务内操作顺序问题：删除旧Score后直接创建新Score关联用户，Hibernate可能先执行插入SQL，再执行删除SQL，引发短暂的唯一键冲突。

解决方案 #

方案1：正确维护双向关联并强制刷新 #

修改旧Score删除逻辑，先解除双向关联再删除并刷新：

else if (currentUserEntity.getScore() != null) {
    Score oldScore = currentUserEntity.getScore();
    // 解除双向关联
    currentUserEntity.setScore(null);
    oldScore.setUserEntity(null);
    // 删除旧Score
    scoreRepository.delete(oldScore);
    // 强制刷新持久化上下文
    userRepository.flush();
}

方案2：利用orphanRemoval特性简化逻辑 #

UserEntity的score字段已配置orphanRemoval = true，只需置空用户的score引用，Hibernate会自动删除旧记录：

else if (currentUserEntity.getScore() != null) {
    // 置空引用触发自动删除
    currentUserEntity.setScore(null);
    userRepository.save(currentUserEntity);
    userRepository.flush();
}

方案3：调整操作顺序确保删除先执行 #

创建新Score前，确保旧Score删除操作已提交到数据库：

else if (currentUserEntity.getScore() != null) {
    Score scoreRemove = scoreRepository.findById(currentUserEntity.getScore().getId()).get();
    scoreRepository.delete(scoreRemove);
    scoreRepository.flush();
    // 解除用户与旧Score的关联
    currentUserEntity.setScore(null);
}

额外优化建议 #

• 在Score实体的setUserEntity方法中添加双向关联维护逻辑：

  public void setUserEntity(UserEntity user) {
      this.user = user;
      if (user != null && user.getScore() != this) {
          user.setScore(this);
      }
  }
  

• 优化UserEntity的setScore方法维护双向关联：

  public void setScore(Score score){
      this.score = score;
      if (score != null && score.getUserEntity() != this) {
          score.setUserEntity(this);
      }
  }
  

内容的提问来源于stack exchange，提问作者Jasmine