Hi All,
I need your advice on identify what appears to be a persisting a detached School (ManyToMany) entity but could not understand why this is occurring:
1. The data supplied is made up of detail between counties and surrounding schools. There are a dozen of schools on average in any county. However, there
will be some over-lapping schools that are a long the border of adjacent counties. For instance, county 1 consists of A, B, C, D, E, F, G, H, I, J, K, L schools while county 2
(next to each other) is made up of M, N, B, O, P, Q, R, E, S, T, U, H schools. The common ones are B, E and H and are stored as duplicate records in the
SCHOOL table.
2.
3. Lets look at the following code snippets on my attempt to come up with a solution without success still for quite sometime:
4.
5. @Entity
6. @IdClass(CountyPK.class)
7. @Table(name="COUNTY", catalog="CountyDB", schema="")
8. public class County implements Serializable {
9.
10. @Id
11. @Column(name="ZIPCODE")
12. private String zipcode;
13.
14. @Id
15. @Column(name="COUNTY")
16. private String county;
17.
18. @Id
19. @Enumerated(EnumType.STRING)
20. @Column(name="STATE")
21. @ManyToMany(cascade={CascadeType.PERSIST}, fetch=FetchType.EAGER)
22. @JoinTable(name="COUNTY_SCHOOL", catalog="CountyDB", schema="",
23. joinColumns={@JoinColumn(name="ZIPCODE", referencedColumnName="ZIPCODE"),
24. @JoinColumn(name="COUNTY", referencedColumnName="COUNTY"),
25. @JoinColumn(name="STATE", referencedColumnName="STATE")},
26. inverseJoinColumns={@JoinColumn(name="SCHOOL", referencedColumnName="ID")})
27. private Set<School>; schools = new HashSet<School>();
28. public Set<School> getSchools()
29. {
30. return schools;
31. }
32. public void setSchools(Set<School> hotels)
33. {
34. this.schools = schools;
35. }
36. }
37.
38. @Entity
39. @Table(name="SCHOOL", catalog="CountyDB", schema="")
40. public class School implements Serializable {
41.
42. @Id
43. @GeneratedValue(strategy = GenerationType.IDENTITY)
44. @Column(name="ID")
45. private int id;
46.
47. @Column(name="SCHOOL_NAME")
48. private String schoolName;
49.
50. @ManyToMany(mappedBy="schools", cascade={CascadeType.ALL}, fetch=FetchType.EAGER)
51. private Set<County> counties = new HashSet<County>();
52. public Set<County> getCounties()
53. {
54. return counties;
55. }
56. public void setCounties(Set<County> counties)
57. {
58. this.counties = counties;
59. }
60.
61. @Stateless
62. public class CountyBean implements CountyRemote {
63.
64. @PersistenceContext(unitName="CountyDB-PU") private EntityManager manager;
65.
66. public void createCounty(County county)
67. {
68. manager.persist(county);
69. manager.flush();
70. }
71.
72. public void saveOrUpdateCounty(County county)
73. {
74. manager.merge(county);
75. manager.flush();
76. }
77.
78.
79. public County findCounty(String zipcode, String county, State state)
80. {
81. CountyPK pk = new CountyPK(zipcode, county, state);
82. return manager.find(County.class, pk);
83. }
84.
85. public List fetchCountiesWithRelationships()
86. {
87. List list = manager.createQuery("SELECT DISTINCT c FROM County c LEFT JOIN FETCH c.counties").getResultList();
88. for (Object obj : list)
89. {
90. County county = (County)obj;
91. }
92. return list;
93. }
94.
95. @Stateless
96. public class SchoolBean implements SchoolRemote {
97.
98. @PersistenceContext(unitName="CountyDB-PU") private EntityManager manager;
99.
100. public void createSchool(School school)
101. {
102. manager.persist(school);
103. }
104.
105. public void saveOrUpdateSchool(School school)
106. {
107. manager.merge(school);
108. }
109.
110. public School findSchool(int school_id)
111. {
112. return manager.find(School.class, school_id);
113. }
114.
115. public School findSchool(String schoolName)
116. {
117. School school = null;
118. Query query = manager.createQuery("Select s FROM School s where s.schoolName = :schoolName");
119. query.setParameter("schoolName", schoolName);
120. List list = query.getResultList();
121. for (Object obj : list)
122. {
123. school = (School)obj;
124. if (school.getSchoolName().matches(schoolName))
125. return school;
126. }
127. return school;
128. }
129.
130. public List fetchSchoolsWithRelationships()
131. {
132. List list = manager.createQuery("SELECT DISTINCT s FROM School s LEFT JOIN FETCH s.schools").getResultList();
133. for (Object obj : list)
134. {
135. School school = (School)obj;
136. }
137. return list;
138. }
139. }
140.
141. public class ApplicationClientAddCounty {
142.
143. @EJB
144. private static CountyRemote remoteCountybean;
145. @EJB
146. private static SchoolRemote remoteSchoolbean;
147.
148. public static void main(String[] args)
149. {
150. BufferedReader br = new BufferedReader(new FileReader("COUNTY.XML"));
151. while (countyList_iterator.hasNext())
152. {
153. County county = new County();
154. county.setZipcode(((org.jdom.Element)countyList_iterator.next()).getChild("zipcode");
155. while (schoolList_iterator.hasNext())
156. {
157. String school_name = ((org.jdom.Content)schoolsList_iterator.next()).getValue();
158. if (school_name.length() != 0)
159. {
160. School school = null;
161. if (!school_name.contains("Schools:"))
162. {
163. school = remoteSchoolbean.findSchool(school_name);
164. if (school == null)
165. {
166. school = new School();
167. school.setSchoolName(school_name);
168. }
169. county.getSchools().add(school);
170. }
171. }
172. }
173. }
174. remoteCountybean.createCounty(county);
175. }
Below is the a simplistic set of the data available which resulted in deplicate School records being persisted:
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 only a single normalized School 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 is 2 instead of 1. Vice versa for County 2.
( ii ) More importantly, why is School A being populated twice even though the data is identical.
There appears to be some transactional issue/delay using the container managed JTA. Another intriguing symptom is that 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)I am running JDK1.6.0_17, GF 2.1 on XP. This is a Java EE 5 Enterprise Application.
It would be much appreciated for some guidance in an unfamiliar territory. I have had many attempts and read up quite a number of similar threats
But none offer concrete results.
Thanks in advance,
Jack