CODEWITHSUNDEEP
oracle-databasehibernate
mostar
mostar

Reputation: 4831

Hibernate Restrictions API to_char() function

Hi guys I have an SQL query like this:

SELECT * FROM PEOPLE P WHERE to_char(P.BIRTH_DATE,'DD/MM/YYYY') = '01/06/2012'

I want to run this query via Hibernate Restrictions API. I think the following won't do the same work:

Restrictions.eq(People.PROP_BIRTH_DATE, Calendar.getInstance())

How can I create such a restriction?

Upvotes: 0

Views: 4854

Answers (1)

JB Nizet
JB Nizet

Reputation: 692071

java.sql.Date date = java.sql.Date.valueOf("2012-06-01");
criteria.add(Restrictions.eq(People.PROP_BIRTH_DATE, date));

or

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date date = df.parse("01/06/2012");
criteria.add(Restrictions.eq(People.PROP_BIRTH_DATE, date));

The above supposes that the birth date is stored as a date in database, and not as a timestamp. If the birth date in database is a timestamp, and thus has a time portion, the portable way of doing that is:

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date begin = df.parse("01/06/2012");
Calendar endCalendar = Calendar.getInstance();
endCalendar.setTime(date);
endCalendar.add(Calendar.DATE, 1);
criteria.add(Restrictions.ge(People.PROP_BIRTH_DATE, begin));
criteria.add(Restrictions.lt(People.PROP_BIRTH_DATE, endCalendar.getTime()));

Upvotes: 1

Related Questions