wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Couldn't persist OneToMany JoinColumn/JoinTable (Unidir) using TopLink JPA    Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Couldn Watch "Couldn New topic
Author

Couldn't persist OneToMany JoinColumn/JoinTable (Unidir) using TopLink JPA

Jack Bush
Ranch Hand

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

I am having a lot of difficulty deploying a OneToMany Unidirectional (Zipcode) record which consist of multiple Zipnames using TopLink on Glassfish v2r2, JDK1.6.0_06, MySQL 5.0, Netbeans 6.1 and Windows XP platform.

Below are the relevant EJBs snippets:



The JoinColumn method would not work for one Zipname record. On the other hand, JoinTable approach would allow us to add a single Zipname record without error out.

Q1. Can you confirm whether TopLink JPA 1.0 (Glassfish v2r2) support OneToMany JoinColumn Unidirectional at all?
Q2. Does TopLink JPA 1.0 (Glassfish v2r2) support OneToMany JoinTable Unidirectional? If so, what is the cause of this issue? Otherwise, please make appropriate recommendation steps to take.

This question has been posted on http://forums.sun.com/thread.jspa?threadID=5333236.

Your guidance would be very much appreciated.

Many thanks,

Jack
masse Satriana
Greenhorn

Joined: Sep 21, 2008
Posts: 8
hi Jack,
I think your problem in "ZipcodeApplicationClient".
This is only java class which mean are not managed class.
So you cannot using @PersistanceContext on definition unmanaged class such yours.

Rgds,
Masse Satriana


A warm family is worthy enough than hard working.
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi,

Below are some more error output from the OneToMany JoinTable Unidirectional approach:

pre-run-deploy:
Initial deploying InvestmentBean to C:\Documents and Settings\abc\InvestmentBean\dist\gfdeploy
Completed initial distribution of InvestmentBean
Start registering the project's server resources
Finished registering server resources
moduleID=InvestmentBean
deployment started : 0%
deployment finished : 100%
Deploying application in domain completed successfully
Trying to create reference for application in target server completed successfully
Trying to start application in target server completed successfully
WARNING:
JDO76614: Deployment encountered SQL Exceptions:
JDO76609: Got SQLException executing statement "CREATE TABLE ZIPCODE (ID INTEGER NOT NULL, DIALING_CODE VARCHAR(255), TIME_ZONE VARCHAR(255), CODE INTEGER, NEAREST_AIRPORT VARCHAR(255), LONGITUDE VARCHAR(255), NEAREST_TRAIN_STATION VARCHAR(255), NEAREST_URBAN_CENTRE VARCHAR(255), HOTELS VARCHAR(255), LATITUDE VARCHAR(255), NEARBY_FEATURES VARCHAR(255), COUNCIL VARCHAR(255), LOCALITY VARCHAR(255), PRIMARY KEY (ID))": com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'zipcode' already exists
JDO76609: Got SQLException executing statement "CREATE TABLE ZIPCODE_ZIPNAME (ZIPCODE_ID INTEGER NOT NULL, ZIPNAME_ID INTEGER NOT NULL, PRIMARY KEY (ZIPCODE_ID, ZIPNAME_ID))": com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'zipcode_zipname' already exists
JDO76609: Got SQLException executing statement "CREATE TABLE ZIPNAME (ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID))": com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'zipname' already exists
JDO76609: Got SQLException executing statement "ALTER TABLE ZIPCODE_ZIPNAME ADD CONSTRAINT FK_ZIPCODE_ZIPNAME_ZIPCODE_ID FOREIGN KEY (ZIPCODE_ID) REFERENCES ZIPCODE (ID)": java.sql.SQLException: Can't create table '.\geographicaldb\#sql-434_4.frm' (errno: 121)
*JDO76609: Got SQLException executing statement "ALTER TABLE ZIPCODE_ZIPNAME ADD CONSTRAINT FK_ZIPCODE_ZIPNAME_ZIPNAME_ID FOREIGN KEY (ZIPNAME_ID) REFERENCES ZIPNAME (ID)": java.sql.SQLException: Can't create table '.\geographicaldb\#sql-434_4.frm' (errno: 121)*Enable of GeographicalBean in target server completed successfully
Enable of application in all targets completed successfully
All operations completed successfully
post-run-deploy:
run-deploy:
run-display-browser:
run-ac:
Copying 1 file to C:\Documents and Settings\Jack\GeographicalBean\dist
Caught an unexpected exception!
java.lang.NullPointerException
at client.ZipcodeApplicationClient.main(ZipcodeApplicationClient.java:45)
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.util.Utility.invokeApplicationMain(Utility.java:266)
at com.sun.enterprise.appclient.MainWithModuleSupport.<init>(MainWithModuleSupport.java:449)
at com.sun.enterprise.appclient.MainWithModuleSupport.<init>(MainWithModuleSupport.java:259)
at com.sun.enterprise.appclient.Main.main(Main.java:200)
run-GeographicalBean-app-client:
run:

I would like to correct an earleir comment where I stated that this method did work when adding a single Zipname record. This is no true.

Thanks,

Jack
Jack Bush
Ranch Hand

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

Thanks a lot for responding to this question.

I have called ZipcodeBean to carry out the persisting of zipcode_1 record in ZipcodeApplicationClient instead of carrying out the persisting it directly. Below is the changes made in ZipcodeApplicationClient and the detail of both ZipcodeBean and ZipnameBean:


Nevertheless, here are the error output when extracting the same Zipcode record back out:

Caught an unexpected 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.Zipname@322b2e].
Class> domain.Zipname Primary Key> [0]
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.Zipname@322b2e].
Class> domain.Zipname Primary Key> [0]
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.__ZipcodeRemote_Remote_DynamicStub.createZipcode(ejb/__ZipcodeRemote_Remote_DynamicStub.java)
at ejb._ZipcodeRemote_Wrapper.createZipcode(ejb/_ZipcodeRemote_Wrapper.java)
at client.ZipcodeApplicationClient.main(ZipcodeApplicationClient.java:44)
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.util.Utility.invokeApplicationMain(Utility.java:266)
at com.sun.enterprise.appclient.MainWithModuleSupport.<init>(MainWithModuleSupport.java:449)
at com.sun.enterprise.appclient.MainWithModuleSupport.<init>(MainWithModuleSupport.java:259)
at com.sun.enterprise.appclient.Main.main(Main.java:200)
Caused by: java.rmi.RemoteException: null; nested exception is:
javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.Zipname@322b2e].


Any ideas on why this is occurring?

Thanks very much again,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Your current error,

javax.persistence.EntityExistsException:
Exception Description: Cannot persist detached object [domain.Zipname@322b2e].

means that the entity you are trying to insert (persist) already exists, (you already have an object is your table with the same id).

Seems like you need to clean up your database. From your previous errors it seems that you are trying to recreate your tables, but they already exist, so you may need to drop your old tables.

In general JPA 1.0 and TopLink Essentials do not support having a @OneToMany with a @JoinColumns, you must either use a mappedBy (and have a @ManyToOne back), or use an @JoinTable.

JPA 2.0 and EclipseLink 1.1 (Glassfish v3) will support unidirection @OneToMany with @JoinColumns.

You may also wish to enable logging so you get the errors that are occurring on your server.


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

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

Thanks for responding back to this question.

The persistence property for GeographicalDB is set to drop-and-create-tables and I made sure that the Geographical.EAR project is undeployed prior to deployment. As a result, any tables created from previous deployments will have been dropped.

Btw, any idea on when JPA 2.0 and EclipseLink 1.1 (Glassfish v3) will be available?

I will enable logging and try to debug it to see what is going on.

Thanks again,

Jack
Jack Bush
Ranch Hand

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

The only logging setup that I could find on Glassfish Admin Console is the Logging Level for Persistence (TopLink) where I have set it to FINEST.

Can you confirm whether this is the type of logging you are referring to? Or is it the "Start in debug mode" which is used every time the server is started?

Thanks,

Jack
Jack Bush
Ranch Hand

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

You are right about the zipname_1 and zipname_2 having occupied the same ID of 0 which generated the javax.persistence.EntityExistsException. This means that both ZIPCODE & ZIPNAME weren't setting their IDs correctly because @GeneratedValue was not being defined. As a result, I have added the following line below @ID in both Zipcode and Zipname entity beans:

@GeneratedValue(strategy = GenerationType.AUTO)

The deployment process no longer encounter Persistent Exception with the following output:

Copying 1 file to C:\Documents and Settings\Jack\GeographicalBean\dist
zipcode_2.getId(): 1
zipcode_2.getCode(): 9001
Number of names: 2
domain.Zipname@1495bb8
domain.Zipname@107e9a8
.....

zipcode_2.getId(): 1
zipcode_2.getCode(): 9001
Number of names: 2
domain.Zipname@1495bb8
domain.Zipname@107e9a8
........
run-GeographicalBean-app-client.

Now I am left with only 2 issues:

(i) Why does the fetchZipcodesWithRelationships() returned 2 identical zipcode_1 when there are only 1 record in the database?
( ii ) Why doesn't the Iterator statement in ZipcodeApplicationClient below retrieves the Zipnames correctly? ie domain.Zipname@1495bb8, domain.Zipname@107e9a8 when the ZIPNAME table consists of "Harbour Cove" (ID-1) and "Wonderland" (ID-2), where both have identical foreign key (ID-1) pointing back to the same zipcode_1 record.

Iterator iterator = zipcode_2.getNames().iterator();
while (iterator.hasNext())
{
System.out.println(iterator.next());
}

Perhaps there is a different ways to retrieve these Zipname records correctly.

Thanks in advance,

Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Your one query "SELECT zc FROM Zipcode zc LEFT JOIN FETCH zc.zipnames" gets duplicates because of the JOIN FETCH, the JPA spec actually requires this (TopLink filters duplicates by default, but for JPA we had to allow them...).

There is an option you can set on the TopLink ReadAllQuery (setShouldFilterDuplicates(true)) to filter the duplicates, or you could just use a Set for the result collection class.

For the second issue you seem to be seeing the toString() of your objects, if you want them printed differently, then change your toString() or access their name in your printer.
Jack Bush
Ranch Hand

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

The duplicate query result list has been resolved by adding a DISTINCT entry into the query SELECT statement.

The second issue has also been addressed (indirectly - not ideal) by adding fetchZipnamesWithRelationships() to ZipcodeBean & calling fetchZipnamesWithRelationships() to retrieve Zipname records in ZipcodeApplicationClient as follows:


However, this round about approach to retrieve all Zipname records will pick up every records in ZIPNAME table which is not correct. In other word, I am still not able to query the 2 Zipname records (Harbour Cove, Wonderland) via the zipcode_2 (OneToMany - Unidirection) relationship. Let's quickly re-visit the orginal attempts to extract these 2 Zipname records using the OneToMany - Unidirectional approach in ZipcodeApplicationClient without success:


As a result, I am still faced with the difficulty of not be able to do something like "iterator.next().getName()" (no such option) and instead it has returned "iterator.next().toString()", which turned out to be domain.Zipname@1495bb8, domain.Zipname@107e9a8.
Any assistance would be very much appreciated still.
Many thanks again,
Jack
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
... you can cast to Zipname and call getName().

((Zipname)iterator.next()).getName()
Jack Bush
Ranch Hand

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

Yes, everything is now worked perfectly just the way I wanted it.

Thanks a million in helping me with grasping some key JPA concepts.

You are a legend!

Jack
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Couldn't persist OneToMany JoinColumn/JoinTable (Unidir) using TopLink JPA
 
Similar Threads
EJB 3 in Action - JPA Doubt
How to avoid duplicate inserts in JPA HQL (EJB 3.0 - Java EE 5)
testing jpa - OneToMany
How to retrieve detached @ManyToOne entities with relationship
Need help to correct @JoinColumn mapping in entity class with composite keys