4 Replies Latest reply on Aug 3, 2009 4:35 PM by Tanya Ruttenberg

    Creating datatable with count and group by data

    Tanya Ruttenberg Expert
      I want to create a webpage with the following table:

      2612            478
      3745             226
      3825            1807
      7206VXR           70
      7609               8
      7613               2
      AS5350             2
      MSFC2            107
      WS-C2948         114
      WS-C2980           1
      WS-C2980G-A      187
      WS-C3550-12G     238
      WS-C3550-24      371

      this is a result of the following SQL query:

      select dev_platform, count(*) from devices
      group by dev_platform

      I am unsure how to create this table via Seam.  I have a Devices entity with devPlatform attribute.  Do I also need a property for the count?  Or will that come for free with the EntityQuery?  I created an EntityQuery as follows:


      @Name("platformBreakdownList")
      public class PlatformBreakdownList extends EntityQuery<Devices> {

        private static final String EJBQL = "select distinct
              devices.devPlatform from Devices devices";

        private Devices devices = new Devices();

        public PlatformBreakdownList() {
          setEjbql(EJBQL);
          setGroupBy("Devices.devPlatform");
        }

        public Devices getDevices() {
          return devices;
        }

      }


      And my view PlatformBreakdown.xhtml:

      <rich:dataTable id="breakdownList" var="_platform"
          value="#{platformBreakdownList.resultCount}">
           <h:outputText value="#{_platform.devPlatform}" />
      </rich:dataTable>


      which comes up completely empty.  And anyhow, I can't figure out how to output the count resulting from the group by.

      Help?

      TDR
        • 1. Re: Creating datatable with count and group by data
          Arbi Sookazian Master

          The following JPAQL query is a projection (scalar values in result set, not entities) and will return an Object[].  You can verify this by observing the List<Object[]> variable below in your debugger.


          List resultCount = entityManager.createQuery("select dev_platform, count(*) from devices
          group by dev_platform").getResultList();



          so in your facelet:


          <!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
                                "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
          
          <ui:composition xmlns="http://www.w3.org/1999/xhtml"
               xmlns:ui="http://java.sun.com/jsf/facelets"
               xmlns:h="http://java.sun.com/jsf/html"
               xmlns:f="http://java.sun.com/jsf/core"
               xmlns:a4j="http://richfaces.org/a4j"
               xmlns:rich="http://richfaces.org/rich"
               xmlns:s="http://jboss.com/products/seam/taglib"
               template="/templates/normal.xhtml">
               
               <ui:define name="body">
                    <h:form>
                         <h:commandButton value="startup" action="#{insertAppSite.startup}"/>
                         <br/>
                         <h:commandButton value="end" action="#{insertAppSite.end}"/>
                    </h:form>
                    
                    Rowcount: <h:outputText value="#{resultCount.getRowCount()}"/>
                    <rich:dataTable id="breakdownList" var="_platform"
                        value="#{resultCount}">
                        <rich:column>
                              <h:outputText value="#{_platform[0]}" />
                        </rich:column>
                        <rich:column>
                             <h:outputText value="#{_platform[1]}" />
                        </rich:column>
                    </rich:dataTable>
                    
               </ui:define>
          
               
          </ui:composition>



          backing bean:


          @Name("insertAppSite")
          @Scope(ScopeType.CONVERSATION)
          public class TestStartupInsertApplicationSite {
          
               @In
               private EntityManager entityManager;
               
               @Logger
               private Log log;
               
               @DataModel
               private List<Object[]> resultCount;
                    
               @Begin(join=true)
               @Transactional
               public void startup(){
                    
                    resultCount = entityManager.createQuery("select e.modelNumber, count(*) from Equipment e group by e.modelNumber").getResultList();
                    
                    log.info("resultCount.size() = "+resultCount.size());
               
               }
               
               @End(beforeRedirect=true)
               public void end(){
                    //no op
               }
               
               @Destroy
               public void destroy(){
                    log.info("in "+this.getClass().getName()+".destroy()");
               }
          }



          I noticed that the cid is not postpended to the URL on postback if you do not add something like the following in your pages.xml:


          <page view-id="/TestStartConversation.xhtml">
                   <navigation from-action="#{insertAppSite.startup}">
                        <redirect view-id="/TestStartConversation.xhtml"/>                          
                  </navigation>
              </page>

          • 2. Re: Creating datatable with count and group by data
            Tanya Ruttenberg Expert

            Thank you! I think I am almost there, but am having a trouble with injecting the EntityManager. Here is the code




            @Scope(ScopeType.CONVERSATION)
            @Name("platformBreakdownList")
            public class PlatformBreakdownList {
                 
                 @In
                 private EntityManager em;
                 
                 @DataModel
                 private List<Object[]> platforms;
            
                 public List<Object[]> getPlatforms() {
                      platforms = em.createQuery("select d.devPlatform, count(*) from Devices d" +
                      "group by d.devPlatform").getResultList();
                      return platforms;
                 }
            }




            And I am getting this problem (excuse the verbosity):




            javax.faces.FacesException: javax.el.ELException: /PlatformBreakdown.xhtml @16,52 value="#{platformBreakdownList.platforms}": Error reading 'platforms' on type dne.nmt.ond.action.PlatformBreakdownList_$$_javassist_seam_2
                 at javax.faces.component.UIData.getValue(UIData.java:612)
                 at org.ajax4jsf.component.UIDataAdaptor.getValue(UIDataAdaptor.java:1644)
                 at org.ajax4jsf.component.SequenceDataAdaptor.getDataModel(SequenceDataAdaptor.java:65)
                 at org.ajax4jsf.component.SequenceDataAdaptor.createDataModel(SequenceDataAdaptor.java:59)
                 at org.richfaces.component.UIDataTable.createDataModel(UIDataTable.java:125)
                 at org.ajax4jsf.component.UIDataAdaptor.getExtendedDataModel(UIDataAdaptor.java:621)
                 at org.ajax4jsf.component.UIDataAdaptor.setRowKey(UIDataAdaptor.java:339)
                 at org.richfaces.renderkit.AbstractTableRenderer.encodeTableStructure(AbstractTableRenderer.java:124)
                 at org.richfaces.renderkit.html.DataTableRenderer.doEncodeBegin(DataTableRenderer.java:206)
                 at org.richfaces.renderkit.html.DataTableRenderer.doEncodeBegin(DataTableRenderer.java:194)
                 at org.ajax4jsf.renderkit.RendererBase.encodeBegin(RendererBase.java:101)
                 at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:813)
                 at javax.faces.component.UIData.encodeBegin(UIData.java:962)
                 at org.ajax4jsf.component.UIDataAdaptor.encodeBegin(UIDataAdaptor.java:1220)
                 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:928)
                 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:933)
                 at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
                 at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
                 at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
                 at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
                 at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
                 at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
                 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
                 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
                 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
                 at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:40)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:178)
                 at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
                 at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:390)
                 at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:517)
                 at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:60)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.web.HotDeployFilter.doFilter(HotDeployFilter.java:53)
                 at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
                 at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
                 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                 at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
                 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
                 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
                 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
                 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
                 at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
                 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
                 at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
                 at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
                 at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
                 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
                 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
                 at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
                 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
                 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
                 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
                 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
                 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
                 at java.lang.Thread.run(Unknown Source)
            Caused by: javax.el.ELException: /PlatformBreakdown.xhtml @16,52 value="#{platformBreakdownList.platforms}": Error reading 'platforms' on type dne.nmt.ond.action.PlatformBreakdownList_$$_javassist_seam_2
                 at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:76)
                 at javax.faces.component.UIData.getValue(UIData.java:609)
                 ... 65 more
            Caused by: org.jboss.seam.RequiredException: @In attribute requires non-null value: platformBreakdownList.em
                 at org.jboss.seam.Component.getValueToInject(Component.java:2335)
                 at org.jboss.seam.Component.injectAttributes(Component.java:1736)
                 at org.jboss.seam.Component.inject(Component.java:1554)
                 at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:61)
                 at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
                 at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
                 at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
                 at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
                 at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
                 at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
                 at dne.nmt.ond.action.PlatformBreakdownList_$$_javassist_seam_2.getPlatforms(PlatformBreakdownList_$$_javassist_seam_2.java)
                 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
                 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
                 at java.lang.reflect.Method.invoke(Unknown Source)
                 at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
                 at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:54)
                 at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
                 at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
                 at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
                 at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
                 at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
                 ... 66 more
            




            ??

            • 3. Re: Creating datatable with count and group by data
              Arbi Sookazian Master

              components.xml:


              <persistence:managed-persistence-context name="entityManager"
                                                   auto-create="true"
                                    persistence-unit-jndi-name="java:/boBETSEntityManagerFactory"/> 



              do you have the auto-create set to true in yours?


              don't do this:


              @DataModel
                   private List<Object[]> platforms;
              
                   public List<Object[]> getPlatforms() {
                        platforms = em.createQuery("select d.devPlatform, count(*) from Devices d" +
                        "group by d.devPlatform").getResultList();
                        return platforms;
                   }
              



              if your business method returns an Object, you don't need @DataModel.  Either remove the @DataModel annotation or use factory component pattern (which fires getPlatforms() when platforms is null):


              @DataModel
                   private List<Object[]> platforms;
              
                      @Factory("platforms")         
                   public void getPlatforms() {
                        platforms = em.createQuery("select d.devPlatform, count(*) from Devices d" +
                        "group by d.devPlatform").getResultList();          
                   }
              



              And remember that with @DataModel, in your case the List will be wrapped and outjected as a ListDataModel which is useful to be used in conjunction with @DataModelSelection or @DataModelSelectionIndex for injection when user clicks a link/button on a row in the dataTable.

              • 4. Re: Creating datatable with count and group by data
                Tanya Ruttenberg Expert

                The error was resolved when I changed the name of the entity manager in my class to match the name of the entity manager in the components.xml file.


                from components.xml:




                <persistence:managed-persistence-context
                  auto-create="true" entity-manager-factory="@seamEmfRef@"  name="entityManager"
                persistence-unit-jndi-name="@puJndiName@" />




                backing bean:




                @Scope(ScopeType.CONVERSATION)
                @Name("platformBreakdownList")
                public class PlatformBreakdownList {
                     
                     @In(create=true)
                     private EntityManager entityManager;
                     
                     @DataModel
                     private List<Object[]> platforms;
                
                     @Factory("platforms")
                     public List<Object[]> getPlatforms() {
                          List<Object[]> resultList = entityManager.createQuery("select d.devPlatform, count(*) from Devices d " +
                          "group by d.devPlatform").getResultList();
                          platforms = resultList;
                          return platforms;
                     }
                }




                Thank you Arbi!


                Next task, make it possible to select a platform and link to the list of devices which match the platform.  You'll probably be hearing from me again!


                TDR