HQL to query records between two dates

spring hibernate hql
MChan picture MChan · Jan 21, 2014 · Viewed 81.2k times · Source

I am trying to query all customers records using HQL in my Spring/ Hibernate app that have DateAdded between Date1 and Date2 OR LastSeen between Date1 and Date2, so I've build this HQL query in the Repository/ DAO class:

sessionfactory.getCurrentSession().createQuery("from Customer c where c.dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"' OR c.lastSeenDate BETWEEN  '"+startDate+"' AND '"+endDate+"'").list();

I've debugged the app to check the startDate and endDate and found that they are sent as:

startDate: Wed Jan 22 01:16:57 HKT 2014

endDate: Wed Jan 29 01:16:57 HKT 2014

In DB, I am 100% sure there is one record at least meeting this query, as this record DateAdded and LastSeen are as follows:

2014-01-23 15:33:38

2014-01-25 15:33:38

So can someone please tell me what I am doing wrong / missing here?

Answer

Ashish Jagtap picture Ashish Jagtap · Jan 22, 2014 
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate);
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setParameter("stDate", frmDate)
.setParameter("edDate", enDate)
.list();

hope this will help!

Related questions

JpaRepository Not supported for DML operations [delete query]

I have written a query to delete some objects in my interface extending JPaRepository, but when I execute the query it throws an exception! Can anyone explain it for me? Query: public interface LimitRepository extends JpaRepository<CLimit, Long> { @…

Read More
Hibernate HQL Count Distinct not working?

I have the following classes: class User { hasMany = [ratings: Rating] } class Item { hasMany = [ratings: Rating] } class Rating { belongsTo = [user: User, item: Item] } I want to count the distinct users that rated on an item. The following does not work: select …

Read More
Using Hibernate query : colon gets treated as parameter / escaping colon

return sessionFactory.getCurrentSession(). createQuery("FROM Weather WHERE city_id = :id AND date " + "BETWEEN now()::date AND now()::date + (:days - 1)"). setInteger("id", city_id).setString("days", days).list(); getting error: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: : How can I …

Read More