CODEWITHSUNDEEP
javahibernatecriteria
LorenzoR
LorenzoR

Reputation: 421

Hibernate criteria search by age range from day of birth

I have a class User which has a date of birth variable and I am trying to write a criteria query to find all users that are more than X years old and less than Y, where X and Y are two Integers and date of birth is a Date.

I solved it using this expression, but I would like to avoid the use of Expression since I guess it will only work with MySQL.

.createCriteria(User.class)
.add(Expression
         .sql("DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( BIRTHDATE ) ) ,  '%Y' ) >= " + lowAge))
.add(Expression
         .sql("DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( BIRTHDATE ) ) ,  '%Y' ) <= " + highAge))

Any ideas?

Upvotes: 0

Views: 2386

Answers (2)

Peter
Peter

Reputation: 65

It was an old question. However today I resolved in this way, may be helpful for someone more:

Criteria=cri=session.createCriteria(YourClass.class);
Conjunction conj=Restrictions.conjunction();
Calendar cal=Calendar.getInstance();
cal.add(Calendar.YEAR,(-1*age));
Date birthEnd=cal.getTime();
cal.add(Calendar.YEAR,-1);
Date birthStart=cal.getTime();
conj.add(Restrictions.gt("birthdayAttributeName", birthStart));
conj.add(Restrictions.le("birthdayAttributeName", birthEnd));
disj.add(conj);

Upvotes: 2

Jon Lin
Jon Lin

Reputation: 143966

Can you use Restrictions instead? 

.createCriteria(User.class)
.add(Restrictions.ge("birthdate", lowAge))
.add(Restrictions.le("birthdate", highAge))

birthdate being the date of birth field for your User class.

Upvotes: 1

Related Questions