Skip to main content



      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 08:14 Go to next message
Eclipse UserFriend
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

Re: Criteria API and grouping by a truncated date [message #637187 is a reply to message #636667] Thu, 04 November 2010 10:13 Go to previous messageGo to next message
Eclipse UserFriend
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.
Re: Criteria API and grouping by a truncated date [message #676855 is a reply to message #637187] Mon, 06 June 2011 14:09 Go to previous message
Eclipse UserFriend
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 Jul 08 02:54:16 EDT 2025

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

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

Back to the top