如何编写日期范围的between子句的hql查询？ [英] 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();}
} 

protected void doGet（HttpServletRequest请求，HttpServletResponse响应）抛出ServletException，IOException {
Session session = HibernateSession.getHibernateSession（）;

//开始交易
交易记录= session.beginTransaction（）;
String hql = null;
// ArrayList< HistoryLatitudeBean> vehicleHistoryList = new ArrayList< HistoryLatitudeBean>（）;
尝试{
DateFormat df = new SimpleDateFormat（yyyy / mm / dd）;
日期frmDate = df.parse（2014/01/01）;
日期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）;
列表< HistoryLatitudeBean> groupList =（List< HistoryLatitudeBean>）query.list（）; （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

这是我的bean类

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

在mysql命令行中执行相同的查询，我得到11行，但在java方法中，我没有得到行。

In Mysql db I am trying query as

在Mysql数据库中，我正在查询作为

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

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

我不知道如何通过hibernate传递日期。任何人都可以告诉我，我的上述方法有什么问题。它真的帮助我。

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.

推荐答案

我不记得为什么，但对于日期，Hibernate需要你使用这个结构： p>

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

我只在Calendar对象和Hibernate.CALENDAR中使用它，所以你可能不得不调整确切的键入。

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

这篇关于如何编写日期范围的between子句的hql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！