17

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
jagbandhuster
  • 677
  • 2
  • 7
  • 20

5 Answers5

27

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.

bdulac
  • 1,686
  • 17
  • 26
James
  • 17,965
  • 11
  • 91
  • 146
  • Hi James, Thanks for your answer. I am actually using toplink essentials. I applied the way as above bu still I get: Unexpected Token - FUNC. Please Help! – jagbandhuster Sep 10 '10 at 04:08
  • @venomrld As James wrote, this FUNC stuff is available EclipseLink 2.1+, see [Bug 300512](https://bugs.eclipse.org/bugs/show_bug.cgi?id=300512). So don't expect to find it in TopLink Essentials. – Pascal Thivent Sep 10 '10 at 06:22
  • So I'm stuck. Please help if some way exists. I'm using netbeans 6.5 and visual faces for development. Seems I've to change pretty much code to migrate. – jagbandhuster Sep 10 '10 at 06:30
7

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!

if you need you a dynamic variable, you can do that too. here :poDate is the year which is deifned in the 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.

amrodelas
  • 85
  • 2
  • 7
  • Thanks for reply.. Actually the problem I am facing that the values are dynamic.. i.e The values are taken from variables only... So I switched to native SQL queries and they work great. Anyway, thanks again. – jagbandhuster Aug 22 '11 at 17:54
3

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.

I am using Toplink Essentials for the same. Please help, if any function exists in Toplink. Thanks.

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

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
1

Following worked for me with 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")

suraj bahl
  • 2,864
  • 6
  • 31
  • 42
-1

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);
Matan Lachmish
  • 1,235
  • 12
  • 17
luisTercero_
  • 1