Order by date in getEjbql

[valatharv]

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;
}

[luxspes]

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;
}

[valatharv]

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;
}

[luxspes]

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?

[luxspes]

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.

[luxspes]

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;
}

[valatharv]

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;
}

[luxspes]

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;
//}

[valatharv]

THANKS A LOT FRANCISCO...

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

Thanks again.