spring HQL 查询两个日期之间的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21265134/
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
HQL to query records between two dates
提问by MChan
I am trying to query all customers records using HQL in my Spring/ Hibernate app that have DateAdded between Date1 and Date2 OR LastSeen between Date1 and Date2, so I've build this HQL query in the Repository/ DAO class:
我试图在我的 Spring/Hibernate 应用程序中使用 HQL 查询在 Date1 和 Date2 之间有 DateAdded 或在 Date1 和 Date2 之间有 LastSeen 的所有客户记录，所以我在 Repository/DAO 类中构建了这个 HQL 查询：
sessionfactory.getCurrentSession().createQuery("from Customer c where c.dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"' OR c.lastSeenDate BETWEEN '"+startDate+"' AND '"+endDate+"'").list();
I've debugged the app to check the startDate and endDate and found that they are sent as:
我调试了该应用程序以检查 startDate 和 endDate，发现它们被发送为：
startDate: Wed Jan 22 01:16:57 HKT 2014
开始日期：2014 年 1 月 22 日星期三 01:16:57 HKT
endDate: Wed Jan 29 01:16:57 HKT 2014
结束日期：2014 年 1 月 29 日星期三 01:16:57 HKT
In DB, I am 100% sure there is one record at least meeting this query, as this record DateAdded and LastSeen are as follows:
在 DB 中，我 100% 确定至少有一条记录满足此查询，因为此记录 DateAdded 和 LastSeen 如下：
2014-01-23 15:33:38
2014-01-23 15:33:38
2014-01-25 15:33:38
2014-01-25 15:33:38
So can someone please tell me what I am doing wrong / missing here?
那么有人可以告诉我我做错了什么/在这里遗漏了吗？
回答by Ashish Jagtap
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate);
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setParameter("stDate", frmDate)
.setParameter("edDate", enDate)
.list();
hope this will help!
希望这会有所帮助！
回答by Hans
This is an old post, but I figured it might help someone. Setting .setTimeStamp should do the trick.
这是一个旧帖子，但我认为它可能对某人有所帮助。设置 .setTimeStamp 应该可以解决问题。
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate);
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setTimestamp("stDate", frmDate)
.setTimestamp("edDate", enDate)
.list();
回答by Altin Vijay
SimpleDateFormat sf=new SimpleDateFormat("dd-MM-YYYY");
String fromDate=null;
String toDate=null;
fromDate=sf.format(startDate);
toDate=sf.format(endDate);
sessionfactory.getCurrentSession().createQuery("from Customer where dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"'");
回答by coding-dude.com
It's important to know that when using BETWEEN “setDate” will truncate the HQL date value passed as parameter and ignore the hours, minutes, seconds. This is very important to note especially if you have an HQL query checking between dates from different days because using “setDate” will interpret the date interval as between midnight of the specified dates.
重要的是要知道，当使用 BETWEEN “setDate” 时，将截断作为参数传递的 HQL 日期值并忽略小时、分钟、秒。这一点非常重要，特别是如果您在不同日期的日期之间进行 HQL 查询检查，因为使用“setDate”会将日期间隔解释为指定日期的午夜之间。
The solution is to use “setParameter” instead of “setDate” which causes the HQL date values to be interpreted as dates and times.
解决方案是使用“setParameter”而不是“setDate”，这会导致 HQL 日期值被解释为日期和时间。
here's a few examples in my site for those interested http://www.coding-dude.com/wp/java/hql-date-datetime-quick-tip/
这是我网站上的一些示例，供感兴趣的人使用http://www.coding-dude.com/wp/java/hql-date-datetime-quick-tip/
回答by Gui Alencar
You need to annotate with @JsonFormat the same way the date filters and date field as follows:
您需要以与日期过滤器和日期字段相同的方式使用@JsonFormat 进行注释，如下所示：
Class Filter {
...
@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date startDate;
}
Class YourObject {
...
@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date DateAdded;
}
The pattern and timezone should be adjusted to your region.
模式和时区应根据您所在的地区进行调整。
回答by MR AND
I had similar issue. When we use end date (Wed Jan 29), the system looks for Wed Jan 29 00:00:00, which is just like choosing Jan 28. In order to avoid such case, we can add one day to the end date. This also explains why we do not have issue with the start date.
我有类似的问题。当我们使用结束日期（Wed Jan 29）时，系统会查找Wed Jan 29 00:00:00，就像选择Jan 28一样。为了避免这种情况，我们可以在结束日期上加一天。这也解释了为什么我们对开始日期没有问题。
Surprisingly, this issue does not exist when we use hibernate criteria.
令人惊讶的是，当我们使用休眠标准时，这个问题并不存在。
回答by user9260527
SimpleDateFormat formatter = new SimpleDateFormat("MM-dd-yyyy");
Calendar c = Calendar.getInstance();
c.setTime(new Date()); // Now use today date.
c.add(Calendar.DATE, 90);
Date fromDate = null, toDate = null;
String fromDateStr = formatter.format(new Date());
String toDateStr = formatter.format(c.getTime());
try {
fromDate = formatter.parse(fromDateStr);
toDate = formatter.parse(toDateStr);
} catch (ParseException e) {
e.printStackTrace();
}
query.setParameter("stDate", fromDate);
query.setParameter("edDate", toDate);
回答by Mukesh S
Try something like this:
尝试这样的事情：
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date fromDate = df.parse(startDate);
Date toDate = df.parse(endDate);
Criteria criteria = sessionfactory.getCurrentSession().createCriteria(Customer.class)
.add(Restrictions.between("dateAdded", fromDate , toDate ));
List<Customer> listCustomer = criteria.list();
Hope this helps..!!
希望这可以帮助..！！
