In Criteria API i have to group the results of a query based on the day part of a date column returned in the query. Can you please suggest how to do this?
The following is the multiselect query
cq.multiselect(root.get("id"), root.get("recievedOn")) ;
where recievedOn is the date column. I want to groupBy the query based on the day, week, month and year. Can you please suggest how to do this.
If you use a GROUP BY, then you can only select by fields that you grouped by, or apply an aggregate function (count/min/max/etc), so I'm not sure what you are trying to do?
It is easy enough to group by the date, but if you only want to group by a specific part of the date then you need to use a function. The Criteria API does not define any date functions, but you can use the function() API to call a specific database function.
See, http://en.wikibooks.org/wiki/Java_Persistence/Criteria#Group_By
http://en.wikibooks.org/wiki/Java_Persistence/Criteria#Criteria_API_special_functions
If you are using EclipseLink you can also combine Expression objects with Criteria for additional function support,
Or you could use JPQL, which has more extensions,
http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_extract.htm#extract
