Hibernate Criteria API:无法使用SQL限制(..):ORA-00904:无效的标识符

moiiocjp  于 2022-11-14  发布在  其他
关注(0)|答案(1)|浏览(308)

有人知道为什么下面的sqlRestriction不工作吗?我正在使用本机Oracle trunc(..)函数来削减日期的时间部分。

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

错误:由:java.sql.SQL语法错误异常:ORA-00904:“PUBMEDIMPORTDATE”:无效的标识符

该查询基于具有该字段的域类Publication

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

我需要在pubmedImportDate前面加上一些别名吗?我看到pubmedImportDate从未链接到Hibernate的别名this_
这两个我都试过了:

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

但查询结果总是如下所示:

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

如何将其链接到this_

yruzcnhs

yruzcnhs1#

我找到了解决方案:必须使用{alias}.column_name将其链接到this_

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

还要注意,它必须是实际的DB列名(pubmed_import_date),而不是类属性(pubmedImportDate)。

相关问题