如何在hibernate中使用between子句比较日期 [英] How to compare dates using between clause in hibernate

问题描述

我想要获取两个时间戳之间的记录。我想要这个使用hibernate。我尝试使用以下查询，但是没有得到任何输出。


HQL =From AddressInfo address where address.addressId ='+ newValues.get（pickupAddress）。date_format（'+ DateUtility.getTimeOfBeforeOneMinute（）+'，'％m /％d /％y％H：toString（）+'和address.updatedDateAndTime ％i：％s'）和date_format（'+ DateUtility.getCurrentDate（）+'，'％m /％d /％y％H：％i：％s'）;


这是从控制台组成的SQL查询：


选择作为addressinf0_.T_ADM_NPK_ADDRESS_ID T1_5_，addressinf0_.T_ADM_VNM_USER_ID如T2_5_，addressinf0_.T_ADM_FPK_USER_ASSIGNED_ID如T3_5_，addressinf0_.T_ADM_VNM_STREET_1如T4_5_，addressinf0_.T_ADM_VNM_STREET_2如T5_5_，addressinf0_.T_ADM_VNM_STREET_3如T6_5_，addressinf0_。 T_ADM_VNM_CITY如T7_5_，addressinf0_.T_ADM_VNM_STATE如T8_5_，addressinf0_.T_ADM_VNM_ZIPCODE如T9_5_，addressinf0_.T_ADM_DNM_LATITUDE如T10_5_，addressinf0_.T_ADM_DNM_LONGITUDE如T11_5_，addressinf0_.T_ADM_TNM_CREATE_DATETIME如T12_5_，addressinf0_.T_ADM_TNM_UPDATE_DATETIME如T13_5_从t_address_master addressinf0_其中addressinf0_.T_ADM_NPK_ADDRESS_ID = '19'和date_format（'Tue Oct 02 17:09:53 EDT 2012'，'％m /％d /％y％H：％i：％s'）和date_format（'Tue Oct 02 17:10）之间的（addressinf0_.T_ADM_TNM_UPDATE_DATETIME） ：53 EDT 2012'，'％m /％d /％y％H：％i：％s'））





p >

我的数据库有一个记录与T_ADM_TNM_UPDATE_DATETIME = 10/2/2012 5:10:40 PM

但结果集是无法获取此查询...我不知道我在哪里错了。

任何人都可以进一步指导我

解决方案

您应该使用HQL参数而不是连接。

以下示例：

  Map< String，Object> parameterNameAndValues = new HashMap< String，Object>（）; 
 
日期startDate; 
 Date endDate; 
 
 //将值分配给startDate和endDate 
 
 parameterNameAndValues.put（startDate，startDate）; 
 parameterNameAndValues.put（endDate，endDate）; 
 
 String hqlQuery =FROM EntityName WHERE fechaInicio BETWEEN：startDate AND：endDate; 
 
查询查询= this.sessionFactory.getCurrentSession（）。createQuery（hqlQuery）; 
 
 for（Entry< String，Object> e：parameterNameAndValues.entrySet（））{
 query.setParameter（e.getKey（），e.​​getValue（））; 
} 
 
 return query.list（）; 
  

这将绑定日期参数，hibernate将为您避免健全检查和错误进行必要的转换。记住，即使MySQL以该格式保存Date对象，其他数据库也可能不会破坏Hibernate的帮助。

I want to fetch records that fall between the two timestamp. I want this using hibernate. I tried it doing with the below query but I am not getting any output

HQL = "From AddressInfo address where address.addressId = '"+newValues.get("pickupAddress").toString()+"' and address.updatedDateAndTime between date_format('" + DateUtility.getTimeOfBeforeOneMinute() + "', '%m/%d/%y %H:%i:%s') and date_format('"+DateUtility.getCurrentDate()+"','%m/%d/%y %H:%i:%s')";

This is the SQL Query formed from the console :

select addressinf0_.T_ADM_NPK_ADDRESS_ID as T1_5_, addressinf0_.T_ADM_VNM_USER_ID as T2_5_, addressinf0_.T_ADM_FPK_USER_ASSIGNED_ID as T3_5_, addressinf0_.T_ADM_VNM_STREET_1 as T4_5_, addressinf0_.T_ADM_VNM_STREET_2 as T5_5_, addressinf0_.T_ADM_VNM_STREET_3 as T6_5_, addressinf0_.T_ADM_VNM_CITY as T7_5_, addressinf0_.T_ADM_VNM_STATE as T8_5_, addressinf0_.T_ADM_VNM_ZIPCODE as T9_5_, addressinf0_.T_ADM_DNM_LATITUDE as T10_5_, addressinf0_.T_ADM_DNM_LONGITUDE as T11_5_, addressinf0_.T_ADM_TNM_CREATE_DATETIME as T12_5_, addressinf0_.T_ADM_TNM_UPDATE_DATETIME as T13_5_ from t_address_master addressinf0_ where addressinf0_.T_ADM_NPK_ADDRESS_ID='19' and (addressinf0_.T_ADM_TNM_UPDATE_DATETIME between date_format('Tue Oct 02 17:09:53 EDT 2012', '%m/%d/%y %H:%i:%s') and date_format('Tue Oct 02 17:10:53 EDT 2012', '%m/%d/%y %H:%i:%s'))

My DB has a record with T_ADM_TNM_UPDATE_DATETIME = 10/2/2012 5:10:40 PM

But the resultset is not able to fetch this query...I don't know wwhere I am going wrong.

Can anyone please guide me further

解决方案

You should be using HQL parameters instead of concatenate.

Take this example:

Map<String, Object> parameterNameAndValues = new HashMap<String, Object>();

Date startDate;
Date endDate;

// Assign values to startDate and endDate

parameterNameAndValues.put("startDate", startDate);
parameterNameAndValues.put("endDate", endDate);

String hqlQuery = "FROM EntityName WHERE fechaInicio BETWEEN :startDate AND :endDate";

Query query = this.sessionFactory.getCurrentSession().createQuery(hqlQuery);

for (Entry<String, Object> e : parameterNameAndValues.entrySet()) {
    query.setParameter(e.getKey(), e.getValue());
}

return query.list();

This will bind the date parameters and hibernate will make the necessary convertions for you avoiding sanity checks and errors. Remember that even if MySQL saves Date objects in that format other databases may not and it will undermine Hibernate help.

这篇关于如何在hibernate中使用between子句比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！