SQL Literal in JPA Hibernate Formula using TIMESTA

问题:

This question already has an answer here:

• SEPARATOR keyword not working properly in Hibernate Formula 1 answer

For a JPA entity with a pseudo column defined using @Formula:

@Formula("TIMESTAMPDIFF(SECOND, dateColA, dateColB)")
private Long duration;

Here SECOND is expected to be treated as a constant literal (with MySQL), however, in the generated JPQL, it is treated as a column name, just like dateColA and dateColB, like:

where ... TIMESTAMPDIFF(entity_.SECOND, entity_.dateColA, entity_.dateColB) ...

I wonder how to get the JPQL generated correctly for the SECOND literal?

Thanks

回答1:

Encapsulate the TIMESTAMPDIFF(SECOND, dateColA, dateColB) in a mysql function

this in Java code:

@Formula("second_diff(dateColA, dateColB)")
private Long duration;

this to create the function:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `second_diff`( dateColA date, dateColB date ) RETURNS int(11)
BEGIN
   DECLARE diff INT;
   SET diff =  TIMESTAMPDIFF(SECOND, dateColA, dateColB);
   RETURN diff;
END$$
DELIMITER ;