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.
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!!!!
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!!!!
Cool Tip ! Thanks for sharing.
ReplyDeleteuse HQL
ReplyDeleteHi Mark,
ReplyDeleteThx for your comment. But to achieve dynamic queries with pure HQL is hard isnt it? So in that instance only do i recommend this.
Cheers
Hi Dinuka,
ReplyDeleteSmall suggestion, It's better not to use trunc() with table columns since it has an impact in performance. Even we use functional indexing there is an impact.
reference :
http://hoopercharles.wordpress.com/2010/03/08/impact-of-the-trunc-function-on-an-indexed-date-column/
Better way:
emp_join_date >= to_date('01/09/2011','dd/mm/yyyy') AND emp_join_date < to_date('01/09/2011','dd/mm/yyyy')+1
cheers,
Dhanuka (D2)
Hi Dhanuka,
ReplyDeleteYes you are correct that truncating does have performance impacts. The post was intended to disseminate the ability to use pure SQL within the criteria API when the need arise. But i agree with your point 100%. We can achieve this with a BETWEEN syntax as well i believe or you can use as
to_char(emp_date, 'DD-MM-YYYY') and do a string comparison. But this will violate your database independence if that is a concern in your application as the regex format changes from DB to DB.