Spring Boot- Duplicate entry for key ‘PRIMARY’

I am new to Spring Boot. I am trying to use the save() functionality via the JPA library using Postman for the first time. My database is a legacy Mysql database. Generically speaking, this table contains data of baseball players who have been drafted into a fantasy baseball league. The primary key of my table is ‘play_id’, and I also track the player’s ‘mlb_id’ (Major League Baseball’s unique identifier) in the same table.

Here is my code:

Table setup in Mysql:

 CREATE TABLE `mlb_rosters` (
  `play_id` int(10) NOT NULL,
  `mlb_id` int(10) NOT NULL,
  `name_first` varbinary(255) NOT NULL,
  `name_last` varbinary(255) NOT NULL,
  `bats` varchar(1) NOT NULL,
  `throws` varchar(1) NOT NULL,
  `birthday` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `mlb_rosters`
  ADD PRIMARY KEY (`play_id`),
  ADD UNIQUE KEY `mlb_id` (`mlb_id`),
  ADD UNIQUE KEY `mlb_id_2` (`mlb_id`);

ALTER TABLE `mlb_rosters`
  MODIFY `play_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6730; 

I also ran insert statements for approximately ~1500 players, so this is not a blank table.

My object in Springboot:

package com.example.demo.entities;

import javax.persistence.*;

@Entity
@Table(name="mlb_rosters")
public class IbcMlbPlayer {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="play_id", columnDefinition = "int(10)")
    private Integer playId;

    @Column(name="mlb_id")
    private Integer mlbId;

    @Column(name="name_first", columnDefinition = "varbinary(255)")
    private String nameFirst;

    @Column(name="name_last", columnDefinition = "varbinary(255)")
    private String nameLast;

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

    @Column(name="throws")
    private String thrws;

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

    public IbcMlbPlayer(){

    }

    public Integer getPlayId() {
        return playId;
    }

    public void setPlayId(Integer playId) {
        this.playId = playId;
    }

    public Integer getMlbId() {
        return mlbId;
    }

    public void setMlbId(Integer mlbId) {
        this.mlbId = mlbId;
    }

    public String getNameFirst() {
        return nameFirst;
    }

    public void setNameFirst(String nameFirst) {
        this.nameFirst = nameFirst;
    }

    public String getNameLast() {
        return nameLast;
    }

    public void setNameLast(String nameLast) {
        this.nameLast = nameLast;
    }

    public String getBats() {
        return bats;
    }

    public void setBats(String bats) {
        this.bats = bats;
    }

    public String getThrws() {
        return thrws;
    }

    public void setThrws(String thrws) {
        this.thrws = thrws;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
}

The relevant path of my controller:

@PostMapping(value = "/saveIbcMlbPlayer")
    public IbcMlbPlayer saveIbcMlbPlayer(@RequestBody IbcMlbPlayer ibcMlbPlayer){
        return ibcMlbPlayerDao.save(ibcMlbPlayer);
    }

My Dao:

package com.example.demo.dao;

import com.example.demo.entities.IbcMlbPlayer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface IbcMlbPlayerDao extends JpaRepository<IbcMlbPlayer, Integer> {
}

When I attempt to do a Post request to the save path and pass in the JSON object of the player who I’m attempting to create, I get the following error:

Duplicate entry ’25’ for key ‘PRIMARY’

In this case, I’ve tried this 25 times, so Postman/Spring Boot keep incrementing the ‘play_id’ field by 1 (this number goes up in the error message by one each time I test).

I understand the error, for whatever reason, Spring Boot isn’t getting the max value of the ‘play_id’ field, incrementing it by one, and then attempting to do the insert. I would have expected ‘play_id’ to be 6730, which I believe is the table’s max play_id plus one. Does anyone know how to fix this? Any help would be really appreciated!

Answer

AUTO shouldn’t be used as GenerationType you must use IDENTITY

@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="play_id", columnDefinition = "int(10)")
private Integer playId;

Leave a Reply

Your email address will not be published. Required fields are marked *