Eclipse Community Forums: EclipseLink » Criteria API and grouping by a truncated date

Help

Home

Home » Eclipse Projects » EclipseLink » Criteria API and grouping by a truncated date

Show: Today's Messages :: Show Polls :: Message Navigator

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

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

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

Report message to a moderator