34

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?

bpapa
  • 21,107
  • 23
  • 95
  • 147

2 Answers2

51

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.

vphilipnyc
  • 6,607
  • 6
  • 44
  • 70
Michael Pralow
  • 6,358
  • 2
  • 27
  • 43
9

Is current_date() a Hibernate function?

I would use sysdate instead. like this:

where alert.expiration > sysdate

Or to ignore time of day:

where alert.expiration > trunc(sysdate)
user1766169
  • 1,893
  • 3
  • 21
  • 41
Lost in Alabama
  • 1,573
  • 9
  • 15
  • 1
    Current_date and Current_timestamp are Oracle functions. Current_date = Sysdate but Oracle doesn't need () after a function call. –  Feb 05 '09 at 21:32
  • 8
    CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() are also Hibernate HQL Functions. ("Java Persistence With Hibernate") – Maarten van Leunen Mar 24 '12 at 22:55
  • just pass CURRENT_DATE() in your query like for example: "and date=CURRENT_DATE()"; – arn-arn Apr 14 '16 at 17:15