Java JPA 查询 MONTH/YEAR 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3674567/
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 Query MONTH/YEAR functions
提问by jagbandhuster
How can I write a JPA query using MONTHfunction just like sql query?
如何使用MONTH像 sql 查询一样的函数编写 JPA查询？
@NamedQuery(name="querybymonth", query="select t from table1 t where MONTH(c_Date) = 5")
When I use the above pattern for query, I get an error: unexpected token - MONTH.
当我使用上述模式进行查询时，出现错误：unexpected token - MONTH.
采纳答案by James
If you are using EclipseLink (2.1) you can use the FUNC() function to call any database function that is not defined in the JPA JPQL spec.
如果您使用 EclipseLink (2.1)，您可以使用 FUNC() 函数来调用任何未在 JPA JPQL 规范中定义的数据库函数。
i.e. FUNC('MONTH', c_Date)
即 FUNC('MONTH', c_Date)
In JPA 2.1 (EclipseLink 2.5) the FUNCTIONsyntax becomes part of the specification (and replaces the EclipseLink-specific FUNC).
在 JPA 2.1 (EclipseLink 2.5) 中，FUNCTION语法成为规范的一部分（并取代了 EclipseLink 特定的 FUNC）。
If you are using TopLink Essentials, you cannot do this in JPQL, but you can define a TopLink Expression query for it (similar to JPA 2.0 criteria), or use native SQL.
如果您使用的是 TopLink Essentials，则无法在 JPQL 中执行此操作，但您可以为其定义 TopLink 表达式查询（类似于 JPA 2.0 标准），或使用本机 SQL。
Also if you are using any JPA 2.0 provider and using a Criteria query there is a function() API that can be used to define this.
此外，如果您使用任何 JPA 2.0 提供程序并使用 Criteria 查询，则可以使用 function() API 来定义它。
回答by Pascal Thivent
The MONTH()function exists in Hibernate HQL but is not a standard JPA function. Maybe your JPA provider has some proprietary equivalent but you didn't mention it. If it doesn't, fall back on native SQL.
该MONTH()函数存在于 Hibernate HQL 中，但不是标准的 JPA 函数。也许你的 JPA 提供者有一些专有的等价物，但你没有提到它。如果没有，请回退到本机 SQL。
I am using Toplink Essentials for the same. Please help, if any function exists in Toplink. Thanks.
我也在使用 Toplink Essentials。如果 Toplink 中存在任何功能，请提供帮助。谢谢。
To my knowledge, TopLink doesn't have a direct equivalent. So either use a native SQL query or maybe a TopLink Expression query (not sure about this, and not sure this is available in TopLink Essentials).
据我所知，TopLink 没有直接的等价物。因此，要么使用本机 SQL 查询，要么使用 TopLink 表达式查询（不确定这一点，也不确定这在 TopLink Essentials 中是否可用）。
回答by amrodelas
I want to query YEAR(itemDate) but the function doesn't exit, then i saw the SUBSTRING() function so what i did was
Select q from table where SUBSTRING(itemDate, 1, 4)='2011'and it works for me! hope it helps!
我想查询 YEAR(itemDate) 但该函数没有退出，然后我看到了 SUBSTRING() 函数，所以我所做的是Select q from table where SUBSTRING(itemDate, 1, 4)='2011'它对我有用
！希望能帮助到你！
if you need you a dynamic variable, you can do that too. here :poDate is the year which is deifned in the setParameter();
如果你需要一个动态变量，你也可以这样做。这里 :poDate 是在 setParameter() 中定义的年份；
@NamedQuery(name = "PurchaseOrders.findByYear", query = "SELECT p FROM PurchaseOrders p WHERE SUBSTRING(p.poDate, 1, 4) = :poDate")
Query q = em.createNamedQuery("PurchaseOrders.findByYear");
q.setParameter("poDate", s_year+"");
but if your okay with your solutions, that'll be fine. i just find JPA faster to execute.
但如果你的解决方案没问题，那就没问题了。我只是发现 JPA 执行起来更快。
回答by suraj bahl
Following worked for me with hibernate (4.3.9.Final) & JPA 2.1.
以下使用 hibernate (4.3.9.Final) 和 JPA 2.1 为我工作。
@NamedQuery(name = "PartyEntity.findByAID", query = "select distinct psc.party from PartyShortCode psc where (psc.shortCode = :aidNumber or FUNCTION('REPLACE',psc.accountReference,' ','')= :aidNumber) and psc.sourceSystem in :sourceSystem")
@NamedQuery(name = "PartyEntity.findByAID", query = "select distinct psc.party from PartyShortCode psc where (psc.shortCode = :aidNumber or FUNCTION('REPLACE',psc.accountReference,' ','')= :aidNumber ) 和 psc.sourceSystem 在 :sourceSystem")
回答by luisTercero_
if your class holds a date type variable, you can use a query like this:
如果您的类包含一个日期类型变量，您可以使用这样的查询：
@Query("select m from Movement m where m.id_movement_type.id=1 and SubString(cast(m.date as text),1,4) = :year")
List<Movement> buysForYear(@Param("year") String year);
