Posted under JPA
Permalink
Tags Bug, Eclipselink, JPA, Oracle, Tip
I found a number of issues when configuring a many to many relationship, but eventually found a working solution.
1/ This example for a many to many uses referencedColumnName when it does not need to – it was a hangover from an example using multiple join columns. If you do this with Eclipselink 1.1.2 and Oracle (in my case XE 10g), the columns are created with data types of varchar2(255) instead of the default of number(19) :-
@ManyToMany
@JoinTable(name="AppUserRole",
joinColumns =@JoinColumn(name="AppUserID",
referencedColumnName="AppUserID"),
inverseJoinColumns=@JoinColumn(name="AppRoleID",
referencedColumnName="AppRoleID”))
The referencedColumnName attribute is the cause of this issue. Leaving it out causes correct column types. It is only needed for multiple column joins (which break anyway see 2/), and so should not be used. This therefore works correctly and is the recommended format to use :-
@ManyToMany @JoinTable(name="AppUserRole", joinColumns =@JoinColumn(name="AppUserID"), inverseJoinColumns=@JoinColumn(name="AppRoleID"))
2/ Using a many to many as in 1/ but with multiple join columns causes eclipselink bug 300485. (Although listed as a one to many bug it also happens with many to many). This is not due to be fixed until eclipselink 2.1. The bug gives a query parameter not found error for an internally generated query used when eclipselink lazily loads a relationship collection.
3/ Leaving out the @JoinTable and only having the @ManyToMany annotation works ok, but gives an XML column name resolution error from eclipse for one of the join columns. This is purely an ‘invalid validation’ however, as the code runs fine against a database created from it.
4/ using the xml annotations in orm.xml along with just an @ManyToMany annotation in the code works fine, but you do get some validation errors from Eclipse as the validation does not appear to merge the annotations and xml correctly when validating :-
<entity> <attributes> <many-to-many name="appRoles"> <join-table name="AppUserRole"> <join-column name="AppUserID" column-definition="number(19)"/> <inverse-join-column name="AppRoleID" column-definition="number(19)"/> </join-table> </many-to-many> </attributes> </entity>
This would be the preferred route if anything database specific was needed, such as the column-definition attributes for Oracle in the example. The “@ManyToMany” annotation on the entity may be superfluous in this case but is a helpful label. A comment in the code that there are overrides in orm.xml would be helpful. The above fragment was tested but without the column-definition attributes on the columns – these are shown as examples of how to add database specific column definitions without having to pollute the code with them via annotations.
The intention here would be to use xml in conjunction with annotations, with annotations used for all the standard metadata. Different versions of orm.xml and persistence.xml could then be swapped in and out for different back end databases, keeping the code standard. The same approach has been advocated to permit using Oracle sequences here.