Search code examples
datejpagroup-byeclipselink

Eclipselink Date Range Queries (with Group by)

I am trying to do a date range query with Eclipselink JPA 2.0, but everything I try fails. The native SQL-Query would look like this:

SELECT booked FROM household_bookings WHERE YEAR(booked) = 2011 GROUP BY YEAR(booked)

But i don't want to do a native query, when having eclipselink in the background doing all I need for me. I tried this:

SELECT b FROM Booking b WHERE YEAR(b.booked) = '" + year + "'

where 'year' is an Date Object. But this returns an "Unexpected token [(]".

I also tried the ExpressionBuilder but ran into the same Error ("Unexpected token [(]").

Is there any tutorial on how to do date range queries with Eclipselink?

Solution

  • There is no YEAR function defined in JPQL, so in EclipseLink you need to use the FUNC function to define this.

    "SELECT b FROM Booking b WHERE FUNC('YEAR', b.booked) = '" + year + "'"

    With Expressions you cannot get the error ("Unexcpected token [(]") as there are no tokens? Or did you get this error from the database? Anyway, Expression defines getFunction(), and also datePart() usable on some databases.