2 Replies Latest reply on Nov 6, 2008 7:10 PM by Val Sw

    Seam issue (is it a bug?) with Entity query when adding order by clause

    Val Sw Expert
      I am facing issue with Entity query when adding order by clause. not sure I should change anything else or it is a bug.

      ver : jboss-4.2.3, jboss-seam-2.0.2.SP1

      ExperimentTableIndexList.java returns experiment details.
      Corresponding xhtml page (ExperimentTableIndexList.xhtml) works fine using default getEjbql without any order by clause
      i.e. I can search or sort rich:dataTable without any error.

      When I add order by clause in "getEjbql()" as below, and try to search or sort rich:dataTable on the basis of experiment name, it gives error as:
      "javax.el.ELException: /ExperimentTableIndexList.xhtml @50,73 rendered="#{empty experimentTableIndexList.resultList}": Error reading 'resultList' on type com.novartis.bdm.proteomics.session.ExperimentTableIndexList_$$_javassist_3" which is
      "<h:outputText value="The Experiment Table Index search returned no results."
                     rendered="#{empty experimentTableIndexList.resultList}"/>"


      ExperimentTableIndexList.java
      -----------------------------
      @Name("experimentTableIndexList")
      public class ExperimentTableIndexList extends EntityQuery {

           private static final String[] RESTRICTIONS = {
                     "lower(experimentTableIndex.experimentName) like concat('%',lower(#{experimentTableIndexList.experimentTableIndex.experimentName}),'%')",
                     "lower(experimentTableIndex.experimentTableName) like concat('%',lower(#{experimentTableIndexList.experimentTableIndex.experimentTableName}),'%')",
                     "lower(experimentTableIndex.dateCreated) like concat('%',lower(#{experimentTableIndexList.experimentTableIndex.dateCreated}),'%')",
                     "lower(experimentTableIndex.protXml) like concat('%',lower(#{experimentTableIndexList.experimentTableIndex.protXml}),'%')",};

           private ExperimentTableIndex experimentTableIndex = new ExperimentTableIndex();

           @Override
           public String getEjbql() {
                //return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex"; //THIS WORKS
                return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.experimentName";
           }

           /**@Override
           public Integer getMaxResults() {
                return 25;
           }**/

           public ExperimentTableIndex getExperimentTableIndex() {
                return experimentTableIndex;
           }

           @Override
           public List<String> getRestrictions() {
                return Arrays.asList(RESTRICTIONS);
           }

      }

      ExperimentTableList.xhtml
      -------------------------
      <!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
                                   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
      <ui:define name="body">
         
          <h:messages globalOnly="true" styleClass="message" id="globalMessages"/>   
          <h:form id="experimentTableIndexSearch" styleClass="edit">
         
              <rich:simpleTogglePanel label="ExperimentTableIndex search parameters" switchType="ajax">
             
                  <s:decorate template="layout/display.xhtml">
                      <ui:define name="label">Experiment Name</ui:define>
                      <h:inputText id="experimentName" value="#{experimentTableIndexList.experimentTableIndex.experimentName}"/>
                  </s:decorate>

                  <s:decorate template="layout/display.xhtml">
                      <ui:define name="label">Experiment Table Name</ui:define>
                      <h:inputText id="experimentTableName" value="#{experimentTableIndexList.experimentTableIndex.experimentTableName}"/>
                  </s:decorate>

                  <s:decorate template="layout/display.xhtml">
                      <ui:define name="label">ProtXml</ui:define>
                      <h:inputText id="protXml" value="#{experimentTableIndexList.experimentTableIndex.protXml}"/>
                  </s:decorate>

             
              </rich:simpleTogglePanel>
             
              <div class="actionButtons">
                  <h:commandButton id="search" value="Search" action="/ExperimentTableIndexList.xhtml"/>
              </div>
             
          </h:form>
         
          <rich:panel>
              <f:facet name="header">Experiment Table List</f:facet>
          <div class="results" id="experimentTableIndexList">

          <h:outputText value="The Experiment Table Index search returned no results."
                     rendered="#{empty experimentTableIndexList.resultList}"/>
                    
          <rich:dataTable id="experimentTableIndexList"
                      var="experimentTableIndex"
                    value="#{experimentTableIndexList.resultList}"
                 rendered="#{not empty experimentTableIndexList.resultList}">
              <h:column>
                  <f:facet name="header">
                      <s:link styleClass="columnHeader"
                                   value="Exp Table Id #{experimentTableIndexList.order=='expTableId asc' ? messages.down : ( experimentTableIndexList.order=='expTableId desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{experimentTableIndexList.order=='expTableId asc' ? 'expTableId desc' : 'expTableId asc'}"/>
                      </s:link>
                  </f:facet>
                  #{experimentTableIndex.expTableId}
              </h:column>
              <h:column>
                  <f:facet name="header">
                      <s:link styleClass="columnHeader"
                                   value="Experiment Name #{experimentTableIndexList.order=='experimentName asc' ? messages.down : ( experimentTableIndexList.order=='experimentName desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{experimentTableIndexList.order=='experimentName asc' ? 'experimentName desc' : 'experimentName asc'}"/>
                      </s:link>
                  </f:facet>
                  #{experimentTableIndex.experimentName}
              </h:column>
              <h:column>
                  <f:facet name="header">
                      <s:link styleClass="columnHeader"
                                   value="Experiment Table Name #{experimentTableIndexList.order=='experimentTableName asc' ? messages.down : ( experimentTableIndexList.order=='experimentTableName desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{experimentTableIndexList.order=='experimentTableName asc' ? 'experimentTableName desc' : 'experimentTableName asc'}"/>
                      </s:link>
                  </f:facet>
                  #{experimentTableIndex.experimentTableName}
              </h:column>
              <h:column>
                  <f:facet name="header">
                      <s:link styleClass="columnHeader"
                                   value="ProtXml #{experimentTableIndexList.order=='protXml asc' ? messages.down : ( experimentTableIndexList.order=='protXml desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{experimentTableIndexList.order=='protXml asc' ? 'protXml desc' : 'protXml asc'}"/>
                      </s:link>
                  </f:facet>
                  #{experimentTableIndex.protXml}
              </h:column>
              <h:column>
                  <f:facet name="header">
                      <s:link styleClass="columnHeader"
                                   value="Date Created #{experimentTableIndexList.order=='dateCreated asc' ? messages.down : ( experimentTableIndexList.order=='dateCreated desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{experimentTableIndexList.order=='dateCreated asc' ? 'dateCreated desc' : 'dateCreated asc'}"/>
                      </s:link>
                  </f:facet>
                  #{experimentTableIndex.dateCreated}
              </h:column>
              <!--<h:column>
                  <f:facet name="header">action</f:facet>
                  <s:link view="/#{empty from ? 'ExperimentTableIndex' : from}.xhtml"
                         value="Select"
                            id="experimentTableIndex">
                      <f:param name="experimentTableIndexExpTableId"
                              value="#{experimentTableIndex.expTableId}"/>
                  </s:link>
              </h:column>-->
          </rich:dataTable>

          </div>
          </rich:panel>
         
          <div class="tableControl">
           
              <s:link view="/ExperimentTableIndexList.xhtml"
                  rendered="#{experimentTableIndexList.previousExists}"
                     value="#{messages.left}#{messages.left} First Page"
                        id="firstPage">
                <f:param name="firstResult" value="0"/>
              </s:link>
             
              <s:link view="/ExperimentTableIndexList.xhtml"
                  rendered="#{experimentTableIndexList.previousExists}"
                     value="#{messages.left} Previous Page"
                        id="previousPage">
                  <f:param name="firstResult"
                          value="#{experimentTableIndexList.previousFirstResult}"/>
              </s:link>
             
              <s:link view="/ExperimentTableIndexList.xhtml"
                  rendered="#{experimentTableIndexList.nextExists}"
                     value="Next Page #{messages.right}"
                        id="nextPage">
                  <f:param name="firstResult"
                          value="#{experimentTableIndexList.nextFirstResult}"/>
              </s:link>
             
              <s:link view="/ExperimentTableIndexList.xhtml"
                  rendered="#{experimentTableIndexList.nextExists}"
                     value="Last Page #{messages.right}#{messages.right}"
                        id="lastPage">
                  <f:param name="firstResult"
                          value="#{experimentTableIndexList.lastFirstResult}"/>
              </s:link>
             
          </div>   
      </ui:define>

      </ui:composition>

        • 1. Re: Seam issue (is it a bug?) with Entity query when adding order by clause
          Val Sw Expert
          Here is stack trace, notice the query uses order by twice... how can this be handled....

          Caused by: javax.el.ELException: /ExperimentTableIndexList.xhtml @50,73 rendered="#{empty experimentTableIndexList.resultList}": Error reading 'resultList' on type com.ExperimentTableIndexList_$$_javassist_3
               at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:76)
               at javax.faces.component.UIComponentBase.isRendered(UIComponentBase.java:390)
               ... 55 more
          Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: order near line 1, column 156 [select experimentTableIndex from com.novartis.bdm.proteomics.entity.ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.experimentName order by experimentName asc]
               at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:616)
               at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:95)
               at org.jboss.seam.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:79)
               at org.jboss.seam.framework.EntityQuery.createQuery(EntityQuery.java:175)
               at org.jboss.seam.framework.EntityQuery.initResultList(EntityQuery.java:73)
               at org.jboss.seam.framework.EntityQuery.getResultList(EntityQuery.java:65)
               at sun.reflect.GeneratedMethodAccessor119.invoke(Unknown Source)
               at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
               at java.lang.reflect.Method.invoke(Unknown Source)
               at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
               at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
               at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
               at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:38)
               at org.jboss.seam.util.Work.workInTransaction(Work.java:41)
               at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:32)
               at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
               at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
               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:166)
               at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
               at com.ExperimentTableIndexList_$$_javassist_3.getResultList(ExperimentTableIndexList_$$_javassist_3.java)
               at sun.reflect.GeneratedMethodAccessor118.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:53
          • 2. Re: Seam issue (is it a bug?) with Entity query when adding order by clause
            Val Sw Expert

            Any suggestions on how to handled the above issue....