Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

convert sql to JPA

Posted on 2012-03-22
3
Medium Priority
?
2,961 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 59

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 59

Accepted Solution

by:
HainKurt earned 2000 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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

722 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