How to write a hql query for between clause for date range?

Question

I am writing a hql query which retrieve details of perticular vehicle between given dates, I have a method like this.

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Session session = HibernateSession.getHibernateSession();

    // Starting Transaction
    Transaction transaction = session.beginTransaction();
    String hql = null;
    //ArrayList<HistoryLatitudeBean> vehicleHistoryList= new ArrayList<HistoryLatitudeBean>();
      try {
          DateFormat df = new SimpleDateFormat("yyyy/mm/dd");
          Date frmDate= df.parse("2014/01/01");
          Date toDate=df.parse("2014/09/01");


          hql= "from HistoryLatitudeBean where vehicleno=:vehicleno and rdate BETWEEN :frmdate and :todate";
          Query query =session.createQuery(hql);
            query.setParameter("vehicleno", 12);
            query.setParameter("frmdate", frmDate);
            query.setParameter("todate", toDate);
            List<HistoryLatitudeBean> groupList = (List<HistoryLatitudeBean>)query.list();

             for(HistoryLatitudeBean arr : groupList){
                 System.out.println(arr.getLat());   
                }


             transaction.commit();
      }
      catch(Exception e){
          if (transaction!=null) transaction.rollback();
          e.printStackTrace();
      }
      finally{session.close();}
}

Here is my bean class

@Entity
@Table(name="hlatlng")
public class HistoryLatitudeBean {

@Id
@Column(name="vehicleno")
private int vehicleno;
@Column(name="lat")
private String lat;
@Column(name="lng")
private String lng;
@Column(name="status")
private String status;
@Column(name="rdate")
private Date rdate;
@Column(name="rtime")
private Date rtime;

//getter and setters

}

The same query I am executing in mysql command line, I am getting 11 rows , but in java method I am getting no rows.

In Mysql db I am trying query as

select * from hlatlng where vehicleno=12 and rdate BETWEEN '2014/01/01' and '2014/09/01'

I am not getting how to pass the date formate through hibernate. whats wrong in my above method can any one tell me . It realy helps me.

Enable show_sql in cfg file and check for the query generated by Hibernate — MayurB, Sep 09 '14 at 12:27

score 1 · Answer 1 · answered Sep 09 '14 at 07:33

1

I don't remember why but for dates, Hibernate requires you to use this construct:

query.setParameter("frmdate", frmDate, Hibernate.DATE);
query.setParameter("todate", toDate, Hibernate.DATE);

I am only using it with Calendar objects and with Hibernate.CALENDAR so you might have to adapt the exact type.

answered Sep 09 '14 at 07:33

Eric Darchis

24,537
4
28
49

I hard code the query like this `hql= "from HistoryLatitudeBean where vehicleno=:vehicleno and rdate BETWEEN '2014-1-1' and '2014-9-1'";` it return 11 rows but all are same, why is i? – Raghu Sep 09 '14 at 07:40

score 0 · Answer 2 · answered Sep 09 '14 at 06:57

0

The Date class doesn't store the formatting. Here:

        query.setParameter("frmdate", frmDate);
        query.setParameter("todate", toDate);

You have to use the SimpleDateFormat to format the date.

        query.setParameter("frmdate", df.format(frmDate));
        query.setParameter("todate", df.format(toDate));

answered Sep 09 '14 at 06:57

Arnold Galovics

3,246
3
22
33

`df.format(frmDate)` will change date object string right, but we have to pass date object to hibernat query right? – Raghu Sep 09 '14 at 07:00
Then use query.setTimestamp("frmdate", frmDate); – Arnold Galovics Sep 09 '14 at 07:02
Or you may check the setDate method as well. – Arnold Galovics Sep 09 '14 at 07:06

score 0 · Answer 3 · answered Sep 09 '14 at 07:00

0

Use

query.setTimestamp("fromdate",frmDate);
query.setTimestamp("todate",toDate);

answered Sep 09 '14 at 07:00

Charvee Shah

730
1
6
21

use query.setInteger for vehicleno and then try. – Charvee Shah Sep 09 '14 at 07:14
Are you sure your date object is in "2014/01/01" format in sql?? – Charvee Shah Sep 09 '14 at 07:20
and its type is date??that also you are sure? – Charvee Shah Sep 09 '14 at 07:24
Yes I am trying sql query for db as I mentioned above, then it is a date type right? – Raghu Sep 09 '14 at 07:27

score 0 · Answer 4 · answered Sep 09 '14 at 07:08

0

mysql stores date in yyyy-mm-dd format. for example 2014-09-09 so convert your date like this before querying.

answered Sep 09 '14 at 07:08

Selva

1,620
3
33
63

yes but for hql query we have to pass date object. . how is it for hql? – Raghu Sep 09 '14 at 07:19
yeah,you should construct the date object like (yyyy-MM-dd) this format and give this object to invoke a query. – Selva Sep 09 '14 at 07:21
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); Date dt=format.format(strdate); like this you should convert your string representation of date into date object – Selva Sep 09 '14 at 07:23
yes I have changed like this, `DateFormat df = new SimpleDateFormat("yyyy-mm-dd"); Date frmDate= df.parse("2014-01-01"); Date toDate=df.parse("2014-09-01");` but no use – Raghu Sep 09 '14 at 07:25
use format instead of parse and try again.refer http://stackoverflow.com/questions/12575990/calendar-date-to-yyyy-mm-dd-format-in-java – Selva Sep 09 '14 at 07:27

score 0 · Answer 5 · answered Sep 09 '14 at 07:24

0

you can use

 select * from hlatlng where vehicleno=12 and (rdate >= '2014/01/01' and rdate <='2014/09/01')

this type of query it may be helpful for you.

answered Sep 09 '14 at 07:24

Hardik Visa

323
6
23

score 0 · Answer 6 · answered Sep 09 '14 at 08:12

0

I will always prefer criteria over HQL

    criteria.add(Restrictions.ge("rdate ", sDate)); 
    criteria.add(Restrictions.lt("rdate ", eDate));`

or you can use

    criteria.add(Restrictions.between("rdate ", sDate, eDate));

answered Sep 09 '14 at 08:12

MayurB

3,609
2
21
36

Hi , I tried this , its giving exception as `org.hibernate.QueryException: could not resolve property: rdate of: com.abc.its.beans.HistoryLatitudeBean` – Raghu Sep 09 '14 at 09:46
1

Thank u, it solved. The problem is `vehicleno` is primary key but in table there is no primary key. So I create one more field for PK. Then it solved. – Raghu Sep 09 '14 at 12:31

score 0 · Answer 7 · answered Sep 24 '15 at 15:36

The answer to Raghu's question:

change frmDate and toDate to Strings: String frmDate = "2014/01/01"; String toDate = "2014/09/01";
format rdate in the query to the same format: to_char(rdate,'yyyy/mm/dd')

The key thing here is that HQL can take string date input for date comparison.

This works in Groovy:
to_char(rdate,'yyyy/mm/dd') between '${frmDate}' and '${toDate}'

How to write a hql query for between clause for date range?

7 Answers7

Linked