3 Replies Latest reply on Oct 17, 2008 4:38 AM by Andrew Fuqua

    Specify fields in query and use in datatable?

    Al Green Newbie

      Is it possible to specify fields in a query and use those same fields in a datatable?


      Bean:


      
           @Factory("acertRequests")
      
           public void requestList(){
      
                String requestQuery = "select t.acertNumber, t.acertTitle from Task t";
      
                acertRequests = (List<Task>) em.createQuery(requestQuery).getResultList();
      
           }
      
      



      View:


      
           <h:dataTable id="table1" value="#{acertRequests}" var="req">
      
                <h:column><h:outputText value="#{req.acertNumber}" /></h:column>
      
                <h:column><h:outputText value="#{req.acertTitle}" /></h:column>     
      
           </h:dataTable>
      
      



      I get the error "java.lang.NumberFormatException: For input string: "acertNumber" with the above example.


      Of course, everything works fine when using select t from Task t. I need to specify field names because I will need to get distinct acertNumber values from the db (select distinct t.acertNumber, t.acertTitle from Task t).


      Any ideas? I've tried just about everything in the value attribute of h:outputText without success. Not sure where to go next.

        • 1. Re: Specify fields in query and use in datatable?
          Fernando Montaño Expert

          What about:


          @Factory("acertRequests")
               public void requestList(){
                    String requestQuery = "select t.acertNumber, t.acertTitle from Task t";
                    acertRequests = (List<Object[]>) em.createQuery(requestQuery).getResultList();
               }
          



          and:



          <h:dataTable id="table1" value="#{acertRequests}" var="req">
                    <h:column><h:outputText value="#{req[0]}" /></h:column>
                    <h:column><h:outputText value="#{req[1]}" /></h:column>     
               </h:dataTable>
          

          • 2. Re: Specify fields in query and use in datatable?
            Al Green Newbie

            Yes, that worked! Thank you very much.

            • 3. Re: Specify fields in query and use in datatable?
              Andrew Fuqua Newbie
              The EL expressions in the columns of the dataTable, "#{req[0]}" and "#{req[1]}", leave something to be desired because they aren't very readable.  You have to know that req[0] corresponds to acertNumber and req[1] corresponds to acertTitle.  With many fields in the query that could get very unmanageable.  Is there a way to reference the fields of the query by name?  Maybe cast to something besides List<Object[]>, like List<NumAndTitle>?  Assuming NumAndTitle has properties named num and title, could you then do "#{req.num}" and "#{req.title}".  This is much more readable and scales well to queries with many fields.