使用TIMESTAMPDIFF的JPA Hibernate Formula中的SQL Literal [英] SQL Literal in JPA Hibernate Formula using TIMESTAMPDIFF

问题描述

对于具有使用 @Formula 定义的伪列的JPA实体：

  @Formula（TIMESTAMPDIFF（SECOND，dateColA，dateColB））
 private持续时间; 
  

这里 SECOND 预计会被视为但是，在生成的JPQL中，它被视为列名称，就像 dateColA 和 dateColB ，例如：

 其中... TIMESTAMPDIFF（entity_.SECOND，entity_ .dateColA，entity_.dateColB）... 
  

我想知道如何正确地生成JPQL SECOND literal？

谢谢

解决方案

这个在Java代码中：
$ b $ b

  @Formula（second_diff（dateColA，dateColB））
 private持续时间长; 
  

创建函数：

<$ p （dateColA date，dateColB date）RETURNS int（11）
BEGIN $ b $（$）$ $
CREATE DEFINER =`root` @`localhost` FUNCTION`second_diff` b DECLARE diff INT;
SET diff = TIMESTAMPDIFF（SECOND，dateColA，dateColB）;
RETURN diff;
END $$
DELIMITER;

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

解决方案

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 ;

这篇关于使用TIMESTAMPDIFF的JPA Hibernate Formula中的SQL Literal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！