java 使用 jpql 和 jpa 从日期字段中提取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34295105/
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
Extract Year from date field using jpql and jpa
提问by user3419507
I want to extract the year part from a row in the database in order to compare it with a value.
我想从数据库中的一行中提取年份部分，以便将其与一个值进行比较。
Here's my function
这是我的功能
public List<Dossier> getAllDossierParAn() {
Date date = new Date();
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
int strI = calendar.get(Calendar.YEAR);
TypedQuery<Dossier> query;
query = em.createQuery("SELECT d FROM DOSSIER d WHERE EXTRACT(YEAR ,d.dateCreation)=2015", Dossier.class);
System.out.println(strI);
return query.getResultList();
}
I get always
我总是
An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT d FROM DOSSIER d WHERE EXTRACT(YEAR FROM d.dateCreation)=2015]. [14, 21] The abstract schema type 'DOSSIER' is unknown. [48, 62] The state field path 'd.dateCreation' cannot be resolved to a valid type.
在 EntityManager 中创建查询时发生异常：异常描述：编译问题 [SELECT d FROM DOSSIER d WHERE EXTRACT(YEAR FROM d.dateCreation)=2015]。[14, 21] 抽象模式类型“DOSSIER”未知。[48, 62] 状态字段路径“d.dateCreation”无法解析为有效类型。
I test it directly in the database and it works
我直接在数据库中测试它，它的工作原理
select * from dossier where extract(year from date_creation)=2015
I'm using jpaand ejband jdeveloperas IDE.
我使用jpa，并ejb与jdeveloper作为IDE。
回答by Neil Stockton
"Directly in the database" is called SQL.
“直接在数据库中”称为 SQL。
createQuerytakes in JPQL not SQL, and YEAR / EXTRACT are invalid keywords (though YEAR, but not EXTRACT, is supported by some JPA providers). Any decent JPA docs would spell that out.
createQuery接受 JPQL 而不是 SQL，并且 YEAR / EXTRACT 是无效关键字（虽然 YEAR 但不是 EXTRACT，一些 JPA 提供程序支持）。任何体面的 JPA 文档都会说明这一点。
回答by renke
First, the main problem with your query is what the error message say:
首先，您查询的主要问题是错误消息所说的内容：
The abstract schema type 'DOSSIER' is unknown
抽象模式类型“DOSSIER”未知
Since JPA is mapping your POJOs as entities, their names are case sensitive. Your query should be:
SELECT d FROM Dossier d WHERE ...
由于 JPA 将您的 POJO 映射为实体，因此它们的名称区分大小写。您的查询应该是：
SELECT d FROM Dossier d WHERE ...
Also, regarding the problem you mentioned, the EXTRACTfunction is only supported by EclipseLink, as far as I know. By the error message, I think this is your JPA implementation, but if it's not, there are two options:
另外，关于您提到的问题EXTRACT，据我所知，该功能仅由EclipseLink支持。根据错误消息，我认为这是您的 JPA 实现，但如果不是，则有两种选择：
- If you're using Hibernate, it has built in functions for retrieving date parts, such as
YEAR(date),MONTH(date),DAY(date),HOUR(date),MINUTE(date)andSECOND(date). - For any other JPA implementation, or if you want to keep it JPQL compliant, you can workaround with
SUBSTRING:SUBSTRING(d.dateCreation, 1, 4). Note the first position of a string is denoted by 1;
- 如果您在使用Hibernate，它具有内置的功能，用于检索日期部分，如
YEAR(date)，MONTH(date)，DAY(date)，HOUR(date)，MINUTE(date)和SECOND(date)。 - 对于任何其他 JPA 实现，或者如果您想让它保持 JPQL 兼容，您可以使用
SUBSTRING:解决方法SUBSTRING(d.dateCreation, 1, 4)。请注意，字符串的第一个位置由 1 表示；
Hope it helps
希望能帮助到你
回答by user3419507
thank you guys i solved the problem: first i got the current year than format it to int than to String in order to do the comparaison and substract it here's my code it work fine:
谢谢你们，我解决了这个问题：首先我得到了当前年份，然后将其格式化为 int 而不是 String 以便进行比较并减去它，这是我的代码，它工作正常：
public List<Dossier> getAllDossierParAn() {
Date date = new Date();
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
int strI = calendar.get(Calendar.YEAR);
String strInt =Integer.toString(strI);
String nvlstri= strInt.substring(2, 4);
TypedQuery<Dossier> query;
query = em.createQuery("SELECT d FROM Dossier d WHERE SUBSTRING(d.dateCreation, 7, 2) = :vr", Dossier.class);
query.setParameter("vr",nvlstri);
System.out.println("l anne est " +strI);
System.out.println("la date formaté " +nvlstri);
return query.getResultList();
}
回答by Bwire
On eclipselink, the way that works for me was something like:
在 eclipselink 上，对我有用的方式是这样的：
SELECT a.id, EXTRACT(WEEK CURRENT_DATE ) FROM Account a
This works on postgres and sql server at least but should work with other supported databases too.
这至少适用于 postgres 和 sql server，但也应该适用于其他受支持的数据库。
From javadocsthe syntax supported is :
从javadocs支持的语法是：
extract_expression ::= EXTRACT(date_part_literal [FROM] scalar_expression)
The FROM seems to cause funny exceptions on sql server.
FROM 似乎在 sql server 上引起了有趣的异常。
