Native query right outer join Hibernate core on Jboss 7
mbuamuh Apr 17, 2013 7:09 AMI was wondering if i can get an answer to this on this forum. I am writing a native query with a right outer join. But i keep getting a syntax error from hibernate-core-4.2.2.CR1.jar as stated below. Can someone please tell me where i am going wrong with my query. The query is:
select distinct app.app_id, app.app_name, "
+ " obj.object_id, obj.object_name, obj.object_type, "
+ " obj.object_descr, pobj.object_name "
+ " from BasisObjectEntity AS pobj RIGHT OUTER JOIN BasisObjectEntity AS obj, BasisApplicationEntity AS app, BasisAccessEntity AS acc"
+ " pobj.object_id = obj.func_object_id "
+ " and obj.app_id = app.app_id "
+ " and app.app_name = :appName "
+ " and obj.object_id = acc.object_id "
+ " and acc.role_id in (" + this.roles + ")"
The error message i get is(i also get the same error when i remove the AS):
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: pobj near line 1, column 402 [select distinct app.app_id, app.app_name, obj.object_id, obj.object_name, obj.object_type, obj.object_descr, pobj.object_name from com.ec.eccore.util.security.entities.BasisObjectEntity as pobj RIGHT OUTER JOIN BasisObjectEntity as obj, com.ec.eccore.util.security.entities.BasisApplicationEntity as app, com.ec.eccore.util.security.entities.BasisAccessEntity as acc pobj.object_id = obj.func_object_id and obj.app_id = app.app_id and app.app_name = :appName and obj.object_id = acc.object_id and acc.role_id in ('SYST.ADM','COST','ROOT','WEB','REVENUE','JBossAdmin')]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1734) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1]