java 休眠查询以在忽略时间戳的日期获取记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4013258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Query to fetch records on date ignoring timestamp
提问by Arun
I have a timestamp column tradedate in one of the DB(Oracle) tables. I am using hibernate as the persistence layer to fetch and store Data to DB. I have a requirement in which I need to query the DB on date. i.e From UI the user passes a date and I need to get the filtered data based on this date.
我在 DB(Oracle) 表之一中有一个时间戳列 tradedate。我使用 hibernate 作为持久层来获取数据并将其存储到 DB。我有一个要求，我需要按日期查询数据库。即从 UI 用户传递一个日期，我需要根据该日期获取过滤后的数据。
If the tradedate column only has the date part my query returns the correct records The issue arises when the tradedate column is populated with a timestamp value ie(date + time). Then those values are not returned by the query.
如果tradedate 列只有日期部分，我的查询会返回正确的记录 当tradedate 列填充时间戳值即（日期+ 时间）时，就会出现问题。然后这些值不会被查询返回。
eg:say there are 10 records for 23rd Oct 2010 in DB 5 of them have the timestamp entries and 5 dont
例如：说 2010 年 10 月 23 日在 DB 中有 10 条记录，其中 5 条有时间戳条目，5 条没有
So the query which i have returns me only the 5 rows which dont have timestamp.
所以我的查询只返回没有时间戳的 5 行。
Now i know that i need to extract date from the timestamp and then do the comparision so that i get all the records for aprticular date but i dont know how its done with Hibernate and using HQL
现在我知道我需要从时间戳中提取日期，然后进行比较，以便获得特定日期的所有记录，但我不知道它是如何使用 Hibernate 和使用 HQL 完成的
the query in Java/Hibernatethat i am using is as follows
我正在使用的Java/Hibernate中的查询如下
String hql = "select Clearing from POJO as POJO where POJO.tradeDate = :date"; Query query = getSession().createQuery(hql); query.setParameter("date", date);
String hql = "select Clearing from POJO as POJO where POJO.tradeDate = :date"; 查询查询 = getSession().createQuery(hql); query.setParameter("日期", 日期);
回答by Adeel Ansari
String hql = "from POJO as POJO where to_date(to_char(POJO.tradeDate, 'DD-MON-YY'), 'DD-MON-YY') = :date";
Query query = getSession().createQuery(hql);
query.setParameter("date", date);
[Edit]
[编辑]
Beware, the index, if there is any, on tradeDatewill not be used if you follow the above query. In case, there are some performance concerns, you can rather do it like this,
请注意，tradeDate如果您遵循上述查询，则不会使用索引（如果有）。如果有一些性能问题，您可以这样做，
String hql = "from POJO as POJO where POJO.tradeDate between :date and :ceilDate";
Query query = getSession().createQuery(hql);
// a date having timestamp part, 00:00:00.0, or missing completely
query.setParameter("date", date);
// a date having timestamp part, 23:59:59.999
query.setParameter("ceilDate", ceilDate);
