0 Replies Latest reply on Jul 14, 2009 1:13 AM by Arbi Sookazian

    dataTable var referencing JPQL query list

    Arbi Sookazian Master

      I have a projection JPQL native query as follows:

      equipmentProcessingList = entityManager.createNativeQuery("select AppSite.SiteName, "+
                            " er.AccountNumber, "+
                           " er.ServiceOccurance, "+
                           " er.ItemNumber, "+
                           " er.SerialNumber, "+
                            " eqType.ListValue "+
                           " from EquipmentRecoveryStatusChangeLog as erscl "+
                           " INNER JOIN EquipmentRecovery as er "+
                           " ON erscl.EquipmentRecoveryID =  er.EquipmentRecoveryID "+
                           " INNER JOIN ApplicationSite as AppSite "+
                           " ON AppSite.InternalSiteID = er.IcomsSiteID "+
                           " INNER JOIN RecoveredEquipmentManagement as rem "+
                           " ON AppSite.ApplicationSiteID = rem.ApplicationSiteID "+
                           " AND er.ItemNumber = rem.ICOMS_ITEM_NUMBER "+
                           " INNER JOIN (SELECT ListValue, ListValueID  FROM ListValue WHERE ListID = 4) as eqType "+
                           " ON rem.EquipmentType = eqType.ListValueID "+
                           " where erscl.IsCurrent = 1 "+
                           " AND erscl.EquipmentStatusID = :equipmentStatusId "+
                           " AND er.IcomsSiteID = :icomsSiteId "+
                           " AND er.CustomerStatusCode = :customerStatusCode ")
                           .setParameter("equipmentStatusId", equipmentStatusId)
                           .setParameter("icomsSiteId", icomsSiteId)
                           .setParameter("customerStatusCode", 'A')

      which is not returning any entities, just scalar values.  I get an array that I use as the values from the columns in the dataTable below.  Obviously, if the order of the columns returned in the resultList change that will break the dataTable presentation.  And also using array indices to refer to scalar values from a query is not understandable or easy to read, etc.

      What is the best way to handle this?

      I think you can do a select new{...} or similar in JPQL or HQL.  Is that correct, and then you get a class/DTO in the resultSet instead?  in the dataTable you'd essentially be calling getters to get the values for the outputText fields...

      <rich:scrollableDataTable id="equipmentProcessingDataTableId" 
                                                            rendered="#{equipmentProcessingList.getRowCount() > 0}">
                                   <rich:column sortable="false">
                                        <f:facet name="header"><h:outputText value="Site"/></f:facet>
                                         <h:outputText value="#{listVal[0]}"/>
                                    <rich:column sortable="false">
                                         <f:facet name="header"><h:outputText value="Account Number"/></f:facet>
                                         <h:outputText value="#{listVal[1]}"/>
                                    <rich:column sortable="false">
                                         <f:facet name="header"><h:outputText value="Occurrence"/></f:facet>
                                         <h:outputText value="#{listVal[2]}"/>
                                    <rich:column sortable="false">
                                         <f:facet name="header"><h:outputText value="Item Number"/></f:facet>
                                         <h:outputText value="#{listVal[3]}"/>
                                    <rich:column sortable="false">
                                         <f:facet name="header"><h:outputText value="Serial Number"/></f:facet>
                                         <h:outputText value="#{listVal[4]}"/>
                                    <rich:column sortable="false">
                                         <f:facet name="header"><h:outputText value="Equipment Type"/></f:facet>
                                         <h:outputText value="#{listVal[5]}"/>