由于分组函数，如何处理JDBC数字类型的精度损失 [英] How to handle the loss of precision on JDBC numeric types due to grouping functions

问题描述

Oracle(和其他一些DB)的数据类型为NUMBER，可以选择使用该数据类型设置精度和小数位数.

Oracle (and some other DB's) have a datatype NUMBER, with which one can optionally set the precision and scale.

假设以下查询:

SELECT agent_code, 
AVG (opening_amt)
FROM customer 
GROUP BY agent_code;

如果上述查询中的两个字段均被定义为NUMBER(12,0)，则JDBC中的结果确实是agent_code的结果，但是在"AVG(opening_amt)"上，精度和小数位均返回0(通过java.sql.ResultSetMetaData) .getPrecision(col)和java.sql.ResultSetMetaData.getScale(col).

If both fields in above query were defined as NUMBER(12,0), the result in JDBC is indeed that for agent_code, but on "AVG(opening_amt)" both precision and scale return 0 (via java.sql.ResultSetMetaData.getPrecision(col) and java.sql.ResultSetMetaData.getScale(col) .

这与NUMBER基本相同，没有任何精度或小数位规范，并且根据oracle，等于NUMBER(38,12).

That's basically the same as NUMBER, without any precision or scale specification, and according to oracle, would equal NUMBER(38,12).

上述精度损失使我难以确定应将sql类型转换为Double还是Integer.

The above loss of precision gives me a problem to determine if the sql type should be converted to Double or Integer.

因此，我想知道这是否实际上是Oracle JDBC驱动程序中的错误，还是应该如何处理? (不，对我来说，使用BigDecimal作为对应的Java类型不是一个选择).

So, I was wondering if this is actually a bug in Oracle's JDBC driver, or how this should be handled? (and no, using BigDecimal as corresponsting java type is not an option for me).

推荐答案

这是基于Postgres驱动程序postgresql-9.4-1204-jdbc42.jar中类似行为的推测.

This is speculation based on similar behaviour in the Postgres driver postgresql-9.4-1204-jdbc42.jar.

对于未指定的NUMERIC，数据库似乎未存储有关列的精度和范围的任何特定信息.这允许数据库以看起来合适的任何方式在内部存储值.来自 https://www.postgresql.org/docs/current/static /datatype-numeric.html

For an unspecified NUMERIC the database doesn't seem to store any particular information on the precision and scale of the column. This allows the database to internally store the value in any way it seems fit. From https://www.postgresql.org/docs/current/static/datatype-numeric.html

不带任何精度或小数位数的列会创建一列，其中可以存储任何精度和小数位数的数值，但不超过精度的实现限制(小数点前最多131072位；小数点后最多16383位)

without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point)

由于 driver 不知道 server 的特定于实现的最大值，因此无法返回实际值.返回0表示它不知道实际值，也不想进行任何有根据的猜测.

Since the driver doesn't know what the implementation specific maximum of the server is, it can't return the actual values. It returns 0 to indicate that it doesn't know the actual values, and doesn't want to make any educated guesses.

似乎情况是与Oracle相同.最大精度可能更高，但可移植性只能保证不超过38位数字.

Seems like the situation is the same with Oracle. The max precision may be higher, but portability is guaranteed only up to 38 digits.

几乎任何数量的数字都可以存储，并保证在运行Oracle数据库的不同系统之间可移植，精度高达38位.

Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle Database, up to 38 digits of precision.

关于解决问题的方法，如StanislavL所示，您可以通过强制转换将值强制为特定的精度/小数位数.

As for solving the issue in the question, like StanislavL indicated you can force the value to a specific precision/scale by casting.

这篇关于由于分组函数，如何处理JDBC数字类型的精度损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！