Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Criteria API and grouping by a truncated date
Criteria API and grouping by a truncated date [message #636667] Tue, 02 November 2010 12:14 Go to next message
Janne is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline 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
Re: Criteria API and grouping by a truncated date [message #676855 is a reply to message #637187] Mon, 06 June 2011 18:09 Go to previous message
Ville Kaukonen is currently offline Ville Kaukonen
Messages: 19
Registered: January 2011
Junior Member
Did you ever get DATEDIFF to work. I'm unable to use it.

http://www.birt-exchange.org/org/forum/index.php/topic/22498-datediff-with-sql-server/
Previous Topic:Load only part of object graph
Next Topic:Cascade delete not desired, on a ManyToMany relation
Goto Forum:
  


Current Time: Tue Oct 21 13:46:05 GMT 2014

Powered by FUDForum. Page generated in 0.01665 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software