I'm currently having trouble to execute this query using HQL.
String queryString = "SELECT o FROM Orders o WHERE o.orderMade >= DATE_SUB(NOW(), INTERVAL :fetchTime HOUR)";
this will fetch the last orders from the last ":fetchTime" hours.
The problem is, how to set this parameter using HQL ?
I tryed
.em.createQuery(queryString).setParameter("fetchTime", fetchTime).getResultList();
but it results in a hibernate error.
I also tryed to use "?" instead a named parameter, but without success.
Even writing a number into the queryString and not setting any parameters, I still getting this error:
unexpected token: 6
I know this is referred to the number 6 I've used instead the fetchTime.
The only way I got this working was by using this as a NativeQuery, but this one returns a list of detached Objects and I really wanted to use a NamedQuery.
Thank you for your time!
Time calculations in SQL are always difficult, because the time functions are different for every database (MySQL, Oracle, ...). The easiest solution, which I would recommend to you, is to do the time calculation in Java, for example like this:
long fetchTime = ....; /* fetch time in hours */
String queryString = "FROM Orders o WHERE o.orderMade >= :orderTime";
Query query = session.createQuery(queryString);
Timestamp orderTime = new Timestamp(System.currentTimeMillis() - fetchTime * 60L * 60L * 1000L);
query.setTimestamp("orderTime", orderTime);
@SuppressWarnings("unchecked")
List<Orders> orders = query.list();
Remark: 60L * 60L * 1000L is one hour. don't forget the L to force long calculations.
Then you can be sure your HQL will work on every database.