JPA Criteria API - arithmetic with Calendar values

Question

I am rewriting a huge search query, originally created in Hibernate Criteria. I am trying to use only standard JPA solutions. The problem I am facing with is, that I want to make a where clause like this:

select ... where (stopTime - startTime) > minimalLength

And this does not seems to possible with Calendar values, as I can't do arithmetic with them using Criteria API. Is there a solution for this?

Update 1

As it was mentioned in the answers (now it is deleted), Criteria API has the sum, diff, prod methods, but these have the signature of :

<N extends java.lang.Number> Expression<N>
    method(Expression<? extends N> x, Expression<? extends N> y)
<N extends java.lang.Number> Expression<N>
    method(N x, Expression<? extends N> y)
<N extends java.lang.Number> Expression<N>
    method(Expression<? extends N> x, N y)

So they are not usable for Calendar (or even for Date).

Update 2

I think, I am on the right track. I decided to use the function method of CriteriaBuilder, and use it to call the (unfortunately vendor specific) datediff SQL Server method. It looks like this:

builder.function("datediff", Integer.class, builder.literal("second"), startTime, stopTime)

This nearly works, the only problem is, that datediff does not work with String as the first parameter, it needs a keyword (i.e. "second" without quotes). Does anybody know a way to pass a literal to the function?

I had the same issue, and ran into the same dead end you encountered in your update 2. — Sasha Borodin, May 11 '15 at 21:11

meskobalazs · Accepted Answer · 2014-10-13T14:13:07.267

1

Okay, finally I gave up, and did it with HQL istead of Criteria API. Let's just hope that my issue will be fixed in the next iteration of Criteria API...

edited Oct 13 '14 at 14:13

answered Oct 13 '14 at 09:43

meskobalazs

15,741
2
40
63

There is similar question: https://stackoverflow.com/questions/9449003/compare-date-entities-in-jpa-criteria-api But with right answer. – Michał Piątkowski Jun 11 '17 at 22:36

score 1 · Answer 2 · answered May 11 '15 at 21:25

1

I had the same issue, and ran into the same dead end you encountered in your update 2.

The only way around this that I could think of, is to create a UDF that doesn't take the literal "second", and delegates to datediff.

 CREATE FUNCTION [dbo].[datediff_seconds]
 (
    @from datetime,
    @to datetime
 )
 RETURNS int
 AS
 BEGIN

    RETURN datediff(second, @from, @to)

 END

 GO

It's a kludge, but works.

answered May 11 '15 at 21:25

Sasha Borodin

193
1
1
8

Yes, it works if you can use stored procedures, for me it was not an option. – meskobalazs May 11 '15 at 21:27
It's a UDF, not a stored procedure; but I get your point - this is only an option if adding an SQL function to the schema is an option. – Sasha Borodin May 11 '15 at 21:31

score 0 · Answer 3 · answered Nov 04 '15 at 15:11

I ended up with simply converting to unix_timestamp, which are numbers and can be diff-ed. It is simply a shame that JPA can not give a parameter to a function.

cb.greaterThan(
  cb.diff(
    cb.function("unix_timestamp", Long.class, root.get(Table_.until)),
    cb.function("unix_timestamp", Long.class, root.get(Table_.from))
  )
, 3600L*longerThanHours)

JPA Criteria API - arithmetic with Calendar values

3 Answers3