6 Replies Latest reply on Apr 17, 2009 8:19 PM by Harry Holt

    NPE on join query

    Harry Holt Newbie

      I'm trying to do a fairly simple join query on a pair of tables with a onetomany relationship.  This seems like a pretty basic query, but it just triggers a NullPointerException on SessionFactoryHelper.findSQLFunction.


      Here's the query:


               try {
                    fleas = (List<Flea>)entityManager
                         .createQuery("SELECT Cat FROM Flea flea " + 
                                   " LEFT OUTER JOIN FETCH flea.cat ")
                         .getResultList();
               } catch(Exception ex) {
                    log.error("There was an error: #0", ex.getMessage());
                    ex.printStackTrace();
               }
               return fleas;
      



      In Flea.java, the relationship is defined this way:



           @ManyToOne
           @JoinColumn(name="catId")
           public Cat getCat() {
                return cat;
           }
      



      In Cat.java, the reverse relationship is defined to allow a list of fleas for each cat:



           @OneToMany
              public List<Flea> getFleas() {
                return fleas;
           }
      



      So why can't I do this?  How would I do this kind of join - or is it even possible?  Has anybody else run into this issue, and if so, how did you work around it?


      Thanks for any hints.


      ... Squirrel

        • 1. Re: NPE on join query
          Harry Holt Newbie

          Oops - I forgot to post the error:


          00:01:30,329 ERROR [CatControl] There was an error: null
          00:01:30,329 ERROR [STDERR] java.lang.NullPointerException
          00:01:30,330 ERROR [STDERR]      at org.hibernate.hql.ast.util.SessionFactoryHelper.findSQLFunction(SessionFactoryHelper.java:342)
          00:01:30,330 ERROR [STDERR]      at org.hibernate.hql.ast.tree.IdentNode.getDataType(IdentNode.java:266)
          00:01:30,330 ERROR [STDERR]      at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:143)
          00:01:30,330 ERROR [STDERR]      at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
          00:01:30,331 ERROR [STDERR]      at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:92)
          00:01:30,331 ERROR [STDERR]      at org.jboss.seam.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:81)
          00:01:30,331 ERROR [STDERR]      at org.domain.testy1.session.CatControl.getCatFleas(CatControl.java:38)
          00:01:30,332 ERROR [STDERR]      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          00:01:30,332 ERROR [STDERR]      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
          00:01:30,332 ERROR [STDERR]      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
          00:01:30,332 ERROR [STDERR]      at java.lang.reflect.Method.invoke(Unknown Source)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
          00:01:30,332 ERROR [STDERR]      at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.core.SynchronizationInterceptor.aroundInvoke(SynchronizationInterceptor.java:32)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
          00:01:30,333 ERROR [STDERR]      at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
          00:01:30,333 ERROR [STDERR]      at org.domain.testy1.session.CatControl_$$_javassist_7.getCatFleas(CatControl_$$_javassist_7.java)
          00:01:30,333 ERROR [STDERR]      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          00:01:30,333 ERROR [STDERR]      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
          00:01:30,333 ERROR [STDERR]      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
          00:01:30,333 ERROR [STDERR]      at java.lang.reflect.Method.invoke(Unknown Source)
          00:01:30,333 ERROR [STDERR]      at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
          00:01:30,333 ERROR [STDERR]      at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
          00:01:30,333 ERROR [STDERR]      at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
          00:01:30,334 ERROR [STDERR]      at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
          00:01:30,334 ERROR [STDERR]      at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
          00:01:30,334 ERROR [STDERR]      at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
          00:01:30,334 ERROR [STDERR]      at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIData.getValue(UIData.java:582)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIData.getDataModel(UIData.java:1063)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIData.setRowIndex(UIData.java:417)
          00:01:30,334 ERROR [STDERR]      at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:85)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:788)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIData.encodeBegin(UIData.java:879)
          00:01:30,334 ERROR [STDERR]      at org.ajax4jsf.renderkit.RendererBase.renderChild(RendererBase.java:280)
          00:01:30,334 ERROR [STDERR]      at org.ajax4jsf.renderkit.RendererBase.renderChildren(RendererBase.java:262)
          00:01:30,334 ERROR [STDERR]      at org.richfaces.renderkit.html.PanelRenderer.doEncodeChildren(PanelRenderer.java:220)
          00:01:30,334 ERROR [STDERR]      at org.richfaces.renderkit.html.PanelRenderer.doEncodeChildren(PanelRenderer.java:215)
          00:01:30,334 ERROR [STDERR]      at org.ajax4jsf.renderkit.RendererBase.encodeChildren(RendererBase.java:121)
          00:01:30,334 ERROR [STDERR]      at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:812)
          00:01:30,335 ERROR [STDERR]      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
          00:01:30,335 ERROR [STDERR]      at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
          00:01:30,335 ERROR [STDERR]      at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
          00:01:30,335 ERROR [STDERR]      at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:108)
          00:01:30,335 ERROR [STDERR]      at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:196)
          00:01:30,335 ERROR [STDERR]      at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:106)
          00:01:30,335 ERROR [STDERR]      at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
          00:01:30,335 ERROR [STDERR]      at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
          00:01:30,335 ERROR [STDERR]      at javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
          00:01:30,335 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
          00:01:30,335 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
          00:01:30,335 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
          00:01:30,335 ERROR [STDERR]      at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:40)
          00:01:30,335 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,335 ERROR [STDERR]      at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
          00:01:30,335 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,336 ERROR [STDERR]      at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:177)
          00:01:30,336 ERROR [STDERR]      at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:267)
          00:01:30,336 ERROR [STDERR]      at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:380)
          00:01:30,336 ERROR [STDERR]      at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:507)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,336 ERROR [STDERR]      at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58)
          00:01:30,337 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,337 ERROR [STDERR]      at org.jboss.seam.web.HotDeployFilter.doFilter(HotDeployFilter.java:53)
          00:01:30,337 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
          00:01:30,337 ERROR [STDERR]      at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
          00:01:30,337 ERROR [STDERR]      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
          00:01:30,337 ERROR [STDERR]      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:432)
          00:01:30,337 ERROR [STDERR]      at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
          00:01:30,337 ERROR [STDERR]      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
          00:01:30,338 ERROR [STDERR]      at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
          00:01:30,338 ERROR [STDERR]      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
          00:01:30,338 ERROR [STDERR]      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
          00:01:30,338 ERROR [STDERR]      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
          00:01:30,338 ERROR [STDERR]      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
          00:01:30,338 ERROR [STDERR]      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
          00:01:30,338 ERROR [STDERR]      at java.lang.Thread.run(Unknown Source)
          

          • 2. Re: NPE on join query
            Stuart Douglas Master

            Try this:


            SELECT c FROM Flea flea LEFT OUTER JOIN flea.cat c
            

            • 3. Re: NPE on join query
              Harry Holt Newbie

              Thanks, that does work, but it gives me a Cat object.  What I really want is a list of fleas - I guess I mangled the query trying to simplify it down to isolate the issue.


              What I have in this scenario is that I need a list of all fleas that have, say, between 2 and 4 legs, but I want to order them by the name of the cat they are on, so something like (if it was SQL):


              SELECT * FROM Flea f LEFT OUTER JOIN Cat c ON f.catId = c.id
              WHERE f.legs BETWEEN 2 AND 4 
              ORDER BY c.name
              



              But anything close to that causes the NPE.  What I think should work is:


              SELECT Flea FROM Cat c LEFT OUTER JOIN c.fleas f 
              



              But, NPE, even without the WHERE and ORDER BY clauses.


              • 4. Re: NPE on join query
                Zoltan Tiringer Newbie

                I think this should work:



                SELECT flea, c FROM Flea flea LEFT OUTER JOIN FETCH flea.cat c



                I'm not sure whether you really need the outer join as you just flipped it in your previous post (and also you want orderBy according to a probably not existing value - c.name in your SQL), but you should know your model...


                • 5. Re: NPE on join query
                  Zoltan Tiringer Newbie

                  Correction, I would rather try this:



                  SELECT flea FROM Flea flea LEFT OUTER JOIN FETCH flea.cat c


                  • 6. Re: NPE on join query
                    Harry Holt Newbie

                    Excellent!  That works perfectly.  The entire thing (in this test scenario) ended up like this:


                    SELECT flea FROM Flea flea LEFT OUTER JOIN FETCH flea.cat c 
                    WHERE flea.legs BETWEEN :legsLower AND :legsUpper 
                    ORDER BY c.name
                    



                    Works fine and produces the results as expected.


                    Thanks for all the help with this.


                    For some reason it runs the query 4 times to display the results in a h:dataTable, but hopefully that won't slow down the app too bad.