JPQL平均聚合函数是否可以与整数一起使用? [英] Does the JPQL avg aggregate function work with Integers?

问题描述

我有一个名为 Surgery 的JPA 2实体.它具有一个名为 transfusionUnits 的成员，该成员是一个 Integer .

I have a JPA 2 Entity named Surgery. It has a member named transfusionUnits that is an Integer.

数据库中有两个条目.执行此JPQL语句:

There are two entries in the database. Executing this JPQL statement:

Select s.transfusionUnits from Surgery s

产生预期的结果:

2
3

以下语句产生预期的答案为 5 :

The following statement produces the expected answer of 5:

Select sum(s.transfusionUnits) from Surgery s

我希望以下语句的答案为 2.5 ，但它返回 2.0 .

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

如果我在其他(Float)成员上执行该语句，则结果是正确的.有什么想法为什么会这样?我需要在JPQL中进行某种类型的转换吗?这有可能吗?当然，我在这里错过了一些琐碎的事情.

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.

推荐答案

首先，让我总结一下JPA 2.0规范关于AVG的内容(有关SELECT子句的 4.8.5聚合函数部分，完整内容)

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)

AVG函数将状态字段路径表达式作为参数，并计算该组中Sate字段的平均值.状态字段必须为数字，并且结果以Double形式返回.

The AVG function takes a state field path expression as an argument and calculates the average value of the sate field over the group. The state field must be numeric, and the result is returnd as a Double.

因此，在初读之后，我期望以下代码段能够通过:

So, after a first read, I was expecting the following snippet to pass:

Query q = em.createQuery("select avg(s.transfusionUnits) from Surgery s");
Double actual = (Double) q.getSingleResult();
assertEquals(2.5d, actual.doubleValue());

但是没有，我得到的结果是2.0(双精度，但不是预期的结果).

But it didn't, I was getting 2.0 as result (a Double, but not the expected result).

因此，我查看了数据库级别，并意识到SQL查询实际上没有返回2.5.确实，这就是我的数据库文档中有关AVG的内容:

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:

平均值(均值).如果未选择任何行，则结果为NULL.仅在select语句中允许聚合. 返回值与参数具有相同的数据类型.

我期望太高了. JPA会将结果作为Double返回，但不会做任何魔术.如果查询没有以所需的精度返回结果，那么您将无法在Java级别获得该结果.

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.

因此，在不更改transfusionUnits类型的情况下，我必须运行此本机查询才能使工作正常进行:

So without changing the type of transfusionUnits, I had to run this native query to get things working:

Query q = em.createNativeQuery("SELECT AVG(CAST(s.transfusionUnits as double)) from Surgery s");
Double actual = (Double) q.getSingleResult();
assertEquals(2.5d, actual.doubleValue());

---

更新:似乎在INT列上使用AVG时，某些数据库(例如MySQL)确实返回带有小数部分的值(不管数据库I记录的行为如何，这都是有意义的)在这里使用).对于其他数据库，可以处理上面的演员表 在方言"中(例如，对于Hibernate，请参见 HHH-5173 )避免使用JPQL时数据库之间的可移植性问题.

---

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.

这篇关于JPQL平均聚合函数是否可以与整数一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！