| Criteria API and grouping by a truncated date [message #636667] |
Tue, 02 November 2010 12:14  |
 Janne
Messages: 10
Registered: February 2010 |
Junior Member |
|
|
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 12:16] Report message to a moderator |
|
|
| Re: Criteria API and grouping by a truncated date [message #637187 is a reply to message #636667] |
Thu, 04 November 2010 14:13  |
|
EclipseLink Expressions support adding literal syntax such as "dd" to SQL using the LiteralExpression, ExpressionBuilder.literal(), but JPA Criteria does not have this. literal() in Criteria means a constant parameter value (ConstantExpression is used).
Did you try just using another function for dd, i.e. function("dd")? It should print just dd, but you might get dd() because it is a function.
EclipseLink Expressions support both truncateDate(datePart) and addDate(datePart, number). You could use an Expression query instead of Criteria or mix the two by creating a FunctionExpressionImpl directly. It would be nice if it were easier to mix the two, such as an from(Expression) on CriteriaBuilderImpl, feel free to log a bug for this.
James : Wiki : Book : Blog : Twitter
|
|
|
|
Powered by
FUDForum. Page generated in 0.02663 seconds
Search
Help
Register
Login
Home
