Named Query parameter in hour interval

Question

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!

What is the Java type of 'fetchTime' ? Also are not all positional param enclosed in escape sequence like apostrophe marks. But the SQL syntax requires the value to not be escaped. Valid SQL: DATE_SUB(NOW(), INTERVAL 1 HOUR) ... Invalid SQL DATE_SUB(NOW(), INTERVAL '1' HOUR) ... Build the SQL yourself using fetchTime as Java Long type and: "DATE_SUB(NOW(), INTERVAL " + Long.valueOf(fetchTime).toString() + " HOUR)" ... but be sure that such data is validated/sanity-checked/limited in allowed range (which a Long type will do by default). — Darryl Miles, Sep 20 '12 at 21:56
@DarrylMiles thank you for your tip. I tried exactly what you sayd, build the SQL String with Long.valueOf(fetchTime).toString() but I got unexpected token: 6.. But now I guess I'm closer to solve this one. Thank you! — pulu, Sep 21 '12 at 00:24

score 4 · Accepted Answer · answered Sep 21 '12 at 06:58

4

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.

answered Sep 21 '12 at 06:58

Johanna

5,223
1
21
38

Well, as I was about to start thinking about using java to calculate the time, your answer is welcome. It's a good suggestion and should be considered. Thanks a lot @Johanna. – pulu Sep 21 '12 at 12:45
2

Keep this in mind, System.currentTimeMillis() might return a different timestamp than the one stored in the database. – abbas Jul 11 '13 at 09:27
Good Answer, except a bit out-of-date: The `java.sql.Timestamp` class has been replaced by [`java.time.OffsetDateTime`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/OffsetDateTime.html) class. Example: `OffsetDateTime.now( ZoneOffset.UTC ).plusHours( 1 )` – Basil Bourque Apr 10 '19 at 00:14

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

It seems you have two choices or go native or use criteria

How to implement mysql date_sub() function in Hibernate 3.0

edited May 23 '17 at 12:13

Community

1
1

answered Sep 21 '12 at 11:53

Sérgio Abreu

177
2
15

Named Query parameter in hour interval

2 Answers2