JPA Query MONTH/YEAR functions

java orm jpa jpql
jagbandhuster picture jagbandhuster · Sep 9, 2010 · Viewed 48k times · Source

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

James picture James · Sep 9, 2010

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.

Related questions

How To Define a JPA Repository Query with a Join

I would like to make a Join query using Jpa repository with annotation @Query. I have two tables: table user with iduser,user_name and: table area with idarea, area_name and iduser The native query is: SELECT u.user_…

Read More
Doing an "IN" query with Hibernate

I have a list of IDs in a String, and want to use Hibernate to get the rows with these IDs. TrackedItem is a Hibernate/JPA entity (sorry if I'm getting the naming mixed up here). My code is: String …

Read More
JPQL Create new Object In Select Statement - avoid or embrace?

I've learnt recently that it is possible to create new Objects in JPQL statements as follows: select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr Is this something …

Read More