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<br>
 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')"));

   }

As you can see the Criteria API allows to put any valid SQL within the Restrics.sqlRestriction() method. Here i have used the trunc function.

Thats it. If you have any queries or suggestions pls do leave a comment.

Cheers!!!!