Query by age in hql

Question

I have a class User with one field called birthDate which is a java.sql.Date. How do I do a hql query that will retrieve all Users that are between min and max years old?

(My real scenario is slightly more complex than that but that's where I am stuck right now).

UPDATE

It must be an hql expression so I can put the age expression in a computed property.

Answer 1

Calculate the birth dates corresponding to the min and max ages. Then use the below HQL.

Select u from User u where u.birthDate between :minDate and :maxDate

Setup the the minDate and maxDate to the values you computed before executing the query.

Answer 2

It depends on the database. Most have some way of handling date arithmetic. MySQL has a datediff function that will return a number of days so you could divide that by 365 and get pretty close. The MySQL dialect already has datediff as a registered function. For other databases you may need to use different functions and possibly register them in a custom dialect. But you may be off by a little unless you take leap years into account which is tricky in an HQL expression. Using dates is easier because you can keep the month and day constant, change the year, and then use < or > in HQL.