Native query right outer join Hibernate core on Jboss 7

[mbuamuh]

I 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]

[nickarls]

You are missing a where-statement?

[mbuamuh]

Thanks Nicklas. What a mistake?  I mistakenly deleted the where clause and for the life of me could never have seen it. But now i am have the following query; and i am having the exception below. Can you please advice on that? Please see below:

Query: 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 fetch BasisObjectEntity AS obj, BasisApplicationEntity AS app, BasisAccessEntity AS acc where 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 + ")"

Exception: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [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 fetch BasisObjectEntity AS obj, com.ec.eccore.util.security.entities.BasisApplicationEntity AS app, com.ec.eccore.util.security.entities.BasisAccessEntity AS acc where 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.analyze(QueryTranslatorImpl.java:255) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) [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.(HQLQueryPlan.java:105) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.engine.query.spi.HQLQueryPlan.(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] ... 193 more

[ctomc]

Hi,

looking at stracktrace your are not calling nativeQuery but just ordinary OQL query.

Can you post code where you execute your query?

--

tomaz

[mbuamuh]

Hi Tomaz,

Below is the code:

String GetObjectAccessList = "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 fetch BasisObjectEntity AS obj, BasisApplicationEntity AS app, BasisAccessEntity AS acc"

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

        cat.info("Getting Entity manager ----------");

        Query objectAccessQuery = entityManager

                .createQuery(GetObjectAccessList);

        objectAccessQuery.setParameter("appName", appName);

        objectAccessQuery.setHint(hintName, true);

        Map queryCache = new HashMap();

        cat.debug("-------------------------------------Something special ....");

        cat.debug("Running the following query " + GetObjectAccessList);

        List<Object> objectAccessList = objectAccessQuery.getResultList();

[ctomc]

Hi,

you need to call entityManager.createNativeQuery(...)

see http://docs.oracle.com/javaee/6/api/javax/persistence/EntityManager.html#createNativeQuery%28java.lang.String%29 for more details

but beyond that it looks ok.

--

tomaz

[mbuamuh]

Hi Tomaz,

I have done that and i am having the following exception at objectAccessQuery.getResultList():

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1]

    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1]

    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1]

[nickarls]

set SQL query output on and output the final SQL query as the server sees it