33

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?

Improve this question

2 Answers 2

Reset to default
50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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)
Improve this answer

3 Comments

Current_date and Current_timestamp are Oracle functions. Current_date = Sysdate but Oracle doesn't need () after a function call.
CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() are also Hibernate HQL Functions. ("Java Persistence With Hibernate")
just pass CURRENT_DATE() in your query like for example: "and date=CURRENT_DATE()";

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.