8 Replies Latest reply on Oct 2, 2008 8:07 PM by Val Sw

    Order by date in getEjbql

    Val Sw Expert
      Order by clause works fine when it is sorted by Varchar data... but when I use order by date or any id it throws exception...

      return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.protXml"; //This works

      return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.dateCreated"; //Throws exception

      I have dateCreated in my entity...

      Log:
      ---------------
      SEVERE: Error Rendering View[/experimentTableIndexList.xhtml]
      javax.faces.FacesException: javax.el.ELException: /experimentTableIndexList.xhtml @21,81 rendered="#{empty experimentTableIndexList.resultList}": Error reading 'resultList' on type com.novartis.bdm.proteomics.session.ExperimentTableIndexList_$$_javassist_1
           at javax.faces.component.UIComponentBase.isRendered(UIComponentBase.java:393)
      ............
      Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: dateCreated of: com.novartis.bdm.proteomics.entity.ExperimentTableIndex [select experimentTableIndex from com.novartis.bdm.proteomics.entity.ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.dateCreated]
           at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:616)
           at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:95)

      -------------------------------
      @Name("experimentTableIndexList")
      public class ExperimentTableIndexList extends EntityQuery
      {
          @Override
          public String getEjbql()
          {
               return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.protXml"; 
          
              //return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.dateCreated";         
          }   
      }
      -------------------------------
      Entity :
      ------------
      @Entity(name = "ExperimentTableIndex")
      @Table(name = "EXPERIMENT_TABLE_INDEX")
      public class ExperimentTableIndex
          implements Equals, HashCode, ToString
      {
      protected String protXml;
      protected Date dateCreated;
      @Basic
      @Column(name = "PROTXML")
      public String getProtXml() {
           return protXml;
      }
      public void setProtXml(String value) {
           this.protXml = value;
      }

      @Transient
      public Date getDateCreated() {
             return dateCreated;
      }

      public void setDateCreated(Date value) {
             this.dateCreated = value;
      }

      @Basic
      @Column(name = "DATE_CREATED")
      @Temporal(TemporalType.DATE)
      public Date getDateCreatedElement() {
           return this.getDateCreated();
      }
      public void setDateCreatedElement(Date target) {
           this.dateCreated = target;
      }
        • 1. Re: Order by date in getEjbql
          Francisco Jose Peredo Noguez Master

          Val Sw wrote on Oct 02, 2008 17:10:


          Order by clause works fine when it is sorted by Varchar data... but when I use order by date or any id it throws exception...


          return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.protXml"; //This works
          



          return "select experimentTableIndex from ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.dateCreated"; //Throws exception<br/>
          



          Exception:

          Log:
          ---------------
          SEVERE: Error Rendering View[/experimentTableIndexList.xhtml]
          javax.faces.FacesException: javax.el.ELException: /experimentTableIndexList.xhtml @21,81 rendered="#{empty experimentTableIndexList.resultList}": Error reading 'resultList' on type com.novartis.bdm.proteomics.session.ExperimentTableIndexList_$$_javassist_1
           at javax.faces.component.UIComponentBase.isRendered(UIComponentBase.java:393)
          ............
          Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: dateCreated of: com.novartis.bdm.proteomics.entity.ExperimentTableIndex [select experimentTableIndex from com.novartis.bdm.proteomics.entity.ExperimentTableIndex experimentTableIndex ORDER BY experimentTableIndex.dateCreated]
           at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:616)
           at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:95)
          




          Basically it is saying that it could not resolve property: dateCreated


          I have dateCreated in my entity...





          Yes, but it is marked with the @Transient annotation. This annotation specifies that the property or field is not persistent, therefore it can not be used in a query:

          @Transient
          public Date getDateCreated() {
                 return dateCreated;
          }
          




          • 2. Re: Order by date in getEjbql
            Val Sw Expert
            Thanks for the Francisco.

            OK I commented transient, but when the page loads it gives the following error even if i am not using order by dateCreated in getEjbql()

            Entity deatils are also below.

            Xhtml:
            <s:decorate id="expNameDecoration" template="layout/edit.xhtml">
                    <ui:define name="label">Experiment Name</ui:define>                
                    <h:selectOneMenu value="#{qExpHome.instance.experimentTableIndex}">
                     <s:selectItems value="#{experimentTableIndexList.resultList}"
                            var="experimentTableIndex"
                              label="#{experimentTableIndex.experimentName}"
                     />         
                    <s:convertEntity/>
                    </h:selectOneMenu>
            </s:decorate>

            Log:
            ---------------------------------------
               select
                    study0_.HJID as HJID0_,
                    study0_.STUDYID as STUDYID0_,
                    study0_.STUDYNAME as STUDYNAME0_,
                    study0_.NUMBEROFEXPERIMENTS as NUMBEROF4_0_,
                    study0_.STUDYDATE as STUDYDATE0_,
                    study0_.LASTUPDATEDTIMESTAMP as LASTUPDA6_0_
                from
                    MET_STUDY study0_
            13:07:07,950 INFO  [STDOUT] Hibernate:
                select
                    experiment0_.EXP_TABLE_ID as EXP1_6_,
                    experiment0_.EXPERIMENT_NAME as EXPERIMENT2_6_,
                    experiment0_.EXPERIMENT_TABLE_NAME as EXPERIMENT3_6_,
                    experiment0_.PROTXML as PROTXML6_,
                    experiment0_.dateCreated as dateCrea5_6_,
                    experiment0_.DATE_CREATED as DATE6_6_
                from
                    EXPERIMENT_TABLE_INDEX experiment0_
                order by
                    experiment0_.PROTXML
            13:07:07,981 WARN  [JDBCExceptionReporter] SQL Error: 904, SQLState: 42000
            13:07:07,981 ERROR [JDBCExceptionReporter] ORA-00904: "EXPERIMENT0_"."DATECREATED": invalid identifier

            13:07:07,981 ERROR [STDERR] Oct 2, 2008 1:07:07 PM com.sun.facelets.FaceletViewHandler handleRenderException
            SEVERE: Error Rendering View[/study.xhtml]
            javax.faces.FacesException: javax.el.ELException: /study.xhtml @74,12 value="#{experimentTableIndexList.resultList}": Error reading 'resultList' on type com.novartis.bdm.proteomics.session.ExperimentTableIndexList_$$_javassist_8
                    at javax.faces.component.UISelectItems.getValue(UISelectItems.java:144)
            ---------------------------------------

            Entity:
            @Entity(name = "ExperimentTableIndex")
            public class ExperimentTableIndex implements Equals, HashCode, ToString
            {
            protected Date dateCreated;
            ............
            //@Transient
            public Date getDateCreated() {
                    return dateCreated;
            }

            public void setDateCreated(Date value) {
                    this.dateCreated = value;
            }

            @Basic
            @Column(name = "DATE_CREATED")
            @Temporal(TemporalType.DATE)
            public Date getDateCreatedElement() {
                    return this.getDateCreated();
            }

            public void setDateCreatedElement(Date target) {
                    this.dateCreated = target;
            }

            • 3. Re: Order by date in getEjbql
              Francisco Jose Peredo Noguez Master

              Val Sw wrote on Oct 02, 2008 19:15:


              13:07:07,981 ERROR [JDBCExceptionReporter] ORA-00904: "EXPERIMENT0_"."DATECREATED": invalid identifier


              ORA-00904 This means that the table EXPERIMENT0_ does not have a field DATECREATED. Did you check that?




              • 4. Re: Order by date in getEjbql
                Francisco Jose Peredo Noguez Master

                Your entity code is invalid you have defined a field dateCreated mapped to a member variable dateCreated.


                And a field DATE_CREATED mapped to a property dateCreatedElement that internally calls getDateCreated.


                Just that is wrong on so many levels..., and, if it somehow work, then if you want to sort by DATE_CREATED then you have to write order by dateCreatedElement.


                I really recommend you to buy a book like Java Persistence with Hibernate before continuing.

                • 5. Re: Order by date in getEjbql
                  Francisco Jose Peredo Noguez Master

                  The correct code should look like this:


                  @Entity(name = "ExperimentTableIndex")
                  public class ExperimentTableIndex implements Equals, HashCode, ToString
                  {
                  protected Date dateCreated;
                  ............
                  
                  @Basic
                  @Column(name = "DATE_CREATED")
                  @Temporal(TemporalType.DATE)
                  public Date getDateCreated() {
                          return dateCreated;
                  }
                  
                  public void setDateCreated(Date value) {
                          this.dateCreated = value;
                  }
                  

                  • 6. Re: Order by date in getEjbql
                    Val Sw Expert
                    Thanks Francisco.... I am picking up...

                    Do I need to change anything, should I generate equals & hashcode again.

                    Same error : Caused by: java.sql.SQLException: ORA-00904: "EXPERIMENT0_"."DATECREATEDELEMENT": invalid identifier

                    I changed to
                    @Basic
                    @Column(name = "DATE_CREATED")
                    @Temporal(TemporalType.DATE)
                    public Date getDateCreated() {
                        return dateCreated;
                    }

                    Commented annotations
                    //@Basic
                    //@Column(name = "DATE_CREATED")
                    //@Temporal(TemporalType.DATE)
                    public Date getDateCreatedElement() {
                         return this.getDateCreated();
                    }

                    public void setDateCreatedElement(Date target) {
                         this.dateCreated = target;
                    }


                    • 7. Re: Order by date in getEjbql
                      Francisco Jose Peredo Noguez Master

                      Again, correct code should look like this (again please buy and read Java Persistence with Hibernate before continuing, it will save you a lot of time) :



                      @Basic
                      @Column(name = "DATE_CREATED")
                      @Temporal(TemporalType.DATE)
                      public Date getDateCreated() {
                          return dateCreated;
                      }
                      
                      public void setDateCreated(Date value) {
                              this.dateCreated = value;
                      }
                      
                      //PLEASE DELETE ALL THE COMMENTED STUFF
                      //@Basic
                      //@Column(name = "DATE_CREATED")
                      //@Temporal(TemporalType.DATE)
                      //public Date getDateCreatedElement() {
                      // return this.getDateCreated();
                      //}
                      
                      //public void setDateCreatedElement(Date target) {
                      // this.dateCreated = target;
                      //}
                      

                      • 8. Re: Order by date in getEjbql
                        Val Sw Expert

                        THANKS A LOT FRANCISCO...

                        I deleted all the irrelevant code just before your post... it works fine..


                        Thanks again.