Hibernate Criteria API: Unable to use sqlRestriction(..): ORA-00904: Invalid identifier-CodePudding

Does anyone know why the followng sqlRestriction doesn't work. I'm using the native Oracle trunc(..) function to trim the time portion of a DATE.

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Publication.class);

// Some other, non-SQL restrictions...

criteria.add(Restrictions.sqlRestriction("trunc(pubmedImportDate) >= ?", 
                                         todayMinus24Months, 
                                         org.hibernate.type.StandardBasicTypes.DATE));

Error: Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "PUBMEDIMPORTDATE": invalid identifier

The query is based on the domain class Publication which has this field,

public class Publication implements java.io.Serializable {
   //...
   private Date pubmedImportDate;
   //..
    public Date getPubmedImportDate() {
        return this.pubmedImportDate;
    }
    public void setPubmedImportDate(Date pubmedImportDate) {
        this.pubmedImportDate = pubmedImportDate;
    }
}

Do I need to prefix pubmedImportDate with some alias? I see that the pubmedImportDate never gets linked to Hibernate's alias this_.

I've tried both of these:

Criteria criteria = session.createCriteria(Publication.class);
Criteria criteria = session.createCriteria(Publication.class, "p"); // with p.pubmedImportDate

But the query always comes out as this:

from
    PUBLICATIONS_T this_ 
...
trunc(p.pubmedImportDate) >= ?

How do I link it up to this_?

CodePudding user response：

I found the solution: {alias}.column_name has to be used to link it up to this_:

criteria.add(Restrictions.sqlRestriction("trunc({alias}.pubmed_import_date) >= ?", 
                                         todayMinus24Months, 
                                         org.hibernate.type.StandardBasicTypes.DATE));

Also note it has to be the real DB column name (pubmed_import_date) not the class property (pubmedImportDate).