JPQL avg 聚合函数是否适用于整数?
我有一个名为 Surgery 的 JPA 2 实体。它有一个名为 transfusionUnits 的成员,它是一个 Integer。
数据库中有两个条目。执行此 JPQL 语句:
Select s.transfusionUnits from Surgery s
产生预期结果:
2
3
以下语句产生预期答案 5:
Select sum(s.transfusionUnits) from Surgery s
我预计以下语句的答案为 2.5,但它返回 <改为强>2.0。
Select avg(s.transfusionUnits) from Surgery s
如果我在不同的(Float)成员上执行该语句,结果是正确的。关于为什么会发生这种情况有什么想法吗?我需要在 JPQL 中进行某种类型的转换吗?这可能吗?当然,我在这里遗漏了一些微不足道的东西。
I have a JPA 2 Entity named Surgery. It has a member named transfusionUnits that is an Integer.
There are two entries in the database. Executing this JPQL statement:
Select s.transfusionUnits from Surgery s
produces the expected result:
2
3
The following statement produces the expected answer of 5:
Select sum(s.transfusionUnits) from Surgery s
I expect the answer of the following statement to be 2.5, but it returns 2.0 instead.
Select avg(s.transfusionUnits) from Surgery s
If I execute the statement on a different (Float) member, the result is correct. Any ideas on why this is happening? Do I need to do some sort of cast in JPQL? Is this even possible? Surely I am missing something trivial here.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
首先,让我总结一下 JPA 2.0 规范中关于 AVG 的内容(完整内容请参阅4.8.5 SELECT 子句中的聚合函数部分)
因此,在第一次阅读后,我期望以下代码片段能够通过:
但事实并非如此,我得到了
2.0
作为结果(一个 Double,但不是预期的结果)。因此,我查看了数据库级别,发现 SQL 查询实际上没有返回
2.5
。事实上,我的数据库文档中关于 AVG 的描述是这样的:我期待太多了。 JPA 将以 Double 形式返回结果,但它不会发挥任何作用。如果查询未返回具有所需精度的结果,您将无法在 Java 级别获得它。
因此,在不更改
transfusionUnits
类型的情况下,我必须运行此本机查询才能使事情正常工作:更新: 似乎某些数据库(例如 MySQL)确实返回一个值在 INT 列上使用 AVG 时的小数部分(无论我此处使用的数据库的记录行为如何,这都是有意义的)。对于其他数据库,可以处理上述转换
在“方言”中(例如,参见 Hibernate 的 HHH-5173)使用 JPQL 时避免数据库之间的可移植性问题。
First, let me summarize what the JPA 2.0 specification says about AVG (see section 4.8.5 Aggregate Functions in the SELECT Clause for the full content)
So, after a first read, I was expecting the following snippet to pass:
But it didn't, I was getting
2.0
as result (a Double, but not the expected result).So I looked at the database level and realized that the SQL query was actually not returning
2.5
. And indeed, this is what the documentation of my database says about AVG:I was expecting too much. JPA will return the result as a Double, but it won't do any magic. If the query doesn't return a result with the required precision, you won't get it at the Java level.
So without changing the type of
transfusionUnits
, I had to run this native query to get things working:Update: It appears that some database (e.g. MySQL) do return a value with a decimal part when using AVG on an INT column (which makes sense, regardless of the documented behavior of the database I used here). For other databases, the above cast could be handled
in the "dialect" (e.g. see HHH-5173 for Hibernate) to avoid portability issues between database when using JPQL.
将平均场转换为双倍就可以了。不确定对性能的影响,但不应该有什么坏处:
Casting the averaged field to double does the trick. Not sure about performance implications, but should not be anything evil: