SQL [n/a]; constraint [PRIMARY];
I created a CRUD RESTful API application using Java/Spring Boot. I have a MariaDB with a list of employees and relevant data inside. I am using MySQL Workbench as my SQL Client.
There are no errors when running the Spring Boot application. No errors in my database. No errors when I get the API of all Engineers in my database.
My "GET" requests work fine; however, "POST" does not. I am unable to create a new employee without having to directly insert into the database with MySQL WB.
I want to be able to create a new employee using APIs via Postman.
As you can see there is a ConstraintViolationException being thrown. The contraint is my Primary Key, which is "id." I've set that to auto-increment in MySQL WB.
Here is the code for my Engineer class:
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;
}
}
This is the code for my EngineerController class:
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;
}
}
Using Postman, I am trying to "POST" a new "test" employee but fails due to the aforementioned constraint. Any help will be appreciated...
Full stack trace:
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
I had this same issue and the reason is this
o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '68' for key 'PRIMARY'.
Hibernate is trying to generate an Id for the new Engineer, but when it tries to follow the generation strategy specified by @GeneratedValue(strategy = GenerationType.AUTO) It gets an Id that already exists.
Most likely this happens because you manually added new rows to the Engineer table that collapses with the next generated Id values by Hibernate.
Also if you keep trying to insert a new Engineer multiple times you will end up succeeding since Hibernate increments the Id for the next insertion request until it finds an Id that doesn't already exist.
To solve the issue you can change the Id generation strategy to @GeneratedValue(strategy = GenerationType.IDENTITY) or keep trying to insert until Hibernate get to a non used Id value (If you want to keep the same generation strategy)