CODEWITHSUNDEEP
javamysqlsqlhibernate
Raghu
Raghu

Reputation: 1324

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

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.

Upvotes: 1

Views: 12695

Answers (7)

Doris Gammenthaler
Doris Gammenthaler

Reputation: 622

The answer to Raghu's question:

  1. change frmDate and toDate to Strings: String frmDate = "2014/01/01"; String toDate = "2014/09/01";

  2. 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}'

Upvotes: 0

MayurB
MayurB

Reputation: 3649

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));

Upvotes: 0

Eric Darchis
Eric Darchis

Reputation: 26747

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.

Upvotes: 1

Hardik Visa
Hardik Visa

Reputation: 323

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.

Upvotes: 0

Selva
Selva

Reputation: 1670

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

Upvotes: 0

Charvee Shah
Charvee Shah

Reputation: 720

Use 

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

Upvotes: 0

Arnold Galovics
Arnold Galovics

Reputation: 3416

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));

Upvotes: 0

Related Questions