Translating SQL Functions in Criteria

Question

I'm trying to reproduce this sql query into a hibernate criteria query how can I achieve tha.

SELECT * 
FROM PERSON
WHERE 
    BIRTH_DATE IS NOT NULL 
    AND (YEAR(GETDATE()) - YEAR(BIRTH_DATE)) in(6,18,26)
ORDER BY 
    MONTH(BIRTH_DATE) ASC, 
    DAY(BIRTH_DATE) ASC

Globally what I want is to get everybody in age of 6,18 and 26 years old ordered only by the month and the day not the year to have a list of future anniversaries.

In fact my problem in this situation is that I don't know how I can use the sql functions in criteria I know how criteria works but this query is quite tricky I don't have any idea how to reproduce it in criteria =/

Criteria criteriaBirthDates = his.getSession().createCriteria(Person.class);
criteriaBirthDates.add(Restrictions.isNotNull("birthdate"));
criteriaBirthDates.addOrder(Order.asc("month(birthdate)"));
criteriaBirthDates.addOrder(Order.asc("day(birthdate)"));
return criteriaBirthDates.list();

Many thanks!

Answer 1

So I went to the official hibernate forum and here was the answer :

Hi akyo

Sorry, it's impossible be be done by QBC, as an old functionality, QBC will not be improved in the future.

Please use the new functionality "JPA Criteria to do it", For example

Code:

CriteriaBuilder cb = this.em.getCriteraBuilder();
CriteriaQuery<Person> cq = cb.createQuery(Person.class);
Root<Person> person = cb.from(Person.class);
cq
.where(
    cb.isNotNull(person.get(Person_.birthDate),
    cb.in(
        cb.diff(
            cb.function("YEAR", cb.function("GETDATE")),
            cb.function("YEAR", person.get(Person_.birthDate))
        )
    )
    .value(6, 8, 26)
)
.orderBy(
    cb.asc(cb.function("MONTH", person.get(Person_.birthDate))),
    cb.asc(cb.function("DAY", person.get(Person_.birthDate)))
);

Et voilà!