Friday, April 13, 2007

ORA-00907: missing right parenthesis

What follows is not new. It is very well known by jboss+hibernate users and a lot of info can be found at jboss and hibernate forums. Anyway, it took me almost four hours to clean up the mess so I think it is suitable for this blog (for those who have not realized yet, this blog is mostly about my troubles in the java world). So here it goes:

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:

Ilias said...

I just can't follow this madness about hibernate..

Dean said...

Just come across this very same issue.

Blog post sorted it all out, thanks a lot!

chstath said...

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).

Dean said...

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?

Dean said...

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

chstath said...

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.

Dean said...

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.

chstath said...

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 ?

Dean said...

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!

chstath said...

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?

Dean said...

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,

Dean said...
This comment has been removed by the author.
Dean said...

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

chstath said...

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.

Dean said...

I will update you if anyone ever answers @ Hibernate or JBoss...

chstath said...

Thanks

Mapscript said...

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

chstath said...

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.

Dean said...

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)");