Spring Data JPA - 带有“@Param Date”的自定义@Query 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40613171/
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
Spring Data JPA - custom @Query with "@Param Date" doesn't work
提问by Aleksandar Nikolic
I create custom query for getting Invoice data by Date, but it returns null. I'm sure that I set exactly the same Date for query that exists in database.
我创建了用于按Date获取发票数据的自定义查询，但它返回null。我确定我为数据库中存在的查询设置了完全相同的日期。
I use custom query because I want more advance query to write. But issue exist in this simple query. Here is my sample code:
我使用自定义查询是因为我想要编写更多高级查询。但是这个简单的查询中存在问题。这是我的示例代码：
@Query("select i from Invoice i where " +
"i.expirationDate = :expDate")
Invoice findCompanyInvoiceByDate(@Param("expDate") Date expDate);
I tried this code but it does not work also:
我试过这段代码，但它也不起作用：
Invoice findByExpirationDate(Date expirationDate);
I also tried to add @Temporal(TemporalType.DATE)before Date and @Param but result is null.
我也尝试@Temporal(TemporalType.DATE)在 Date 和 @Param 之前添加，但结果为空。
回答by xsalefter
You should use @Temporal(TemporalType.TIMESTAMP)in your date column. If that still not enough (still return null), add columnDefinitionin @Columnannotation as well.
您应该@Temporal(TemporalType.TIMESTAMP)在日期列中使用。如果仍然不够（仍返回null），加columnDefinition在@Column标注为好。
Full working example is here(Note the so-40613171 branch. Sorry for weird repository name, and class naming. It uses by a lot of case study). Rough example:
完整的工作示例在这里（注意 so-40613171 分支。对不起，奇怪的存储库名称和类命名。它被很多案例研究使用）。粗略的例子：
Employee.java
雇员.java
@Entity
public class Employee {
@Id
private Integer id;
private String name;
private String surname;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "birth_date", columnDefinition = "DATETIME")
private Date birthDate;
// Other fields, getter setter, etc.
}
EmployeeRepository.java
EmployeeRepository.java
public interface EmployeeRepository
extends JpaRepository<Employee, Integer> {
@Query("from Employee e where e.birthDate = :birthDate")
List<Employee> findEmployeeDataByBirthDate(@Param("birthDate") Date birthDate);
}
Sample Data
样本数据
final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Company companySun = companyRepository.save(new Company(42, "Sun microsystems"));
Company companyGoogle = companyRepository.save(new Company(43, "Google"));
employeeRepository.save(new Employee(101, "James", "Gosling", dateFormat.parse("1970-01-01 17:05:05"), companySun));
employeeRepository.save(new Employee(102, "Paul", "Sheridan", dateFormat.parse("1970-01-01 17:05:05"), companySun));
employeeRepository.save(new Employee(103, "Patrick", "Naughton", dateFormat.parse("1970-01-01 17:05:05"), companySun));
employeeRepository.save(new Employee(201, "Lary", "Page", dateFormat.parse("1970-01-01 17:01:05"), companyGoogle));
employeeRepository.save(new Employee(202, "Sergey", "Brin", dateFormat.parse("1970-01-02 17:02:05"), companyGoogle));
Test Code Snippet
测试代码片段
@Test
public void employeService_findByBirthDate() throws ParseException {
final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<Employee> result = this.employeeService.findByBirthDate(dateFormat.parse("1970-01-01 17:05:05"));
Assert.assertEquals(3, result.size());
}
If you run this, the test is passed.
如果你运行这个，测试就通过了。
HTH
HTH
回答by Antwon Anderson
I just used the date as a String and used nativeQuery = true. It worked well for me.
我只是将日期用作字符串并使用nativeQuery = true. 它对我来说效果很好。
@Query(value ="select * from table st where st.created_date >= ?1", nativeQuery = true)
List<YourObject> findAllByCreatedDate(@Param("createdDate") @DateTimeFormat(iso = ISO.DATE) String createdDate);
回答by Aleksandar Nikolic
I found the answer that was useful for this problem here.
我在这里找到了对这个问题有用的答案。
If you use "date" type, I think the code from question should work, but for "datetime" you should use "between" in query or something similar. I found the solution that works for me, and this is the code:
如果您使用“日期”类型，我认为问题中的代码应该有效，但对于“日期时间”，您应该在查询中使用“之间”或类似的东西。我找到了适合我的解决方案，这是代码：
@Query("select i from Invoice i where " +
"i.expirationDate >= :todayMidnight and i.expirationDate < :tomorowMidnight " +
"order by expirationDate DESC")
List<Invoice> findCompanyInvoiceByDate(@Param("todayMidnight") Date todayMidnight, @Param("tomorowMidnight") Date tomorowMidnight);
