Numbers in Databases
Most databases support very large and very precise numbers. For instance, MySQL supports decimal numbers up to 65 digits:
create table HugeNumbers( ident int primary key, huge_number decimal(65,20) ) ENGINE=InnoDB;
In this example table, very large numbers and numbers with a lot of precision are stored, with up to 45 digits before the decimal point, and up to 20 digits after:
insert into HugeNumbers (ident, huge_number) values (1, 123456789012345678901234567890123456789012345.99999999999999999999);insert into HugeNumbers (ident, huge_number) values (2, 123456789012345678901234567890123456789012345.99999999999999999998);insert into HugeNumbers (ident, huge_number) values (3, 0.00000000000000000001);
For example, if you retrieve the value for row 1, the resulting number might not be what you might have expected:
out.println("huge_number is: " + row.huge_number);// Output: huge_number is: 1.2345678901234567e+44
Use the Underlying Java Number Types
The following is a simple example:
var huge_number = row.getRawObject("huge_number");out.println("huge_number is now: " + huge_number);// Output: huge_number is now: 123456789012345678901234567890123456789012345.99999999999999999999
getRawObject()method, it will be represented as a
BigDecimalobject. If it is defined as an integer type, it will be represented as a java.math.BigInteger number.
To avoid any precision loss, you must do all operations on these number objects using the methods that are defined on the underlying Java class, for example:
var huge = row.getRawObject("huge_number");var BigDecimal = Java.type("java.math.BigDecimal");var multiplier = new BigDecimal("1.1234567890123456789");var result = huge.multiply(multiplier);out.println("Multiplication result: " + result);// Output: Multiplication result: 138698367765584515651577503665157750366515775.397392165471851699388765432109876543211
The Dark Side of Precision
Unfortunately, precision is never infinite. In the previous example, we multiplied two numbers together and got an exact result, with all 39 decimal digits. But you cannot store that number with full precision in the database, because in our example, we only specified 20 decimal digits. Therefore there will still be a loss of precision if you store this result in the database. This is unavoidable.
In addition, you cannot store numbers such as 10/3 with perfect precision, no matter how many decimals you specify. Once again, there will be a loss of precision that cannot be avoided.
When calling your