RubioRic RubioRic - 6 months ago 75
SQL Question

Hibernate exception: Duplicate entry for key 'PRIMARY'

I've these (simplified) tables in my database

enter image description here

I've mapped the tables with these (simplified) two classes

COMPANY

@Entity
public class Company {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private String name;

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
@JoinTable(name="company_employee",
joinColumns={@JoinColumn(name="company_id")},
inverseJoinColumns={@JoinColumn(name="employee_id")})
@OrderBy("name ASC")
private SortedSet<Employee> employees = new TreeSet<Employee>();

// contructors + getters + setters
}


EMPLOYEE

@Entity
public class Employee implements Comparable<Employee> {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private String name;

@OneToOne(fetch = FetchType.LAZY, optional = true)
@JoinTable(name="company_employee",
joinColumns={@JoinColumn(name="employee_id")},
inverseJoinColumns={@JoinColumn(name="company_id")}
)
private Company company;

@Override
public int compareTo(Employee anotherEmployee) {
return this.name.compareTo(anotherEmployee.name);
}

// contructors + getters + setters + equals + hashcode
}


I'm using a
SortedSet/TreeSet
in employees defined in
Company
to obtain the employees sorted by name.

The problem arises when I persist the objects. I've read that you must establish the relation in both sides. I set the employee in company and the company in the employee before persisting the company. When I try to execute the persist, I'm obtaining the following exception

Exception in thread "main" javax.persistence.RollbackException: Error while committing the transaction
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:86)
at es.rubioric.hibernate.MainTest.main(MainTest.java:43)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:67)
... 1 more
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1314)
at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:50)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:447)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:333)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:335)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1224)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:464)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2890)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2266)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:146)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:230)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)
... 1 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '32-80' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2643)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
... 18 more


This is a simplified code that generates that exception. [EDITED commit location after comment by @NicolasFilotto]

public static void main(String[] args) throws ParseException {

EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestDB");

EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();

try {
Company c = new Company("Nike");

Employee e1 = new Employee("Anthony");
e1.setCompany(c); // line 26
Employee e2 = new Employee("Zenobia");
e2.setCompany(c); // line 28
Employee e3 = new Employee("Chuck");
e3.setCompany(c); // line 30
Employee e4 = new Employee("Bernard");
e4.setCompany(c); // line 32

c.getEmployees().add(e1);
c.getEmployees().add(e2);
c.getEmployees().add(e3);
c.getEmployees().add(e4);

em.persist(c);

tx.commit();

} finally {
em.close();
}
}


These are the SQL sentences internally executed by Hibernate.

Hibernate: insert into Company (name) values (?)
Hibernate: insert into Employee (name) values (?)
Hibernate: insert into company_employee (company_id, employee_id) values (?, ?)
Hibernate: insert into Employee (name) values (?)
Hibernate: insert into company_employee (company_id, employee_id) values (?, ?)
Hibernate: insert into Employee (name) values (?)
Hibernate: insert into company_employee (company_id, employee_id) values (?, ?)
Hibernate: insert into Employee (name) values (?)
Hibernate: insert into company_employee (company_id, employee_id) values (?, ?)
Hibernate: insert into company_employee (company_id, employee_id) values (?, ?)


The same code works perfectly if I comment lines 26, 28, 30 and 32 (marked above). But I want to know why is that exception being generated. Why the duplicated key?

Thanks in advance.

Answer

The duplicates are caused by your company-employee relationship being duplicated in both entities. Each has been setup as a unidirectional 1:M (one side is using a 1:1 for some reason), and both are using the same join table, causing duplicates when JPA goes to insert entries for both sides.

The solution is to mark one side as 'mappedby' the other.

@OneToMany( mappedBy="company", fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
@OrderBy("name ASC")
private SortedSet<Employee> employees = new TreeSet<Employee>();