oracle 带有时间戳和日期字段的 JPA 选择查询无法检索结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27823370/
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 select query with timestamp and date fields fail to retrieve results
提问by DeludedPsyche
I am trying to fetch records using JPA Query which has DATE and TIMESTAMP columns in WHERE clause. But somehow date and timestamp columns fail to retrieve anything from database.
我正在尝试使用 JPA 查询获取记录，该查询在 WHERE 子句中有 DATE 和 TIMESTAMP 列。但不知何故日期和时间戳列无法从数据库中检索任何内容。
Code Segment:
代码段：
String sql = "Select F.* from FIN_TABLE F where F.COL1_NUM = :COL1 and F.COL2_TIMESTAMP =:COL2 and F.COL3_DATE =:COL3";
Query query = JPAentityManager.createNativeQuery(sql);
query.setParameter("COL1", 123);
//java.sql.Timestamp:2014-10-29 12:00:00.0
query.setParameter("COL2", new java.sql.Timestamp(new java.sql.Date(new SimpleDateFormat("MMM dd yyyy HH:mm:sssa").parse("OCT 29 2014 12:00:000AM").getTime()).getTime()));
//java.sql.Date:2014-10-29
query.setParameter("COL3", new java.sql.Date(new SimpleDateFormat("MMM dd yyyy HH:mm:sssa").parse("OCT 29 2014 12:00:000AM").getTime()));
List<FinTable> result = (List<FinTable>)query.getResultList();
And Data in Oracle is:
而 Oracle 中的数据是：
COL1_NUM COL2_TIMESTAMP COL3_DATE 123 29-OCT-14 12.00.00.000000000 AM 26-Nov-14 456 29-OCT-14 12.00.00.000000000 AM 26-Nov-14
I am originally trying to retrieve results using EntityManager.find(Class, Object), but it was also failing so i tried with createNativeQuery(), which also fails to succeed. My FinTable entity has these columns as Timestamp and Date.
我最初尝试使用 EntityManager.find(Class, Object) 检索结果，但它也失败了，所以我尝试使用 createNativeQuery()，但也未能成功。我的 FinTable 实体将这些列作为时间戳和日期。
Please enlighten the right way. :) Thanks !!
请指教正确的方法。：） 谢谢 ！！
采纳答案by DeludedPsyche
Thanks for replying. Problem is resolved.
感谢回复。问题解决了。
I was correctly using the DATE and TIMESTAMP objects.
50% of the problem problem was with the SimpleDateFormat parsing where I had mentioned the 24Hr Hour format (HH) and AM/PM together, the correct parser to use was...
我正确地使用了 DATE 和 TIMESTAMP 对象。
50% 的问题出在 SimpleDateFormat 解析上，我在其中提到了 24Hr Hour 格式 (HH) 和 AM/PM，要使用的正确解析器是...
"MMM dd yyyy hh:mm:sssa" //OCT 29 2014 12:00:000AM
“MMM dd yyyy hh:mm:sssa”//2014 年 10 月 29 日 12:00:000AM
and not...
并不是...
"MMM dd yyyy HH:mm:sssa" //OCT 29 2014 00:00:000
“MMM dd yyyy HH:mm:sss a” //OCT 29 2014 00:00:000
Remaining 50% of the problem was with column COL3_DATE which was mistakenly defined as DATE but was originally a TIMESTAMP; changing it to TIMESTAMP resolved the problem.
剩下的 50% 的问题是列 COL3_DATE，它被错误地定义为 DATE 但最初是一个 TIMESTAMP；将其更改为 TIMESTAMP 解决了问题。
However, working with Date is still the issue by any approach (java.sql.Date, java.util.Date with Temporal.DATE).
但是，通过任何方法（java.sql.Date、java.util.Date 和 Temporal.DATE）使用 Date 仍然是问题。
Thanks for contributing. :)
感谢您的贡献。:)
回答by dr.bapbap
Date and Calendar parameter values require special methods in order to specify what they represent, such as a pure date, a pure time or a combination of date and time, as explained in detail in the Date and Time (Temporal) Types section.
日期和日历参数值需要特殊的方法来指定它们所代表的内容，例如纯日期、纯时间或日期和时间的组合，如日期和时间（时间）类型部分中的详细说明。
For example, the following invocation passes a Date object as a pure date (no time):
例如，以下调用将 Date 对象作为纯日期（无时间）传递：
query.setParameter("date", new java.util.Date(), TemporalType.DATE);
and timestamp
和时间戳
query.setParameter("date", new java.util.Date(), TemporalType.TIMESTAMP);
