Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes Object Relational Mapping and the fly likes Still getting duplicate in JPA ManyToMany Entity Relationship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Still getting duplicate in JPA ManyToMany Entity Relationship" Watch "Still getting duplicate in JPA ManyToMany Entity Relationship" New topic
Author

Still getting duplicate in JPA ManyToMany Entity Relationship

Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi All,

You are likely to have seen similar threats I posted a while back but did not get a clear resolution so here it goes again, except on a higher level hoping to get some direction on which path to take.



The was added in the hope to make the record in SCHOOL unique but it did not work. In other word, it was not added intentionally to make it a bi-directional relationship since the data are added on the County side. Hence, the has not been used at all so far. Likewise, Using Set to hold records for County and School was another attempt to keep the SCHOOL normalized without success so far.

I am running JDK1.6.0_17, GF 2.1 on XP.

It would be much appreciated for some guidance in an unfamiliar territory.

Thanks in advance,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
So you are loading a bunch of (new?) Countys from a file, which each have a set of possibly shared Schools? and you are seeing the same school inserted twice?

What error do you get? A primary key constraint error? What is the SQL generated?

My guess your issue is you are using persist() not merge(). persist() is for new objects, but your Schools may not be new, so you must use merge() not persist().

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

Thanks for your response to this threat.

Using merge() instead of persist has doubled the number of duplications. i.e. 4 times as opposed to twice. Is it possible that JPA is focusing on keeping the autogenerated primary key (ID) column unique below while the duplication is occurring in the next non-ID (SCHOOL_NAME):



In other word, is JPA capable of keeping/checking the data of each row (e.g. SCHOOL_NAME) normalized even though the primary key has been unique all along?

Any idea on how to configure Glassfish to log/display every SQL INSERT statement to understand when the duplication is taking place?

Thanks a lot,

Jack
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

Below are a little more findings after having worked out how to configure detail logging in Glassfish 2.1 MySQL database:

( i ) All SCHOOL rows/records (14 unique) have been persisted when they belonged to a single county entity. However, the order of these schools being SQL inserted into SCHOOL is in a completely different order to when they were stored in Set<School>. Each school record would include a small comment within a parenthesis. E.g. Local Secondary School (5 miles away). Not sure whether the parenthesis had anything to do with the order of final database inserts.
( ii ) Only the first set of SCHOOL rows/records (15 unique) belonged to the first (one of two) county record were duplicated. All SCHOOL rows/records associated to the second county were unique. In other word, they had been SQL inserted only once.

In short, it appears that the order of data (SCHOOL) been SQL inserted had not taken affect into the database until later on. As a result, I assumed that some records have already been SQL inserted when in fact they have not been. In addition, would the CascadeType, Container Transaction or both have anything to do with this?

Thought I throw in some more ideas to look into.

Many thanks again,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Your Id on School is a generated Id, so any School you create as a new instance will be inserted. To use the existing school, you need it to have the same Id, i.e. load it from the database.

So, instead of doing new School(), you need to call findSchool(name), and add that instance to the collection.
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

This is getting more interesting. Hopefully it will lead to a solution soon. Below is a few additional lines added to AddCounty() when adding new school to incorporate your suggestion:


However, this change was met with the following exception:

20/01/2011 2:34:45 AM com.sun.enterprise.appclient.MainWithModuleSupport <init>
WARNING: ACC003: Application threw an exception.
javax.ejb.EJBException: nested exception is: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: null; nested exception is:
java.lang.IllegalStateException: Query argument schoolName not found in the list of parameters provided during query execution.
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: java.rmi.RemoteException: null; nested exception is: java.lang.IllegalStateException: Query argument schoolName not found in the list of parameters provided during query execution.
at com.sun.corba.ee.impl.javax.rmi.CORBA.Util.mapSystemException(Util.java:243)
at com.sun.corba.ee.impl.presentation.rmi.StubInvocationHandlerImpl.privateInvoke(StubInvocationHandlerImpl.java:205)
at com.sun.corba.ee.impl.presentation.rmi.StubInvocationHandlerImpl.invoke(StubInvocationHandlerImpl.java:152)
at com.sun.corba.ee.impl.presentation.rmi.bcel.BCELStubBase.invoke(BCELStubBase.java:225)
at ejb.__SchoolRemote_Remote_DynamicStub.findSchool(ejb/__SchoolRemote_Remote_DynamicStub.java)
at ejb._SchoolRemote_Wrapper.findSchool(ejb/_SchoolRemote_Wrapper.java)
at client.AddCounty(AddCounty.java:188)


I can do with another pair of eye to see whether the parameter (school_name) passed to query in findSchool(school_name) is correct. This is an unfamiliar territory.
Thanks,
Jack
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,
Further correction to the query statement in SchoolBean below has overcome the exception but the duplication still remains:


Thanks,
Jack
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,

Below are some findings during debugging this application:

( i ) As mentioned earlier in the threat, county 1 and 2 consist of 15 and 14 schools respectively.
( ii ) The first set<School> for county 1 have 15 unique records while the second set<School> for county 2 have 29 unique records (15 from first county + 14 of the second county). As a result, there were a total of 44 schools including 15 duplicates (from first county) in SCHOOL table.

Looks like the issue is from AddCounty() where it still keep past school records from previous county. Perhaps you could identify where it is coming from and offer a suggestion on resolving it.
Thanks,
Jack
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi James,
I am still back to square one after so many weeks of toiling on this issue despite having resolved the issue of populating the correct number of schools. Ie a total of 29 unique records for both counties 1 & 2. However, the duplicate records (when adding multiple of the same records) remained un-resolved. I have since simplified the testing process by supplying only a single identical school A (e.g. Local Secondary School (5 miles away) for both counties with the following outcomes:
COUNTY
ID Name
1 County 1
2 County 2


COUNTY_SCHOOL
ID COUNTY_ID SCHOOL_ID
1 County 2 1
2 County 1 2


SCHOOL
ID Name
1 School A
2 School A

Yet I wanted the following data to be generated instead:

COUNTY
ID Name
1 County 1
2 County 2


COUNTY_SCHOOL
ID COUNTY_ID SCHOOL_ID
1 County 1 1
2 County 2 1


SCHOOL
ID Name
1 School A

I am not clear on why the following behavior is taking place within JPA:
( i ) The ID for County 1 in COUNTY_SCHOOL table is 2 instead of 1 even though it was the first to have been added. Hence, ID for County 2 should be 2 instead of 1 for the same reason.
( ii ) More importantly, why is School A being populated twice even though the data/name is identical.
There appears to be some transactional issue/delay using the container managed JTA. Another intriguing symptom is the following exception occurred after having taken out the @GeneratedValue(strategy = GenerationType.IDENTITY) in School.java in the hope that only a single record is generated:

EJB5018: An exception was thrown during an ejb invocation on [CountyBean]
javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean; nested exception is: javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.School@ce9fa6].
Class> domain.School Primary Key> [0]
javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.School@ce9fa6].
Class> domain.School Primary Key> [0]
at oracle.toplink.essentials.internal.ejb.cmp3.base.EntityManagerImpl.persist(EntityManagerImpl.java:224)
at com.sun.enterprise.util.EntityManagerWrapper.persist(EntityManagerWrapper.java:440)
at ejb.CountyBean.createCounty(CountyBean.java:18)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
oracle.toplink.essentials.exceptions.ValidationException
... 38 more

On the other hand, another database exception occurred when directly persisting County & School using remoteCountybean.createCounty(county) as opposed to sending it through a message queue with sendJMSMessageToMyQueue(county) which works:

02/02/2011 5:22:14 PM com.sun.enterprise.appclient.MainWithModuleSupport <init>
WARNING: ACC003: Application threw an exception.
javax.ejb.EJBException: nested exception is: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: null; nested exception is:
javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.School@1fcea34].
Class> domain.School Primary Key> [1]
Caused by: javax.ejb.EJBException: nested exception is: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: null; nested exception is:
javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.School@1fcea34].
Class> domain.School Primary Key> [1]
at ejb._CountyRemote_Wrapper.createCounty(ejb/_CountyRemote_Wrapper.java)
at addCounty(localImportCounty.java:296)

EJB5018: An exception was thrown during an ejb invocation on [CountyBean]
javax.ejb.EJBException
at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3894)
at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3794)
at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3596)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1379)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1316)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:210)
at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:117)
at $Proxy82.createCounty(Unknown Source)
Caused by: javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.School@1fcea34].
Class> domain.School Primary Key> [1]
at oracle.toplink.essentials.internal.ejb.cmp3.base.EntityManagerImpl.persist(EntityManagerImpl.java:224)
at com.sun.enterprise.util.EntityManagerWrapper.persist(EntityManagerWrapper.java:440)
at ejb.CountyBean.createCounty(CountyBean.java:18)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1011)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:175)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2920)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4011)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:203)
... 17 more

The findSchool(name) method is working fine. In fact, all the code is working fine during debugging.

I am exhausted on guessing where the issue lies still and would appreciate your suggestion once again.

Many thanks,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
You code does not make any sense, "countyList_iterator" is never declare, so can not compile. What is the real code you are executing?
Also the "remoteCountybean.createCounty(county);" is outside of your while loop, which makes no sense.
Since you are not committing the transaction until you call createCountry(), if the same School was there twice, then it would be created as new twice.


Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
You are right James, it was a copy-past error only. Below is the minor correction of what had been posted (code was correct all along):


If only it would have been that simple.
I am trying to run the same method in a Bean Managed Transaction environment to see whether it will make any difference. Will keep you posted with the outcome.
Any other suggestion in the meantime?

Thanks,
Jack
 
jQuery in Action, 2nd edition
 
subject: Still getting duplicate in JPA ManyToMany Entity Relationship
 
Similar Threads
JPA - NoClassDefFoundError: jpa/County (EmbeddedId)
Apache Derby Sql Statement's
How to retrieve detached @ManyToOne entities with relationship
How to link existing record from database table in ManyToMany Uni-directional relationship
How to add JPA ManyToMany records?