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 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.
**/
/**
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
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