| Criteria API and grouping by a truncated date [message #636667] |
Tue, 02 November 2010 08:14  |
Eclipse User |
|
|
|
I'm using EclipseLink 2.1.1 and SQL Server 2008. I can't figure out how to group Criteria API query's results by the day part of a date.
I have an entity that's something like this:
class Event{
@Id
Long id;
@Temporal(TemporalType.TIMESTAMP)
Date created;
}
I'd like to create a Criteria API query that returns the amount of Event's for each day. As the "created" property is a TIMESTAMP, I'd have to truncate it in the query to get the daily event counts. In SQL this would be something like:
-- clip --
Oracle:
select trunc(created), count(*) from event group by trunc(date);
SQL Server 2008:
select DATEADD(dd, 0, DATEDIFF(dd, 0, created)), count(*)
from event
group by DATEADD(dd, 0, DATEDIFF(dd, 0, created));
-- clip --
AFAIK JPA / criteria API doesn't support these sorts of date functions directly, so I thought this might be possible with the CriteriaBuilder.function() method. I tried something like:
-- clip --
Expression<Date> datediff =
builder.function("DATEDIFF",
Date.class,
datepart,
builder.literal(0),
root.get(Event_.created));
Expression<Date> dateadd =
builder.function("DATEADD",
Date.class,
datepart,
builder.literal(0),
datediff);
query.select( builder.array(
dateadd,
builder.count( root.get(Event_.id)
)) );
query.groupBy( dateadd );
-- clip --
The bit I'm having trouble with is the datepart parameter for the SQL Server DATEDIFF / DATEADD functions. It should be a constant "dd" without the quotation marks. I tried using builder.literal("dd"), but got exception "Invalid parameter 1 specified for datediff" from the SQL Server. I think this is because literal() method adds "dd" to the query as a (quoted) String. I noticed EclipseLink has a class DateConstantExpression that might work here (javadoc: "This is different from a ConstantExpression because this value is never converted with the conversion manager and is printed out as-is"), but I'm not how I should create one. I tried:
-- clip --
Expression<String> datepart =
new ExpressionImpl<String>(em.getMetamodel(),
String.class,
new DateConstantExpression("{dd}", new ExpressionBuilder()),
"{dd}");
-- clip --
but got error message:
--clip--
Exception [EclipseLink-6029] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.QueryException
Exception Description: A reference class must be provided.
Query: ReportQuery()
at org.eclipse.persistence.exceptions.QueryException.referenceC lassMissing(QueryException.java:1004)
..
--clip--
Any ideas? Is it possible to use the DATEADD/DATEDIFF functions with Criteria API?
Regards,
Janne
[Updated on: Tue, 02 November 2010 08:16] by Moderator |
|
|
|
|
Powered by
FUDForum. Page generated in 0.04653 seconds
Home