I need to write a query to get an object between a range of time, currently the query looks like this:
Timestamp from = ...
Timestamp to = ...
getHibernateTemplate().find("from " + Person.class.getName() + " ml where ml.lastModifiedOn>="+from.toString()+" and m1.lastModifiedOn<=" + to.toString());
However, this doesnot work for obvious reasons. How can I format the timestamp to be acceptable by the query.
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: 16 near line 1, column 123 [from Person ml where ml.lastModifiedOn>=2010-02-12 16:00:21.292 and m1.lastModifiedOn
You're missing single quotes in your current query. The following should work:
from Person ml where ml.lastModifiedOn
between '2010-02-12 16:00:21.292' and '2010-02-12 23:00:21.292'
Note that I don't know why you're not passing Date instances to the following query:
from Person ml where ml.lastModifiedOn between :from and :to
Are you using java.sql.Timestamp here? If yes, you shouldn't.
You can simply pass a long (from.getTime()) in the comparison, if it is represented as long in the DB.
Otherwise you can use these functiomns: second(...), minute(...), hour(...), day(...), month(...), and year(...)
How about something like this?
String sql = "from " + Person.class.getName() + " ml where ml.lastModifiedOn>= ? and m1.lastModifiedOn<= ?";
Date from = ...;
Date to = ...;
getHibernateTemplate().find(sql, new Object[] {from,to});
If you want to query for something between you can do the following:
public List findPerson() {
Date from = ...;
Date to = ...;
return entityManager.createQuery(
"SELECT p from Person p WHERE p.lastModifiedOn BETWEEN ?1 AND ?2")
.setParameter(1,from, TemporalType.DATE)
.setParameter(2,to, TemporalType.DATE).getResultList();
}
You might need to change TemporalType.DATE to whatever you are using
