7 Replies Latest reply on Apr 19, 2013 2:41 AM by Nicklas Karlsson

    Native query right outer join Hibernate core on Jboss 7

    Unice Gwe Mbuamuh Newbie

      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]

        • 2. Re: Native query right outer join Hibernate core on Jboss 7
          Unice Gwe Mbuamuh Newbie

          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

          • 3. Re: Native query right outer join Hibernate core on Jboss 7
            Tomaz Cerar Master

            Hi,

             

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

             

            Can you post code where you execute your query?

             

             

             

            --

            tomaz

            • 4. Re: Native query right outer join Hibernate core on Jboss 7
              Unice Gwe Mbuamuh Newbie

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

              • 5. Re: Native query right outer join Hibernate core on Jboss 7
                Tomaz Cerar Master

                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

                • 6. Re: Native query right outer join Hibernate core on Jboss 7
                  Unice Gwe Mbuamuh Newbie

                  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]

                  • 7. Re: Native query right outer join Hibernate core on Jboss 7
                    Nicklas Karlsson Master

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