CODEWITHSUNDEEP
javamysqldatetimedatesubtraction
user393964
user393964

Reputation:

JPQL time interval

What I'm trying to do is quite simple in sql but I can't get it to work with JPQL.

The problem is that my date formats don't match each other. In my database the date is stored like 2011-12-04 18:19:00

What I've tried is:

    Calendar cal = Calendar.getInstance();
    cal.add(Calendar.SECOND, -GlobalConfig.heartbeatInterval);
    Query query = JPA.em().createQuery("select uo from UserOnline uo where lastActive < " + cal.getTime());

But this is giving me this error: IllegalArgumentException occured : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: Dec near line 1, column 60 [select uo from models.UserOnline uo where lastActive < Sun Dec 04 19:22:17 CET 2011]

Any ideas?

Upvotes: 1

Views: 1691

Answers (1)

Todd
Todd

Reputation: 31710

When you do "QueryText" + cal.getTime(), you are telling Java to convert cal.getTime() to a String and append it to the query text. You can see it clearly in the exception, it provided the query it attempted...

select uo from models.UserOnline uo where lastActive < Sun Dec 04 19:22:17 CET 2011

See? 'Sun Dec 04 19:22:17 CET 2011' is just a bunch of text here, and Hibernate's query engine has no idea how to parse it.

I would change your query slightly to take an argument, which is the time...

Query query = JPA.em().createQuery("select uo from UserOnline uo where lastActive < :lastActive");
query.setParameter("lastActive", cal.getTime());

EDIT: Took advice from JB Nizet and changed this from numbered to named parameters.

Upvotes: 2

Related Questions