CODEWITHSUNDEEP
jpaejb-3.0
MYE
MYE

Reputation: 1003

How to select only month in JPA

i have trouble with select only month or year in JPA

in mysql i write statement follow:

select * from table where Month(date) = 12 ;

and in entity bean i write follow:

select t from table t where Month(t.date) = 12;

but it throw Error !!

PS: sorry i can't attach my stacktrace because i'm not at home :D

Upvotes: 2

Views: 3589

Answers (3)

roneo
roneo

Reputation: 1257

This might help someone.

Few JPA implementations have built-in support for date/time functions.

EclipseLink

EclipseLink supports EXTRACT allowing any database supported date/time part value to be extracted from the date/time. The EXTRACT function is database independent, but requires database support. EXTRACT(YEAR, model.currencyExchangeDate), but it requires EclipseLink 2.4.x

FUNC allows for a database function to be call from JPQL. It allows calling any database functions not supported directly in JPQL. To call the DB function MyFunc(a, b, c) use FUNC('MyFunc', a, b, c).

You can try FUNC('YEAR', currencyExchangeDate) to call 'YEAR' function.

Hibernate

In HQL, you can have date function YEAR(date), MONTH(date), DAY(date) to extract required details. Other time functions supported are HOUR(date), MINUTE(date), SECOND(date).

Criteria API

CriteriaBuilder#function() : Create an expression for the execution of a database function.

CriteriaQuery<IptExchangeratelines> cq = cb.createQuery(IptExchangeratelines.class);
Root<IptExchangeratelines> exRateLine = cq.from(IptExchangeratelines.class);
cq.where(cb.equal(cb.function("year", Integer.class,

exRateLine.get(exRateLine_.currencyExchangeDate)), year)
.and(cb.equal(cb.function("month", Integer.class,
exRateLine.get(exRateLine_.currencyExchangeDate)), month)));

Upvotes: 0

DataNucleus
DataNucleus

Reputation: 15577

Not part of JPA, and consequently in the role of vendor extensions. Most JPA implementations support it ... and you don't say which one you're using. DataNucleus certainly supports MONTH, YEAR, DAY, HOUR, MINUTE, SECOND.

Upvotes: 0

fasseg
fasseg

Reputation: 17761

sorry but you cant do this with JPA as far as i know. hibernate's hql on the otherside knows stuff like month() hour() and minute().

check this question: JPA Date Arithmetic?

hope that helped

Upvotes: 1

Related Questions