Java Persistence Criterias; Cast Expression<Timestamp> to Expression<Long>?

Question

I am using the CriteriaBuilder and CriteriaQuery to build my query to the database, but I have encountered an issue that I do not know how to solve, since I am very new to this whole ordeal called JPA.

In Java, I have a property called timestamp for a class called Report, and it is set to the same corresponding @TemporalType. I also have a class called Affiliate which has a list of Report objects.

In my query, I want to fetch all the Affiliate objects that do not have a Report in the last Affiliate.maxSilenceMinutes.

My questions:

1. Are there any ways in standardized JPA to modify dates? Like a CriteriaBuilder.subtractMilliseconds(Expression<Timestamp>, Long) of sorts?
2. If not, is there a way to cast Expression<Timestamp> to Expression<Long> so that I can subtract on a currentTimestamp literal to get the minimum value for a CriteriaBuilder.lessThanOrEqualTo(greatestReportTimestampMs, minimumAllowedMs)?

I know this might feel like a confusing question, but the main part is simply: Is it possible to go Expression<Timestamp> to Expression<Long>? It throws an exception for me if I try to use the .as(Long.class) method, but which should be the default underlying data type in most DBs anyway?

Hope you guys can help, since I feel kind of stuck :)

Answer 1

If you know the value you want to subtract at the time of querying, you can subtract beforehand:

Calendar c = new Calendar();
c.setTime(timestamp.getTimestamp());
c.add(DAY, - someNumberOfDays);  //or whatever unit you want
Date d = c.getTime();

If not, you probably need to call a database function to do the subtraction, via CriteriaBuilder.function()

CriteriaBuilder.lessThanOrEqual() works on Comparables. Timestamps are comparable. So you could construct a Timestamp via new Timestamp(long ms) and compare it with the other expression.

I hope this helps.

Answer 2

This is not built into Hibernate, so you will need a custom function of some kind.

The JDBC standard includes a function escape {fn TIMESTAMPADD( SQL_TSI_SECOND, secs, timestamp)} which should be translated into the correct SQL for the target database, but not all JDBC implementations provide it. There is therefore a chance you can add a custom StandardJDBCEscapeFunction to Hibernate's Dialect to get the result you need.

If you don't have that available, you'll have to find out what the correct database specific implementation is and there is a lot of variability here. For example:

Oracle: (timestamp + secs/86400)
SQLServer: DATEADD(ss,secs,timestamp)
DB2: (timestamp + secs SECONDS)
MySQL: DATE_ADD(timestamp, INTERVAL secs SECONDS)

Once you know it, you can use the correct expression as an SQL criteria.

The fact that date-time manipulation is not standardised in the Dialect and not fully implemented in many JDBCs means that what you are trying to do will be very difficult to write in a database neutral way.