Java save criteria; To express the expression <Timestamp> for the expression <Long>>?

Question

I use CriteriaBuilder and CriteriaQuery to create my database query, but I ran into a problem that I do not know how to solve, since I am very new to this test 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 that has a list of Report objects.

In my query, I want to get all Affiliate objects that do not have Report in the latest Affiliate.maxSilenceMinutes .

My questions:

• Are there ways for a standardized JPA to change dates? How do CriteriaBuilder.subtractMilliseconds (Expression <Timestamp>, Long) sort?
• If not, is there an Expression <Timestamp> Expression <Long> way so that I can subtract currentTimestamp literal to get the minimum value for CriteriaBuilder.lessThanOrEqualTo (maximumReportTimestampMs, minimumAllowedMs) ?

I know this may seem like a confusing question, but the main part is simple: is it possible to switch Expression <Timestamp> to Expression <Long> ? This throws an exception for me if I try to use the .as method (Long.class) , but which should be the default default data type in most databases anyway?

Hope you guys can help as I feel stuck :)

Answer 1

1. If you know the value that you want to subtract at the time of the request, you can subtract in advance: Calendar c = new calendar (); c.setTime (timestamp.getTimestamp ()); c.add (DAY, - someNumberOfDays); // or whatever you want Date d = c.getTime ();

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

• CriteriaBuilder.lessThanOrEqual () works with Comparables. Timestamps are comparable. Thus, you can create a timestamp using a new timestamp (long milliseconds) and compare it with another expression.

Hope this helps.

Answer 2

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

The JDBC standard includes the escape function {fn TIMESTAMPADD( SQL_TSI_SECOND, secs, timestamp)} , which must be translated into the correct SQL for the target database, but not all JDBC implementations provide it. Therefore, there is a chance that you can add the custom standard standard function JDBCEscapeFunction in the Hibernate dialog box to get the desired result.

If you don't have this, you will need to figure out what a particular implementation of a particular database is, and there is a lot of variability here. For instance:

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

Once you know this, you can use the correct expression as an SQL criterion.

The fact that date manipulation is not standardized in the dialect and not fully implemented in many JDBC means that what you are trying to do will be very difficult to write in a neutral database mode.