休眠@Formula不支持查询包含'CAST() as int'函数

Question

下面是ExecutionHistory类的一个属性，它的值是从这个属性中获取的@Formula using JPA/Hibernate from exectution_history table，

@Formula("(SELECT SUM(dividend) || '/' || SUM(divisor) " +
"FROM (SELECT CAST(substr(sr.result, 1, position('/' in sr.result)-1 ) AS int) AS dividend ," +
"CAST(substr(sr.result, position('/' in sr.result)+1 ) AS int) AS divisor " +
"FROM suite_results as sr WHERE sr.execution_history=id) AS division)")
private String result;

当我试图获得ExecutionHistory类的实例时，我发现上面的公式查询由JPA/Hibernate像这样转换:

select executionh0_.id as id7_1_, executionh0_.execution_plan as execution3_7_1_, executionh0_.start_time as start2_7_1_, 
(SELECT SUM(sr.duration) FROM suite_results as sr WHERE sr.execution_history=executionh0_.id) as formula0_1_, 
(SELECT SUM(executionh0_.dividend) || '/' || SUM(executionh0_.divisor) FROM 
(SELECT CAST(substr(sr.result, 1, position('/' in sr.result)-1 ) AS executionh0_.int) AS executionh0_.dividend , 
CAST(substr(sr.result, position('/' in sr.result)+1 ) AS executionh0_.int) AS executionh0_.divisor 
FROM suite_results as sr WHERE sr.execution_history=executionh0_.id) AS executionh0_.division) as 
formula1_1_, executionp1_.id as id6_0_, executionp1_.build_number as 
build2_6_0_, executionp1_.name as name6_0_, executionp1_.owner as owner6_0_, executionp1_.sut as sut6_0_, 
executionp1_.wait_for_page_to_load as wait6_6_0_ from execution_history executionh0_ 
left outer join execution_plans executionp1_ on executionh0_.execution_plan=executionp1_.id where executionh0_.id=?

所以问题是，这里的公式查询包含"CAST() AS int"，但在Hibernate的查询转换期间，它把不必要的表引用并执行为"CAST() AS executionh0_int"，所以它在执行时给出sql语法异常。

我不知道如何避免这个问题，有人能帮我吗?

谢谢。

Answer 1

这是一个老问题，但我还是会给出答案。

如果你使用的是SQL Server数据库，你可以在你要转换的类型周围添加双引号。像这样:

@Formula("CAST(FLOOR(CAST( dat_criacao AS "float")) AS "datetime")")

Answer 2

不知道你在使用哪个数据库，但是在SQL Server中你应该使用CONVERT而不是CAST在你的Hibernate查询