Solved

convert sql to JPA

Posted on 2012-03-22
3
2,517 Views
Last Modified: 2012-03-22
I had the below sql and it works well in my data admin..
but I am using this as a named query in eclipse JPA.

SQL:
select * from eventTable eT
where eT.logon_timestamp between (SELECT DATEADD(day, -1, GETDATE()))
and (SELECT DATEADD(day, 0, GETDATE()))

JPA:
@NamedQuery(name = "selectLatestLogonValues", query = "select eventTable from eventTable eT \r\nwhere  eT.logon_timestamp between (SELECT DATEADD(day, -1, GETDATE()))\r\nand (SELECT DATEADD(day, 0, GETDATE()))")})

I got the below error...

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'DATEADD' {originalText=DATEADD}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[IDENT] IdentNode: 'day' {originalText=day}
       +-[UNARY_MINUS] UnaryArithmeticNode: '-'
       |  \-[NUM_INT] LiteralNode: '1'
       \-[METHOD_CALL] MethodNode: '('
          +-[METHOD_NAME] IdentNode: 'GETDATE' {originalText=GETDATE}
          \-[EXPR_LIST] SqlNode: 'exprList'


I think the DATEADD(day, 0, GETDATE())) function is not available in JPA...
if not do we have an alternative to this in JPA...

all I am trying to do is... get the table entries who logged in last 24 hours..
0
Comment
Question by:shragi
  • 2
3 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 37753487
what about

@NamedQuery(name = "selectLatestLogonValues", query = "select * from eventTable eT where  eT.logon_timestamp between DATEADD(day, -1, GETDATE()) and DATEADD(day, 0, GETDATE())")
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 37753502
or simpler form

@NamedQuery(name = "selectLatestLogonValues", query = "select * from eventTable eT where  eT.logon_timestamp between GETDATE()-1 and GETDATE()")
0
 
LVL 7

Expert Comment

by:gudii9
ID: 37754298
Here is interesting explanation, links

Hibernate 3.0 will have a new parser for HQL which will make extending current DB function support feature set and adding arithmetic operations much easier. How much is completed above current support and when cannot currently be discussed since its not in stone as yet. The final is some time off but beta will start soon(ish).


https://forum.hibernate.org/viewtopic.php?f=1&t=931857&sid=890fcf751e8e3b2eecc8986b5b39b6b3

http://www.java2s.com/Questions_And_Answers/JPA/Database/hql.htm
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question