java - 无法执行语句；SQL [不适用]；约束 [PRIMARY]; 嵌套异常是 org.hibernate.exception.ConstraintViolationException

Question

SQL [不适用]；约束[主要]；

我使用 Java/Spring Boot 创建了一个 CRUD RESTful API 应用程序。我有一个 MariaDB，里面有员工列表和相关数据。我使用 MySQL Workbench 作为我的 SQL 客户端。

运行 Spring Boot 应用程序时没有错误。我的数据库中没有错误。当我在我的数据库中获取所有工程师的 API 时没有错误。

我的“GET”请求工作正常；但是，“POST”没有。我无法创建新员工，而无需使用 MySQL WB 直接插入数据库。

我希望能够通过Postman 使用 API 创建新员工。

正如你所看到的，有一个 ConstraintViolationException 被抛出。约束是我的主键，即“id”。我已在 MySQL WB 中将其设置为自动增量。

这是我的工程师课程的代码：

package engineermanagement.model;

import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;

@Entity
@Table(name = "Engineers")
@EntityListeners(AuditingEntityListener.class)
public class Engineer {

    private Long id;
    private String firstName;
    private String lastName;
    private String sid;
    private String email;
    private String manager;
    private Boolean teamLead;
    private String groupName;
    private String shift;
    private int startTime;
    private int endTime;


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }

    @Column(name = "first_name", nullable = false)
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    @Column(name = "last_name", nullable = false)
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    @Column(name = "eid", nullable = false)
    public String getEid() {
        return eid;
    }
    public void setEid(String eid) {
        this.sid = eid;
    }

    @Column(name = "email", nullable = false)
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

    @Column(name = "manager", nullable = false)
    public String getManager() {
        return manager;
    }
    public void setManager(String manager) {
        this.manager = manager;
    }

    @Column(name = "teamlead", nullable = false)
    public Boolean getTeamLead() {
        return teamLead;
    }
    public void setTeamLead(Boolean teamLead) {
        this.teamLead = teamLead;
    }

    @Column(name = "group_name", nullable = false)
    public String getGroupName() {
        return groupName;
    }
    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }

    @Column(name = "shift", nullable = false)
    public String getShift() {
        return shift;
    }
    public void setShift(String shift) {
        this.shift = shift;
    }

    @Column(name = "start_time", nullable = false)
    public int getStartTime() {
        return startTime;
    }
    public void setStartTime(int startTime) {
        this.startTime = startTime;
    }

    @Column(name = "end_time", nullable = false)
    public int getEndTime() {
        return endTime;
    }
    public void setEndTime(int endTime) {
        this.endTime = endTime;
    }
}

这是我的EngineerController 类的代码：

package jpmchase.controller;

import jpmchase.exception.*;
import jpmchase.model.Engineer;
import jpmchase.repository.EngineerRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("/api/v1")
public class EngineerController {

    @Autowired
    private EngineerRepository engineerRepository;

    @GetMapping("/engineers")
    public List<Engineer> getAllEngineers() {
        return engineerRepository.findAll();
    }

    @GetMapping("/engineers/{id}")
    public ResponseEntity<Engineer> getEngineerById(
            @PathVariable(value = "id") Long engineerId) throws ResourceNotFoundException {
        Engineer engineer = engineerRepository.findById(engineerId)
                .orElseThrow(() -> new ResourceNotFoundException("Engineer not found on :: "+ engineerId));
        return ResponseEntity.ok().body(engineer);
    }

    @PostMapping("/engineers")
    public Engineer createEngineer(@Valid @RequestBody Engineer engineer) {
        return engineerRepository.save(engineer);
    }

    @PutMapping("/engineers/{id}")
    public ResponseEntity<Engineer> updateEngineer(
            @PathVariable(value = "id") Long engineerId,
            @Valid @RequestBody Engineer engineerDetails) throws ResourceNotFoundException {
        Engineer engineer = engineerRepository.findById(engineerId)
                .orElseThrow(() -> new ResourceNotFoundException("Engineer not found on :: "+ engineerId));
        engineer.setId(engineerDetails.getId());
        engineer.setFirstName(engineerDetails.getFirstName());
        engineer.setLastName(engineerDetails.getLastName());
        engineer.setSid(engineerDetails.getSid());
        engineer.setEmail(engineerDetails.getEmail());
        engineer.setTeamLead(engineerDetails.getTeamLead());
        engineer.setManager(engineerDetails.getManager());
        engineer.setShift(engineerDetails.getShift());
        engineer.setStartTime(engineerDetails.getStartTime());
        engineer.setEndTime(engineerDetails.getEndTime());

//      engineer.setUpdatedAt(new Date());
        final Engineer updatedEngineer = engineerRepository.save(engineer);
        return ResponseEntity.ok(updatedEngineer);
    }

    @DeleteMapping("/engineers/{id}")
    public Map<String, Boolean> deleteEngineer(
            @PathVariable(value = "id") Long engineerId) throws Exception {
        Engineer engineer = engineerRepository.findById(engineerId)
                .orElseThrow(() -> new ResourceNotFoundException("Engineer not found on :: "+ engineerId));

        engineerRepository.delete(engineer);
        Map<String, Boolean> response = new HashMap<>();
        response.put("deleted", Boolean.TRUE);
        return response;
    }
}

使用邮递员，我试图“发布”一个新的“测试”员工，但由于上述限制而失败。任何帮助将不胜感激...

完整的堆栈跟踪：

2019-01-11 15:26:50.605  WARN 15276 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1062, SQLState: 23000
2019-01-11 15:26:50.606 ERROR 15276 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Duplicate entry '68' for key 'PRIMARY'
2019-01-11 15:26:50.606 ERROR 15276 --- [nio-8080-exec-6] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]
2019-01-11 15:26:50.657  WARN 15276 --- [nio-8080-exec-6] .m.m.a.ExceptionHandlerExceptionResolver : Resolved exception caused by Handler execution: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

标签： javamysqlapimariadbpostman

Answer 1

我有同样的问题，原因是这个 o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '68' for key 'PRIMARY'。

Hibernate 正在尝试为新Engineer生成一个 Id ，但是当它尝试遵循它指定的生成策略时， @GeneratedValue(strategy = GenerationType.AUTO)它会得到一个已经存在的 Id。发生这种情况很可能是因为您手动向Engineer表添加了新行，该表与 Hibernate 生成的下一个 Id 值一起折叠。

此外，如果您多次尝试插入新工程师，您最终会成功，因为 Hibernate 会为下一个插入请求增加 Id，直到找到一个不存在的 Id。

要解决此问题，您可以将 Id 生成策略更改为@GeneratedValue(strategy = GenerationType.IDENTITY)或继续尝试插入，直到 Hibernate 获得未使用的 Id 值（如果您想保持相同的生成策略）