JPA Query MONTH/YEAR functions

Question

How can I write a JPA query using MONTH function just like sql query?

@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.

Answer 1

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.

i.e. FUNC('MONTH', c_Date)

In JPA 2.1 (EclipseLink 2.5) the FUNCTION syntax becomes part of the specification (and replaces the EclipseLink-specific 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.

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.