Creating datatable with count and group by data

[daxxy]

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

[asookazian]

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>

[daxxy]

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

??

[asookazian]

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.

[daxxy]

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