Why does my update throw a hibernate ConstraintViolationException?

Question

CREATE TABLE GUSERPRO
(
  USER_NAME      VARCHAR2(20 BYTE),
  SYSTEM         VARCHAR2(8 BYTE),
  PROCESS        VARCHAR2(8 BYTE),
  LOGIN_TIME     NUMBER,
  LOGIN_DATE     DATE,
  LOGOUT_TIME    NUMBER,
  LOGOUT_DATE    DATE,
  TOTAL_ELAPSED  NUMBER,
  TOTAL_LOGINS   NUMBER
)
TABLESPACE ERIP_D
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

the exception stack references this constraint:

CREATE UNIQUE INDEX GUSERPRO_N1 ON GUSERPRO
(USER_NAME, SYSTEM, PROCESS)
LOGGING
TABLESPACE ERIP_D
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

while trying to update a record into the Oracle database I am getting this error, but not always , some scenarios I get this error. If I create a new record and then update it is working fine. If I try to update the existing record it is throwing this error. Please help me to get rid of this.

org.springframework.dao.DataIntegrityViolationException: 
   Could not execute JDBC batch update; 
   SQL [update PDOXDATA_GUSERPRO set PROCESS=?, SYSTEM=? where USER_NAME=?]; 
   constraint [PDOXDATA_GUSERPRO_N1]; 

nested exception is org.hibernate.exception.ConstraintViolationException: 
    Could not execute JDBC batch update
    << rest of error stack >>

Here is the relevant class:

/**
 * 
 */
package com.lv.rs.accessautomation.common.vo;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

/**
 * @author tp50148
 *
 */
@Entity
@Table(name="PDOXDATA_GUSERPRO"/*,uniqueConstraints = {@UniqueConstraint(columnNames = "userName")}*/)
public class EripUserProVO implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "USER_NAME")
    private String userName;

    @Column(name = "SYSTEM")
    private String system;

    @Column(name = "PROCESS")
    private String process;

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="USER_NAME")
    private List<EripGtSignatVO> eripGtSignatVO = new ArrayList<EripGtSignatVO>();

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="USER_NAME")
    private List<EripGUsersVO> eripGUsersVO = new ArrayList<EripGUsersVO>();

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="USERNAME")
    private List<EripUserLocationVO> eripUserLocationVO = new ArrayList<EripUserLocationVO>();

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="USER_USER")
    private List<Eripcpa95VO> eripcpa95VO = new ArrayList<Eripcpa95VO>();

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="USERNAME")
    private List<EriporaSacUserVO> eriporaSacUserVO = new ArrayList<EriporaSacUserVO>();

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name="SACUSER_USERNAME")
    private List<EriporaSacAccessVO> eriporaSacAccessVO = new ArrayList<EriporaSacAccessVO>();

    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getSystem() {
        return system;
    }
    public void setSystem(String system) {
        this.system = system;
    }
    public String getProcess() {
        return process;
    }
    public void setProcess(String process) {
        this.process = process;
    }
    public List<EripGtSignatVO> getEripGtSignatVO() {
        return eripGtSignatVO;
    }
    public void setEripGtSignatVO(List<EripGtSignatVO> eripGtSignatVO) {
        this.eripGtSignatVO = eripGtSignatVO;
    }
    public List<EripGUsersVO> getEripGUsersVO() {
        return eripGUsersVO;
    }
    public void setEripGUsersVO(List<EripGUsersVO> eripGUsersVO) {
        this.eripGUsersVO = eripGUsersVO;
    }
    public List<EripUserLocationVO> getEripUserLocationVO() {
        return eripUserLocationVO;
    }
    public void setEripUserLocationVO(List<EripUserLocationVO> eripUserLocationVO) {
        this.eripUserLocationVO = eripUserLocationVO;
    }
    public List<Eripcpa95VO> getEripcpa95VO() {
        return eripcpa95VO;
    }
    public void setEripcpa95VO(List<Eripcpa95VO> eripcpa95vo) {
        eripcpa95VO = eripcpa95vo;
    }
    public List<EriporaSacUserVO> getEriporaSacUserVO() {
        return eriporaSacUserVO;
    }
    public void setEriporaSacUserVO(List<EriporaSacUserVO> eriporaSacUserVO) {
        this.eriporaSacUserVO = eriporaSacUserVO;
    }
    public List<EriporaSacAccessVO> getEriporaSacAccessVO() {
        return eriporaSacAccessVO;
    }
    public void setEriporaSacAccessVO(List<EriporaSacAccessVO> eriporaSacAccessVO) {
        this.eriporaSacAccessVO = eriporaSacAccessVO;
    }



}

Answer 1

"this error [...] occurs for particular records alone repeatedly."

You can identify specific records which reproducibly exhibit this behaviour. So this should be very easy for you to debug. But let's unpick it for you.

According to the error stack your update statement is this:

update PDOXDATA_GUSERPRO 
set PROCESS=?, SYSTEM=? 
where USER_NAME=?

Your constraint, PDOXDATA_GUSERPRO_N1, is a compound unique key on (USER_NAME, SYSTEM, PROCESS). Those are the three columns involved in the update statement. This is not a coincidence.

Unique compound key enforce unique permutations. So we can have multiple rows for the same PROCESS or the same SYSTEM or the same USER_NAME. We just cannot have more than one row for any permutation of USER_NAME, SYSTEM, PROCESS.

So, here is a prediction: the update fails when you pass in a USER_NAME which has more than one record. In that case you are trying to set the same PROCESS and SYSTEM for multiple instances of the same USER_NAME and that's why you get a unique constraint violation.

---

Now that we can see your code it is clear that @Hurds is correct and your Hibernate configuration does not match your physical data model. You have defined a single column ID rather than all three columns:

    @Id
    @Column(name = "USER_NAME")
    private String userName;

    @Column(name = "SYSTEM")
    private String system;

So you have a choice:

• If your database is wrong you need to drop and recreate the constraint. Clearly this will require housekeeping the existing data to remove records with multiple instances of the key.

• If you need to maintain the composite key there are a couple of ways to configure it in Hibernate. Check this other StackOverflow thread.