I was trying to configure a many-to-many relation between documents and document bodies. Each document has a collection of bodies, where each body represents a different translation, so each body corresponds to a different language. Each body on the other hand can belong to many documents, thus making the relation many-to-many. On top of this configuration, I wanted to access the bodies of a document by their language id so the map-based solution seemed ok:
public class MyDocument {
...
@ManyToMany
@MapKey(name="langId")
private Map<Integer, MyDocumentBody> bodies;
...
}
The
bodies Map is keyed by the integer language id.Unfortunately, due to this bug, hibernate generates illegal sql for Oracle, when trying to initialize the
bodies Map. To be more specific, it uses a table alias in the FROM clause but it puts the "AS" keyword in front e.g.:
select * from TableA as a where blah, blah
The "as" is optional but acceptable by many databases. However it is not acceptable by Oracle, as it is explained in the links mentioned above. As I found here the generation of "as" is due to a bug in annotations and the upgrade to hibernate-annotations 3.3.0.GA is suggested. However, the use of this version requires an upgrade of the hibernate-entity manager to version 3.3.x as the compatibility matrix suggests. My troubles didn't end at this point though, because I was using jboss-4.0.5.GA whose EJB3-JPA support is not compatible with the new hibernate-entitymanager. The upgrade to 4.2.0.CR1 seemed inevitable (this has a better EJB3 support but the hibernate-* jars still needed upgrading). Alas, a
java.lang.NoClassDefFoundError: org/hibernate/annotations/common/reflection/ReflectionManager was waiting for me during deployment. Another google search and this finally solved my problem. hibernate-annotations-common.jar, which is not included in jboss by default, was needed in the lib folder. Four hours and a lot of googling later, I had my map-based many-to-many relation.
19 comments:
I just can't follow this madness about hibernate..
Just come across this very same issue.
Blog post sorted it all out, thanks a lot!
Happy to have helped you. Just to inform that the story started with jboss 4.0.5.GA , continues with 4.2.0.RC1 and now I 'm using 4.2.1.GA where the hibernate jars still need upgrading (not all of them though).
Seem to have spoken too soon. Still gettnig the same error.
I've upgraded to 4.2.1GA and then upgraded Hibernate to:
Hibernate Core 3.2.5.ga
Hibernate Annotations 3.3.0 GA
Hibernate EntityManager 3.3.1 GA
(added in that additional Annotations commons lib too)
I just replaced the libs in server\default\libs anything else I should do?
It's odd because I look at the source and it's definitely been removed:
Line 289 of
org\hibernate\cfg\annotations\MapBinder.java
//.append(" as ") //Oracle doesn't support it in subqueries
My configuration is:
hibernate3.jar 3.2.4.sp1
hibernate-entitymanager.jar 3.3.1.GA
hibernate-annotations.jar 3.3.0.GA
hibernate-commons-annotations.jar 3.0.0.GA
I 'll try with core 3.2.5.ga to see if it works.
Thanks for that.
I'm wondering if I'll simply have to resort to a native query in JBoss and hope for the best. Presumably that would bypass this issue.
Still, I don't want to be doing that for every query that has this problem. It defeats the point.
I just tried with hibernate core 3.2.5 and it works. Can you tell me more details about what exactly are you trying to do ?
Ok thanks...
I have two tables.
Users and UserSurveys
There is a one-to-many mapping between
Users to UserSurveys.
I'm trying to perform a query like to get users who haven't completed a survey before 'purchaseDate':
Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN FETCH cu.userSurveys us"
+ " WHERE cu.purchaseDate <= :purchaseDate"
+ " AND NOT EXISTS (SELECT us FROM cu.userSurveys us WHERE us.userId = cu.userId)");
query.setParameter("purchaseDate", new java.sql.Date(cal.getTimeInMillis()), TemporalType.DATE);
That's about it!
It still works for me. I have a similar one-to-many relation so I run the same query and it works ok. Is UserSurveys modeled as a Map?
To make it even simpler I've done:
Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN cu.userSurveys us"
+ " WHERE NOT EXISTS (SELECT us FROM cu.userSurveys us WHERE us.userId = cu.userId)");
Still the same problem,
Nope just a simple Set.
@OneToMany(mappedBy = "userId", cascade = {CascadeType.PERSIST, CascadeType.REMOVE}, fetch=FetchType.LAZY)
public Set getUserSurveys()
{
return userSurveys;
}
The Set above is a Set of UserSurvey but I can't post the tag on this blog ;)
13 September, 2007 16:42
The problem I describe in my post is specific to the case where the one-to-many relation is modeled as a Map and not a Set as is your case. Maybe the problem in your case is not in hibernate-annotations but somewhere else.
I will update you if anyone ever answers @ Hibernate or JBoss...
Thanks
I solve the same problem finding a jar file named hibernate-common-annotations.jar in the zip file and put into my WEB-INF/lib. I found this solution in this site
This is also correct. Putting the hibernate-common-annotations.jar in the WEB-INF/lib of your web application makes it available to that application only. I chose to put it in the jboss lib folder so that it is in the global classpath of any web application I deploy.
The solution I found maybe a fudge, but it works.
Instead of selecting the entire object in the inner select, select just the unique ID or similar.
E.g.
Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN cu.userSurveys us"
+ " WHERE NOT EXISTS (SELECT us FROM cu.userSurveys us WHERE us.userId = cu.userId)");
Goes to:
Query query = entityManager.createQuery("FROM " + User.class.getSimpleName()
+ " cu LEFT JOIN cu.userSurveys us"
+ " WHERE NOT EXISTS (SELECT us.userId FROM cu.userSurveys us WHERE us.userId = cu.userId)");
Post a Comment