Hibernate Community • View topic - Translating SQL Functions into Criteria

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Translating SQL Functions into Criteria

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

akyo

Post subject: Translating SQL Functions into Criteria

Posted: Wed Nov 18, 2015 5:43 am

Newbie

Joined: Tue Nov 17, 2015 11:53 am
Posts: 4

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

Code:

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 especially that I also have to implement this clause AND (YEAR(GETDATE()) - YEAR(BIRTH_DATE)) in(6,18,26) =/

Code:

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!

Top

babyfish

Post subject: Re: Translating SQL Functions into Criteria

Posted: Wed Nov 18, 2015 8:10 am

Regular

Joined: Mon Oct 19, 2015 7:49 am
Posts: 61
Location: ChengDu China

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

Top

akyo

Post subject: Re: Translating SQL Functions into Criteria

Posted: Thu Nov 19, 2015 3:48 am

Newbie

Joined: Tue Nov 17, 2015 11:53 am
Posts: 4

Thanks you very much babyfish now I'm sure I have to upgrade my version of hibernate 3.2 to the latest version =( only if you tell me there is a way to use the JPA criteria =/ with my hibernate version

Top

babyfish

Post subject: Re: Translating SQL Functions into Criteria

Posted: Wed Nov 25, 2015 12:10 pm

Regular

Joined: Mon Oct 19, 2015 7:49 am
Posts: 61
Location: ChengDu China

OK, Hibernate/JPA relationship

JPA1.0 Hibernate(version >=3.6.0)
JPA2.0 Hibernate(version >= 4.0.0 && version < 4.3.0)
JPA2.1 Hibernate(veriosn >=4.3.0)

Unfortunately, JPA Criteria is supported since JPA2.0, so it's impossible for Hibernate3

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum