CODEWITHSUNDEEP
javajpacriteria-api
meskobalazs
meskobalazs

Reputation: 16031

JPA Criteria API - arithmetic with Calendar values

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?

Upvotes: 2

Views: 1197

Answers (3)

flaschenpost
flaschenpost

Reputation: 2235

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)

Upvotes: 0

Sasha Borodin
Sasha Borodin

Reputation: 323

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.

Upvotes: 1

meskobalazs
meskobalazs

Reputation: 16031

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...

Upvotes: 1

Related Questions