SQL with Hibernate Criteria

Hibernate's Criteria is a very comprehensive API which provides the user alot of flexibility to write dynamic queries. But of course nothing is perfect. I came across a situation where i had to truncate a date field in order to get the correct result set without considering the time portion. While going through the Criteria API I did not find anything which allowed me to do this. And hence in the path for a solution i found out that Criteria allows for plain SQL syntax to be included which i thought was a big plus point because it gives the developer the flexibility without restricting him/her just to the API.

The following code depicts the way you can incorporate plain SQL to your criteria API.

DetachedCriteria testCirteria = DetachedCriteria.forClass(Employee.class);
SimpleDateFormat dateFormatForSearch = new SimpleDateFormat("dd/MM/yyyy");
Calendar joinDate = empSearchDTO.getJoinDate();

if (joinDate != null) 

{
     /**
     The following uses DateUtils of apache commons to truncate the  date object.
     **/

     joinDate = DateUtils.truncate(joinDate, Calendar.DATE);
     String dateAsStr = dateFormatForSearch.format(joinDate.getTime());
     testCirteria.add(Restrictions.sqlRestriction("trunc(emp_join_date)=to_date('" + dateAsStr + "','dd/mm/yyyy')"));
}

Read more:  My Journey Through IT

Posted via email from .NET Info