3

I need to know how to call a SQL function like HOUR() in a @NamedQuery in Java Persistence.
To make it simpler, In a table that has a starttime (TIMESTAMP) column, I need to get the rows that have the startime falling between CURRENT_TIMESTAMP and HOUR(CURRENT_TIMESTAMP)+1. I have spent a day on Google and still did not manage to find a answer.

The following query works when tried with JAVADB, however the same fails in @NamedQuery.

Java DB Instance:

    select e.EMPLOYEEID, e.FIRSTNAME, r.STARTTIME, r.ENDTIME from reservation r, employee e
where e.EMPLOYEEID = r.EMPLOYEEID AND HOUR(r.STARTTIME) = HOUR(CURRENT_TIMESTAMP)

@NamedQuery:

    @NamedQuery(name="reservation.getcurrent",query = "select e.EMPLOYEEID, e.FIRSTNAME,
r.STARTTIME, r.ENDTIME from reservation r, employee e where e.EMPLOYEEID = r.EMPLOYEEID
AND HOUR(r.STARTTIME) = HOUR(CURRENT_TIMESTAMP)")

ERROR: 

[2014-04-22T17:03:00.946+0530] [glassfish 4.0] [SEVERE] [] [javax.enterprise.system.core] [tid: _ThreadID=38 _ThreadName=admin-listener(5)] [timeMillis: 1398166380946] [levelValue: 1000] [[
  Exception while deploying the app [LyndaPortalReservation] : Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [LyndaPortalReservationPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [select e.EMPLOYEEID, e.FIRSTNAME, r.STARTTIME, r.ENDTIME from reservation r, employee e where e.EMPLOYEEID = r.EMPLOYEEID AND HOUR(r.STARTTIME) = HOUR(CURRENT_TIMESTAMP)]. 
[126, 169] The right expression is not a valid expression.]]
Alex
  • 11,451
  • 6
  • 37
  • 52
Charan Chakravarthi
  • 858
  • 8
  • 19

3 Answers3

3

For clarity, I have added the exact syntax for the declaring a function in JPQL. @Neil was correct but the use of double quote for FUNCTION Name did not work. The syntax for FUNCTION name was FUNC('funcName', args). We need to use single quotes. Hence for other users looking for similar answers. Here is the exact syntax.

@NamedQuery(name="reservation.getall",query = "SELECT e.employeeid, e.firstname, r.starttime, r.endtime from reservation r, employee e where e.employeeid = r.employeeid AND FUNC('HOUR', r.starttime) = FUNC('HOUR', CURRENT_TIMESTAMP)")

This helped resolve my original problem of using functions in HQL.

Charan Chakravarthi
  • 858
  • 8
  • 19
  • which is obviously not standard JPQL (you won't find 'FUNC' anywhere in it), and FUNCTION *is* standard JPQL. Seems your JPA provider doesn't support the standard yet – Neil Stockton Apr 29 '14 at 04:32
0

JPA 2.1 allows use of "FUNCTION(funcName, args)" in a JPQL query string. Use that and set the funcName as "HOUR" (or whatever it is for the DB in question).

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • Tried the below in various forms and I get ERROR: The Function Name must be specified, The statefield e.EMPLOYEEID cannot be resolved to a valid type. The same for FIRSTNAME, STARTTIME, ENDTIME `@NamedQuery(name="reservation.getcurrent",query = "select e.EMPLOYEEID, e.FIRSTNAME, r.STARTTIME, r.ENDTIME from reservation r, employee e where e.EMPLOYEEID = r.EMPLOYEEID AND FUNCTION(HOUR, r.STARTTIME) = FUNCTION(HOUR, CURRENT_TIMESTAMP)")` – Charan Chakravarthi Apr 22 '14 at 15:56
  • The function name has to be in quotes since it is a String for starters. – Neil Stockton Apr 22 '14 at 17:16
  • When I add quotes, I get ") expected" error. Whatever that was happening previously was a warning and was causing an issue when the application is run. Now, its an error. `@NamedQuery(name="reservation.getcurrent",query = "select e.EMPLOYEEID, e.FIRSTNAME, r.STARTTIME, r.ENDTIME from reservation r, employee e where e.EMPLOYEEID = r.EMPLOYEEID AND "FUNCTION(HOUR, r.STARTTIME)" = "FUNCTION(HOUR, CURRENT_TIMESTAMP)"")` Could you let me know if I am missing something. – Charan Chakravarthi Apr 23 '14 at 06:08
  • Huh. I said the function _name_ needs quotes ... FUNCTION(\"HOUR\", args) – Neil Stockton Apr 23 '14 at 06:32
  • Neil, My Bad. Yes, I tried changing the syntax and I believe its a single quote for the function name and and I still get the below error on runtime. – Charan Chakravarthi Apr 23 '14 at 12:40
  • Latest Log : `Internal Exception: Exception [EclipseLink-0] org.eclipse.persistence.exceptions.JPQLException Exception Description: Problem compiling [SELECT e.employeeid, e.firstname, r.starttime, r.endtime from reservation r, employee e where e.employeeid = r.employeeid AND FUNC('HOUR', r.starttime) = FUNC('HOUR', CURRENT_TIMESTAMP)]. [7, 19] The state field path 'e.employeeid' cannot be resolved to a valid type. [21, 32] The state field path 'e.firstname' cannot be resolved to a valid type. [77, 85] The abstract schema type 'employee' is unknown.` – Charan Chakravarthi Apr 23 '14 at 12:51
  • so looks like your JPA provider (EclipseLink) has a problem; there are others. Maybe it is objecting to your seemingly random use of CAPITALS in field names; any JPQL has to be of the field/property name NOT the column name. – Neil Stockton Apr 26 '14 at 09:05
  • I tried using @NamedNativeQuery it works without any runtime errors however the query does not return anything. – Charan Chakravarthi Apr 27 '14 at 14:19
  • but then that's got nothing to do with my answer, and your request to use JPQL – Neil Stockton Apr 27 '14 at 17:42
-4

Use

formatter = new SimpleDateFormat("HH");
hour = formatter.format(CURRENT_TIMESTAMP);
Tabish
  • 1,592
  • 16
  • 13
  • 1
    I am looking for the SQL Query that would satisfy the @NamedQuery syntax. I need to fetch the data from Java DB. your suggestion would be specific to formatting the date right? – Charan Chakravarthi Apr 22 '14 at 13:40