如何在具有Oracle数据库的HQL查询中使用当前日期？ [英] How do I use the current date in an HQL query with an Oracle database?

问题描述

 来自Alert alert 
其中alert.expiration> current_date（）
 order by alert.priority，alert.updated，alert.name 
  

像这样创建SQL -

  Hibernate：选择alert0_.ANNOUNCEMENTS_ID作为ANNOUNCE1_1_，alert0_.ANNOUNCEMENT 
 S_NAME作为ANNOUNCE2_1_，alert0_ .ANNOUNCEMENTS_PRIORITY与ANNOUNCE3_1_，alert0_。 
 ANNOUNCEMENTS_EXPIRATION as ANNOUNCE4_1_，alert0_.ANNOUNCEMENTS_UPDATE_DATE as A 
 NNOUNCE5_1_ from NYC311_ANNOUNCEMENTS alert0_ where（alert0_.ANNOUNCEMENTS_EXPIR 
 ATION> current_date（））order by alert0_.ANNOUNCEMENTS_PRIORITY，alert0_.ANNOUNC 
 EMENTS_UPDATE_DATE ，alert0_.ANNOUNCEMENTS_NAME 
  

当出现所有这些古怪的错误时，例如缺少右括号显然是完全平衡的括号。

为什么甲骨文在这方面感到震惊？有没有更好的方法来编写我的HQL查询？

解决方案

它不应该是 current_date ？

Hibernate会将其翻译成适当的方言。

我没有找到真正的Hibernate会翻译这个到那个参考文档，但是一般来说，表达式可以在用于Hibernate 4.3的HQL表达式。

然后是定义Java持久性查询语言（JPQL）表达式的Java Persistence API 2.0（JPA） for current_date ：
$ b

4.6.17.2.3日期时间函数functions_returning_datetime：= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP日期时间函数
返回数据库
服务器上当前日期，时间和时间戳的值。

I'm trying to write this query using Hibernate 3 and Oracle 10.

from Alert alert
where alert.expiration > current_date()
order by alert.priority, alert.updated, alert.name

It's creating SQL like this -

Hibernate: select alert0_.ANNOUNCEMENTS_ID as ANNOUNCE1_1_, alert0_.ANNOUNCEMENT
S_NAME as ANNOUNCE2_1_, alert0_.ANNOUNCEMENTS_PRIORITY as ANNOUNCE3_1_, alert0_.
ANNOUNCEMENTS_EXPIRATION as ANNOUNCE4_1_, alert0_.ANNOUNCEMENTS_UPDATE_DATE as A
NNOUNCE5_1_ from NYC311_ANNOUNCEMENTS alert0_ where (alert0_.ANNOUNCEMENTS_EXPIR
ATION>current_date()) order by  alert0_.ANNOUNCEMENTS_PRIORITY , alert0_.ANNOUNC
EMENTS_UPDATE_DATE , alert0_.ANNOUNCEMENTS_NAME

I'm getting all of these wacky errors like "missing right parenthesis" when there is apparently perfectly balanced parenthesis.

Why is Oracle freaking out at this? Is there a better way to write my HQL query?

解决方案

Shouldn't it be current_date?

Hibernate will translate it to the proper dialect.

I did not find a real "Hibernate will translate this to that" reference documentation, but the expression, in general, can be found in HQL Expressions for Hibernate 4.3.

Then there is the Java Persistence API 2.0 (JPA) specification which defines expressions for the Java Persistence query language (JPQL) and their meaning e.g. for current_date:

4.6.17.2.3 Datetime Functions functions_returning_datetime:= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP The datetime functions return the value of current date, time, and timestamp on the database server.

这篇关于如何在具有Oracle数据库的HQL查询中使用当前日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！