Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Eclipselink's JPA cannot map @MapKeyColumn to a join table ?

 
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I noticed an interesting issue from EclipseLink.
This is the code from manyToManyMap in Chapter 5:




There is a join table Dept_Emp with one more field CUB_ID


CREATE TABLE EMPLOYEE (ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, NAME VARCHAR(255), SALARY BIGINT, PRIMARY KEY (ID));
CREATE TABLE DEPARTMENT (ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID));
CREATE TABLE DEPT_EMP (DEPT_ID INTEGER NOT NULL, EMP_ID INTEGER NOT NULL, CUB_ID VARCHAR(255), PRIMARY KEY (DEPT_ID, EMP_ID));
ALTER TABLE DEPT_EMP ADD CONSTRAINT DEPTREF FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (ID);
ALTER TABLE DEPT_EMP ADD CONSTRAINT EMPREF FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE (ID);



When this employeeServiceBean is called:


I got this error from the console:


[EclipseLink-6069] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [DEPARTMENT.CUB_ID] in this expression has an invalid table in this context.
Query: ReadAllQuery(name="employeesByCubicle" referenceClass=Employee )



The EclipseLink is looking for CUB_ID from Department table instead of the DEPT_EMP table.
 
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The mapping looks fine to me. I just set up your example and let the tables be created by the JPA implementation.

How does the query look like that is causing the problems?
DepartmentEmployee.jpg
[Thumbnail for DepartmentEmployee.jpg]
DeptEmp
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Frits,
The book provided the queries to create those tables: Department, Employee, DEPT_EMP.
When I run the servlet that calls the addEmployeeDepartment method of the EmployeeServiceBean, it throws an exception saying that  DEPARTMENT_CUB_ID is not found.
I think the persistence provider cannot map CUB_ID to column to the DEPT_EMP.
 
Frits Walraven
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, I will try that tomorrow and will let you know.
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Frits, I let the JPA provider to generate the tables.
I ran the example.
I got the CUB_ID column empty.
cub_id_empty.JPG
[Thumbnail for cub_id_empty.JPG]
Cub_ID column is empty
 
Frits Walraven
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried it also and it is definitely a bug.

Did a search and found a eclipselink bug report

The work around is to explicitly name the table in the MapKeyColumn annotation:
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Frits,
Thanks for your suggestion.
I downloaded the latest version EclipseLink 2.6.4 at https://mvnrepository.com/artifact/org.eclipse.persistence/org.eclipse.persistence.jpa/2.6.4,
I rename it from org.eclipse.persistence.jpa-2-6-4.jar to org.eclipse.persistence.jpa.jar
I overwrite the org.eclipse.persistence.jpa.jar in <glassfish>/glassfish/modules/

Then, the @MapKeyColumn (name="CUB_ID") will map the map key to CUB_ID column.
 
Frits Walraven
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nice! Problem solved.
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found an interesting issue.
I ran this manyToManyMap example (the one with @MayKeyColumn (name="CUB_ID")in Department's entity)with :
1. Glassfish 4.0 with MySQL 5.5, the CUB_ID column can be mapped.
2. Glassfish 4.0 web profile with MySQL 5.6, the CUB_ID column cannot be mapped.

Both Glassfish is using the same org.eclipse.persistence.jpa.jar version 2.6.4.

I guess it may have something to do with MySQL.
Glassfish 4.0 and Glassfish 4.0 web profile should be the same in terms of running the persistence provider.

Maybe, @MapKeyColumn(name="...") is not portable.
@MapKeyColumn(name="...", table="....") is more portable.
 
Frits Walraven
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Maybe, @MapKeyColumn(name="...") is not portable.
@MapKeyColumn(name="...", table="....") is more portable.


As long as the MapKeyColumn is in the specification (and it is without restrictions) it is portable. However most of the JPA implementations I have worked with do not fully comply to the JPA specifications.
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Frits. Thanks for your suggestion.
Is it possible that the database vendor does not comply with the specification?

Like in this case, I use the same org.eclipse.persistence.jpa.jar (version 2.6.4) with both Glassfish (one machine with full version and another machine with web profile version).
But I use MySQL 5.5 in one machine and MySQL 5.6 in another machine.
The MapKeyColumn can be mapped in MySQL 5.5, but not in MySQL 5.6.
That is very interesting.
 
Himai Minh
Ranch Hand
Posts: 1738
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, for everyone's information:
My latest solution is to download the latest version of Glassfish 4.1.2.
Store the mysql-connector-java-5.1.39-bin.jar (a jdbc driver for MySQL ) in the  Glassfish directory : glassfish-4.1.2/glassfish4/glassfish/domains/domain1/lib
Now, @MapKeyColumn(name="CUB_ID") can be mapped to the DEPT_EMP table.

 
Frits Walraven
Creator of Enthuware JWS+ V6
Posts: 3287
296
Android Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Is it possible that the database vendor does not comply with the specification?


The database provider does not have to comply with the JPA specifications, but the JPA implementation has to.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!