Java：JPQL 日期函数将时间段添加到另一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2856386/
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
Java: JPQL date function to add a time period to another date
提问by bguiz
SELECT x FROM SomeClass
WHERE x.dateAtt BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 MONTH)
In the above JPQL statement, SomeClass has a memebr dateAttr, which is a java.util.Dateand has a @Temporal(javax.persistence.TemporalType.DATE)annotation.
在上面的 JPQL 语句中， SomeClass 有一个 memebr dateAttr，它是一个java.util.Date并且有一个@Temporal(javax.persistence.TemporalType.DATE)注释。
I need a way to do the (CURRENT_DATE + 1 MONTH)bit - it is obviously wrong in its current state - but cannot find the doc with the date function for JPQL.
我需要一种方法来做这(CURRENT_DATE + 1 MONTH)件事 - 在当前状态下显然是错误的 - 但找不到带有 JPQL 日期函数的文档。
Can anyone point me in the direction of a doc that documents JPQL date functions(and also how to do this particular query)?
任何人都可以向我指出记录 JPQL 日期函数的文档的方向（以及如何执行此特定查询）？
采纳答案by Shervin Asgari
If you have a date object that is + 1 month already you could do something like this:
如果您的日期对象已经是 + 1 个月，您可以执行以下操作：
public List findEmployees(Date endDate) {
return entityManager.createQuery(
"SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,endDate, TemporalType.DATE).getResultList();
}
This however, requires that the dates be valid before hand.
但是，这要求日期事先有效。
UPDATE
更新
If you always want the next month, you can use JodaTimewhich has a great and easy api. You could then modify your query like this:
如果你总是想要下个月，你可以使用JodaTime，它有一个伟大而简单的 api。然后，您可以像这样修改您的查询：
//Get next month
DateTime dt = new DateTime();
entityManager.createQuery(
"SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,dt.plusMonths(1).toDate(), TemporalType.DATE).getResultList();
回答by Sean Patrick Floyd
Or use commons-lang instead of jodatime:
或者使用 commons-lang 而不是 jodatime：
entityManager.createQuery(
"SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2"
)
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,DateUtils.addMonths(new Date(), 1), TemporalType.DATE)
.getResultList();
But I know this is not what you are asking and I'm pretty sure it can't be done in JPQL alone, you will either have to pass a parameter or use a native named query
但我知道这不是你要问的，我很确定它不能单独在 JPQL 中完成，你要么必须传递参数，要么使用本机命名查询
回答by Pascal Thivent
Standard JPQL doesn't support such operations on dates. You will have to either use a native query or to do the computation on the Java side.
标准 JPQL 不支持对日期进行此类操作。您将不得不使用本机查询或在 Java 端进行计算。
回答by Hazim
Dears,
亲爱的，
i'm using spring data, the entity save the createdDate as datetime, and in the repository like this:
我正在使用 spring 数据，实体将 createdDate 保存为日期时间，并在存储库中如下所示：
@Query(value="SELECT t FROM MyEntity t WHERE t.createdDate Between ?1 and ?2")
public List<MyEntity> findAllBetweenDates(Calendar from, Calendar to);
so i can't use :
所以我不能使用：
setParameter(1,new Date(), TemporalType.DATE
in the backend bean i use the following:
在后端 bean 中，我使用以下内容：
//to set zero of hours,minutes,seconds and milliseconds
fromCalendar.set(java.util.Calendar.HOUR, 0);
fromCalendar.set(java.util.Calendar.MINUTE, 0);
fromCalendar.set(java.util.Calendar.SECOND, 0);
fromCalendar.set(java.util.Calendar.MILLISECOND, 0);
toCalendar.set(java.util.Calendar.HOUR, 0);
toCalendar.set(java.util.Calendar.MINUTE, 0);
toCalendar.set(java.util.Calendar.SECOND, 0);
toCalendar.set(java.util.Calendar.MILLISECOND, 0);
// add 1 days and decrease 1 millisecond
toCalendar.add(java.util.Calendar.DAY_OF_MONTH, 1);
toCalendar.add(java.util.Calendar.MILLISECOND, -1);
allEntities = myEntityRepository.findAllBetweenDates(fromCalendar, toCalendar);
}
and it's working fine.
它工作正常。
