Dear Java Guru,
I need your advice on how to ignore / merge duplicate records when persisting OneToMany unidirectional entity relationship tables that spanned over multiple entity manager factories. Below is the code snippet that I am referring to:
@Entity
@Table(name="EMPLOYEE", catalog="EmployeeDB", schema="")
public class Employee implements Serializable {
@OneToMany(cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, fetch=FetchType.EAGER)
@JoinTable(name="EMP_PHONE", catalog="EmployeeDB", schema="",
joinColumns={@JoinColumn(name="EMP_ID", referencedColumnName="EMP_ID")},
inverseJoinColumns={@JoinColumn(name="PHONE_ID", referencedColumnName="PHONE_ID")})
private Collection<Phone> phones;
public Employee() {}
public Employee(int id) {
this.id = id;
phones = new ArrayList<Phone>();
}
@Id
@Column(name="EMP_ID")
private int id;
…………
@Column(name="NAME")
private String name;
………
public void addPhone(Phone phone) {
if (!getPhones().contains(phone)) {
getPhones().add(phone);
}
}
public Collection<Phone> getPhones() {
return phones;
}
}
@Entity
@Table(name="PHONE", catalog="EmployeeDB", schema="", uniqueConstraints=@UniqueConstraint(columnNames={"PHONE_NUMBER","PHONE_TYPE"}))
public class Phone implements Serializable {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="PHONE_ID")
private int id;
………..
@Column(name="PHONE_NUMBER")
private String number;
……..
@Column(name="PHONE_TYPE")
private String type;
……..
}
public class EmployeeService {
protected EntityManager em;
public EmployeeService(EntityManager em) {
this.em = em;
}
public Employee createEmployee(int id, String name, long salary) {
Employee emp = em.find(Employee.class, id);
if (emp == null) {
emp = new Employee(id);
emp.setName(name);
emp.setSalary(salary);
em.persist(emp);
}
return emp;
}
public class EmployeeTest {
public static void main(String[] args) {
EntityManagerFactory emf1 =
Persistence.createEntityManagerFactory("EmployeeService");
EntityManager em1 = emf1.createEntityManager();
EmployeeService employeeService1 = new EmployeeService(em1);
em1.getTransaction().begin();
Employee emp1 = employeeService1.createEmployee(158, "John Doe");
Phone homePhone1 = new Phone();
homePhone1.setNumber("11111111");
homePhone1.setType("Home phone");
emp1.addPhone(homePhone1);
em1.getTransaction().commit();
System.out.println("Persisted " + emp1);
em1.close();
emf1.close();
EntityManagerFactory emf2 =
Persistence.createEntityManagerFactory("EmployeeService");
EntityManager em2 = emf2.createEntityManager();
EmployeeService employeeService2 = new EmployeeService(em2);
em2.getTransaction().begin();
Employee emp2 = employeeService2.createEmployee(158, "John Doe");
Phone workPhone = new Phone();
workPhone.setNumber("22222222");
workPhone.setType("Work phone");
emp2.addPhone(workPhone2);
em2.getTransaction().commit(); line 43
System.out.println("Persisted " + emp2);
// close the EM and EMF when done
em2.close();
emf2.close();
Desire output
----------------
John Doe – homePhone – 11111111
John Doe – workPhone – 22222222
However, below is the exception error encountered instead:
[EL Info]: 2011-09-20 01:31:46.255--ServerSession(19106770)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20110202-r8913
[EL Info]: 2011-09-20 01:31:47.974--ServerSession(19106770)--file:/C:/Users/Jack/ProJPA2TutorialOneToMany/build/classes/_EmployeeService login successful
Persisted Employee id: 158 name: John Doe salary: 45000
[EL Info]: 2011-09-20 01:31:49.411--ServerSession(19106770)--file:/C:/Users/Jack/ProJPA2TutorialOneToMany/build/classes/_EmployeeService logout successful
[EL Info]: 2011-09-20 01:31:49.443--ServerSession(27291192)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20110202-r8913
[EL Info]: 2011-09-20 01:31:49.708--ServerSession(27291192)--file:/C:/Users/Jack/ProJPA2TutorialOneToMany/src/_EmployeeService login successful
*[EL Warning]: 2011-09-20 01:31:49.755--ServerSession(27291192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'employee' already exists
Error Code: 1050
Call: CREATE TABLE EmployeeDB.EMPLOYEE (EMP_ID INTEGER NOT NULL, NAME VARCHAR(255), SALARY BIGINT, PRIMARY KEY (EMP_ID))
Query: DataModifyQuery(sql="CREATE TABLE EmployeeDB.EMPLOYEE (EMP_ID INTEGER NOT NULL, NAME VARCHAR(255), SALARY BIGINT, PRIMARY KEY (EMP_ID))")
*[EL Warning]: 2011-09-20 01:31:49.771--ServerSession(27291192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'phone' already exists
Error Code: 1050
Call: CREATE TABLE EmployeeDB.PHONE (PHONE_ID INTEGER AUTO_INCREMENT NOT NULL, PHONE_NUMBER VARCHAR(255), PHONE_TYPE VARCHAR(255), PRIMARY KEY (PHONE_ID))
Query: DataModifyQuery(sql="CREATE TABLE EmployeeDB.PHONE (PHONE_ID INTEGER AUTO_INCREMENT NOT NULL, PHONE_NUMBER VARCHAR(255), PHONE_TYPE VARCHAR(255), PRIMARY KEY (PHONE_ID))")
*[EL Warning]: 2011-09-20 01:31:49.771--ServerSession(27291192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'emp_phone' already exists
Error Code: 1050
Call: CREATE TABLE EmployeeDB.EMP_PHONE (EMP_ID INTEGER NOT NULL, PHONE_ID INTEGER NOT NULL, PRIMARY KEY (EMP_ID, PHONE_ID))
Query: DataModifyQuery(sql="CREATE TABLE EmployeeDB.EMP_PHONE (EMP_ID INTEGER NOT NULL, PHONE_ID INTEGER NOT NULL, PRIMARY KEY (EMP_ID, PHONE_ID))")
*[EL Warning]: 2011-09-20 01:31:49.786--ServerSession(27291192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'UNQ_PHONE_0'
Error Code: 1061
Call: ALTER TABLE EmployeeDB.PHONE ADD CONSTRAINT UNQ_PHONE_0 UNIQUE (PHONE_NUMBER, PHONE_TYPE)
Query: DataModifyQuery(sql="ALTER TABLE EmployeeDB.PHONE ADD CONSTRAINT UNQ_PHONE_0 UNIQUE (PHONE_NUMBER, PHONE_TYPE)")
*[EL Warning]: 2011-09-20 01:31:49.896--ServerSession(27291192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: java.sql.SQLException: Can't create table 'Employeedb.#sql-63c_6' (errno: 121)
Error Code: 1005
Call: ALTER TABLE EmployeeDB.EMP_PHONE ADD CONSTRAINT FK_EMP_PHONE_PHONE_ID FOREIGN KEY (PHONE_ID) REFERENCES EmployeeDB.PHONE (PHONE_ID)
Query: DataModifyQuery(sql="ALTER TABLE EmployeeDB.EMP_PHONE ADD CONSTRAINT FK_EMP_PHONE_PHONE_ID FOREIGN KEY (PHONE_ID) REFERENCES EmployeeDB.PHONE (PHONE_ID)")
*[EL Warning]: 2011-09-20 01:31:49.911--UnitOfWork(11317592)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '11111111-Home' for key 'UNQ_PHONE_0'
Error Code: 1062
Call: INSERT INTO EmployeeDB.PHONE (PHONE_NUMBER, PHONE_TYPE) VALUES (?, ?)
* bind => [2 parameters bound]*
Query: InsertObjectQuery(Phone id: 0, no: 11111111, type: Home)
*Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '11111111-Home' for key 'UNQ_PHONE_0'
Error Code: 1062
Call: INSERT INTO EmployeeDB.PHONE (PHONE_NUMBER, PHONE_TYPE) VALUES (?, ?)
* bind => [2 parameters bound]*
Query: InsertObjectQuery(Phone id: 0, no: 11111111, type: Home)
* at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)*
* at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)*
* at chapter4ex23.EmployeeTest.main(EmployeeTest.java:43)*
*Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException*
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '11111111-Home' for key 'UNQ_PHONE_0'
Error Code: 1062
Call: INSERT INTO EmployeeDB.PHONE (PHONE_NUMBER, PHONE_TYPE) VALUES (?, ?)
* bind => [2 parameters bound]*
Query: InsertObjectQuery(Phone id: 0, no: 11111111, type: Home)
* at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)*
* at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:798)*
* at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:864)*
* at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)*
* at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)*
* at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)*
* at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)*
* at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)*
* at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)*
* at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342)*
* at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)*
* at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)*
* at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:469)*
* at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)*
* at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)*
* at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:291)*
* at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)*
* at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:808)*
* at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:711)*
* at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)*
* at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)*
* at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2842)*
* at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1521)*
* at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1503)*
* at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)*
* at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)*
* at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:191)*
* at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:136)*
* at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3766)*
* at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1404)*
* at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:616)*
* at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1511)*
* at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:264)*
* at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1130)*
* at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)*
* ... 2 more*
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '11111111-Home' for key 'UNQ_PHONE_0'
* at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)*
* at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)*
* at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)*
* at java.lang.reflect.Constructor.newInstance(Constructor.java:513)*
* 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:3491)*
* at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)*
* at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)*
* at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)*
* at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)*
* at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)*
* at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)*
* at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)*
* at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)*
* at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:789)*
* ... 35 more*
Yet this exception would not have occurred if all the persistent steps were carried out under the same Entity Manager Factory. However, the data that I am importing will have duplicates that will take place days, if not weeks later. The merging of Phone tables is working fine in general.
This issue appears to be a transactional Management one as opposed to a programming.
Thanks in advance,
Jack