com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException Duplicate entry

Question

I work with a Spring boot project with MySQL database and the entities are provided,

The IP address entity,

@Entity
public class IpAddress {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "IP_ADDR_ID")
    private Long id;

    @Column(name = "IP_ADDRESS")
    @NotEmpty
    private String address;


    @JsonIgnore
    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "ipAddresses")
    private List<HttpInfoMessage> httpInfoMessages = new ArrayList<>();

    public IpAddress() {
    }

    public IpAddress(String address) {
        this.address = address;
    }

    public IpAddress(String address, List<HttpInfoMessage> httpInfoMessages) {
        this.address = address;
        this.httpInfoMessages = httpInfoMessages;
    }

    public Long getId() {
        return id;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<HttpInfoMessage> getHttpInfoMessages() {
        return httpInfoMessages;
    }

    public void setHttpInfoMessages(List<HttpInfoMessage> httpInfoMessages) {
        this.httpInfoMessages = httpInfoMessages;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof IpAddress)) return false;

        IpAddress ipAddress = (IpAddress) o;

        if (!getId().equals(ipAddress.getId())) return false;
        return getAddress().equals(ipAddress.getAddress());
    }

    @Override
    public int hashCode() {
        int result = getId().hashCode();
        result = 31 * result + getAddress().hashCode();
        return result;
    }

    @Override
    public String toString() {
        return "IpAddress{" +
                "id=" + id +
                ", address='" + address + '\'' +
                '}';
    }
}

The Http message entity,

@Entity
public class HttpInfoMessage {

    @Id
//    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "STATUS_ID")
    private Long statusId;

    @Column(name = "STATUS")
    @NotEmpty
    private String status;

    public HttpInfoMessage() {

    }

    public HttpInfoMessage(String status) {
        this.status = status;
    }

    public HttpInfoMessage(Long statusId, String status) {
        this.statusId = statusId;
        this.status = status;
    }

    public HttpInfoMessage(Long statusId, String status, List<IpAddress> ipAddresses) {
        this.statusId = statusId;
        this.status = status;
        this.ipAddresses = ipAddresses;
    }

    public Long getStatusId() {
        return statusId;
    }

    public void setStatusId(Long statusId) {
        this.statusId = statusId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    //    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "httpInfoMessages")
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "IP_ADDR_STATUS",
            joinColumns = @JoinColumn(name = "STATUS_ID", referencedColumnName = "STATUS_ID"),
            inverseJoinColumns = @JoinColumn(name = "IP_ADDRESS_ID", referencedColumnName = "IP_ADDR_ID"))
    private List<IpAddress> ipAddresses = new ArrayList<>();

    public List<IpAddress> getIpAddresses() {
        return ipAddresses;
    }

    public void setIpAddresses(List<IpAddress> ipAddresses) {
        this.ipAddresses = ipAddresses;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof HttpInfoMessage)) return false;

        HttpInfoMessage httpInfoMessage1 = (HttpInfoMessage) o;

        if (!getStatusId().equals(httpInfoMessage1.getStatusId())) return false;
        return getStatus().equals(httpInfoMessage1.getStatus());
    }

    @Override
    public int hashCode() {
        int result = getStatusId().hashCode();
        result = 31 * result + getStatus().hashCode();
        return result;
    }

    @Override
    public String toString() {
        return "Status{" +
                "statusId=" + statusId +
                ", status='" + status + '\'' +
                '}';
    }
}

I read the server log entries from a text file and perform some manipulation on them and eventually, I get the List<IpAddress>.

After I get the List<IpAddress>, I printed them just to show,

The IP list size is = 5

IP address = IpAddress{id=null, address='177.132.239.67'}
Status{statusId=403, status='403_Forbidden'}
Status{statusId=404, status='404_Not Found'}
Status{statusId=405, status='405_Method Not Allowed'}
Status{statusId=406, status='406_Not Acceptable'}

IP address = IpAddress{id=null, address='178.133.239.67'}
Status{statusId=403, status='403_Forbidden'}
Status{statusId=404, status='404_Not Found'}
Status{statusId=405, status='405_Method Not Allowed'}
Status{statusId=406, status='406_Not Acceptable'}

IP address = IpAddress{id=null, address='175.130.239.67'}
Status{statusId=403, status='403_Forbidden'}
Status{statusId=404, status='404_Not Found'}
Status{statusId=405, status='405_Method Not Allowed'}
Status{statusId=406, status='406_Not Acceptable'}

IP address = IpAddress{id=null, address='176.131.239.67'}
Status{statusId=403, status='403_Forbidden'}
Status{statusId=404, status='404_Not Found'}
Status{statusId=405, status='405_Method Not Allowed'}
Status{statusId=406, status='406_Not Acceptable'}

IP address = IpAddress{id=null, address='174.129.239.67'}
Status{statusId=403, status='403_Forbidden'}
Status{statusId=404, status='404_Not Found'}
Status{statusId=405, status='405_Method Not Allowed'}
Status{statusId=406, status='406_Not Acceptable'}

I then tried to save all the entries by iterating through them,

ipAddresses.forEach(
                ipAddress -> {
                    ipAddressService.save(ipAddress);
                }
        );

I get the error at the time of saving the entries,

java.lang.IllegalStateException: Failed to execute CommandLineRunner

Caused by: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '403' for key 'PRIMARY'

I can provide more info if required

Marco Kunis · Accepted Answer · 2017-10-11 13:45:05Z

1

Each of the IpAddress object has a list of HttpInfoMessage objects which uses the statusId field as ID in the database. An ID has to be unique.

But you use the CascadeType.ALL for those collections, which means HttpInfoMessage objects will be saved too.

So when you try to save the Status{statusId=403, status='403_Forbidden'} a second time you get the exception, because of the id already exists. That is simple database stuff and you have to fix this. How, depends on what you want to do.

answered Oct 11, 2017 at 13:45

Marco Kunis

1105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arefe Over a year ago

I guess assigning an IpAddress id column in the HttpInfoMessage table will solve the issue though I wonder how to do it. In that time, It will save like Status{statusId=403, status='403_Forbidden', id=1}, Status{statusId=403, status='403_Forbidden', id=2} etc and will be unique. I will open know any other better solution as well.

Arefe Over a year ago

If I use the CascadeType.MERGE or CascadeType.DETACH, it will populate only the ip_address table. I would prefer to populate all the tables if possible. Please advice

Marco Kunis Over a year ago

What you suggest is a OneToMany relation. Look here en.wikibooks.org/wiki/Java_Persistence/OneToMany, if you want to know how to do it. For me the values are static ones, so I would use an enum or database table with enumerated values

Benjamin Schüller · Accepted Answer · 2017-10-11 13:30:36Z

1

In your httpService-Entity the statusId field is the primary key. Your testdata has the values 403-406 set in statusId in every IpAddress-Block. If you remove the values from statusId and set them to null it should work.

answered Oct 11, 2017 at 13:30

Benjamin Schüller

2,2191 gold badge21 silver badges30 bronze badges

2 Comments

Arefe Over a year ago

How do I remove the values from statusId?

Benjamin Schüller Over a year ago

Do you create the List<IpAddress> manually? Then you also set the value on Status.statusId manually. Otherwise you have to iterate over all Status-elements and call sth. like status.setStatusId=null.

dgebert · Accepted Answer · 2017-10-11 13:44:14Z

1

You are trying to put non-unique http-status codes as Id's statusId which is unique.

As you have your entities provided and you can't change them you have to make shure you are correctly inserting values as a status. For me with entity like that you can put a technical(generated) id to statusId and a name of your status('403_Forbidden' or simply '403') to status column.

answered Oct 11, 2017 at 13:44

dgebert

1,5341 gold badge19 silver badges31 bronze badges

Collectives™ on Stack Overflow

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException Duplicate entry

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related