java JPA - 返回 StartDate 之后和 EndDate 之前的实体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47900182/
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
JPA - Returning entities that are After StartDate and Before EndDate
提问by Suresh Atta
I have two dates in my entity. ie.
我的实体中有两个日期。IE。
Date startDate;
Date endDate;
How do I query so that given a date, it will return all entities where the specified date lies between startDateand endDate?
如何查询以便给定日期，它将返回指定日期介于startDate和之间的所有实体 endDate？
I already tried the following:
我已经尝试了以下方法：
findByStartDateAfterAndEndDateBefore(Date givenDate);
And Spring-Data-JPA didn't like this and running into errors. There is no specific error and the repo just can't be injected to my class.
而 Spring-Data-JPA 不喜欢这样并且遇到错误。没有特定错误，并且无法将 repo 注入我的班级。
What is the correct way? I know this can be done easily wqith Hibernate criteria or with Native SQL but trying to do that in Spring JPA.
正确的方法是什么？我知道这可以通过 Hibernate 标准或使用 Native SQL 轻松完成，但尝试在 Spring JPA 中做到这一点。
Is this a problem with the query itself or some sort of incompatibility between the Date types Spring uses?
这是查询本身的问题还是 Spring 使用的日期类型之间的某种不兼容？
Tried findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate)and that returns null however.
尝试过findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate)，但是返回 null。
回答by Sasha Shpota
You can't use only one parameter because of Spring Data restrictions, but you can workaround it using code like this:
由于 Spring Data 限制，您不能只使用一个参数，但您可以使用如下代码解决它：
List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date startDate, Date endDate);
default List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date givenDate) {
return findByStartDateBeforeAndEndDateAfter(givenDate, givenDate);
}
This code should cover your needs. I also verified it with Spring Boot 1.5.9. using spring-data-get-started example.
此代码应满足您的需求。我也用 Spring Boot 1.5.9 验证过。使用spring-data-get-started 示例。
回答by Suresh Atta
To my surprise LessThanand GreaterThanworking and Beforeand Afterfailing badly.
令我惊讶的是LessThan，GreaterThan工作Before和After失败都很糟糕。
I never thought I can use less than and greater than for dates and always look at date related functions like between, before, after.
我从没想过我可以对日期使用小于和大于，并且总是查看与日期相关的函数，例如之间、之前、之后。
That's mostly because of the documentation example
这主要是因为文档示例
After
findByStartDateAfter
… where x.startDate > ?1
Before
findByStartDateBefore
… where x.startDate < ?1
So I looked more close into docs of Spring Data JPAand found something interesting with the below example
所以我更仔细地研究了Spring Data JPA 的文档，并在下面的例子中发现了一些有趣的东西
LocalDate date = new LocalDate().minusYears(2);
return builder.lessThan(root.get(_Customer.createdAt), date);
So while comparing datatime the authors using the criteria lessthanfor time property.
因此，在比较数据lessthan时间时，作者使用时间属性的标准。
So given a shot with less than and worked and again gave a shot with greater than aswell and later together. So I came up with a conclusion
所以给了一个小于和工作的镜头，然后又给了一个大于和后来的镜头。所以我得出了一个结论
public MyDateEntity findByStartDateLessThanAndEndDateGreaterThan(Date sDate, Date eDate);
And this is working so far. And I believe, there must be a clean way to handle dates probably with before,after,betweenbut I just can't figure that out.
到目前为止，这是有效的。而且我相信，必须有一种干净的方法来处理日期，before,after,between但我就是想不通。
Would be great if someone figure that out.
如果有人弄清楚了，那就太好了。
回答by Hiren
You are using ANDcondition in your JPA query but providing only one parameter. You should use like findByStartDateAfterAndEndDateBefore(Date startDate, Date endDate);
您AND在 JPA 查询中使用了条件，但只提供了一个参数。你应该使用像findByStartDateAfterAndEndDateBefore(Date startDate, Date endDate);
回答by coladict
Since you only need the Date object as an equivalent of LocalDate, this should do the trick.
由于您只需要 Date 对象作为 LocalDate 的等价物，这应该可以解决问题。
default List<AnEntity> findByStartDateAfterAndEndDateBefore(Date startDate, Date endDate) {
Calendar cal = Calendar.getInstance();
cal.setTime(endDate);
cal.add(Calendar.DATE, -1);
return findByStartDateBetween(startDate, cal.getTime());
}
Because you want it to include the start, but not the end, and SQL BETWEENis inclusive, we just move the end one day back.
因为您希望它包含开头而不是结尾，并且 SQLBETWEEN是包含性的，所以我们只是将结尾移回一天。
回答by DHARMENDRA SINGH
Public List<EntityClass> findData(Date startDate,Date endDate)
{
Query<EntityClass> _q = em.createQuery("select a.* from _tableName a
where a.startDate>= :d1 AND a.endDate<= :d2");
_q.setParameter("d1", startDate, TemporalType.DATE);
_q.setParameter("d2", endDate, TemporalType.DATE);
List<EntityClass> result = query.getResultList();
em.getTransaction().commit();
return result;
}
回答by fabioresner
I use it with no problems like this:
我使用它没有这样的问题：
findAllByEntityNotNullAndDateBetween(Date begin, Date end);
Have you already tried it?
你已经尝试过了吗？
