Note: This could be running with SQL Server, Oracle, DB2, or MySQL database. But for this example I am just using SQL Server. The solution needs to be generic enough to apply all mentioned databases though.
注意:这可以与SQL Server,Oracle,DB2或MySQL数据库一起运行。但是对于这个例子,我只是使用SQL Server。该解决方案需要足够通用,以应用所有提到的数据库。
In SQL Server I have a column defined as decimal(32, 16), because that column must be able to store potentially large or precise values. However, it may also store small or imprecise values. But when I select the BigDecimal it is returned with trailing zeros that were not in the original. I assume this is because of my column definition allowing for the high precision.
在SQL Server中,我有一个定义为十进制(32,16)的列,因为该列必须能够存储可能很大或精确的值。但是,它也可能存储小的或不精确的值。但是当我选择BigDecimal时,它返回的尾随零不是原始的。我认为这是因为我的列定义允许高精度。
I do not want any values to be stored or returned with "invented" scale/precision, but I still need to allow for that large scale/precision.
我不希望以“发明的”比例/精度存储或返回任何值,但我仍然需要允许那么大的比例/精度。
// ... set up ...
// precision 2, scale 2
BigDecimal bdInsert = new BigDecimal("0.51");
preparedStatement.insertBigDecimal(1, bdInsert);
// ... insert/commit ... select ...
// precision 0, scale 16 <-- Grrrrrrr!
BigDecimal bdResult = resultSet.getBigDecimal(1);
System.out.println(bdResult);
// prints "0.5100000000000000" <-- Grrrrrrr!
Does SQL Server not store, or JDBC not send nor select, the precision and scale of the inserted BigDecimal?
SQL Server是否不存储或JDBC不发送也不选择插入的BigDecimal的精度和比例?
I can't just go truncating the zeros because it is possible that a BigDecimal was stored with trailing zeros to indicate that it is precise.
我不能只是截断零,因为BigDecimal可能存储有尾随零以表示它是精确的。
// "0.510000" indicating that this value is accurate to the 6th place
BigDecimal bdPrecise = new BigDecimal("0.510000");
preparedStatement.insertBigDecimal(1, bdPrecise);
// precision 0, scale 16
BigDecimal bdResult = resultSet.getBigDecimal(1);
System.out.println(bdResult);
// prints "0.5100000000000000" loses information
System.out.println(bdResult.stripTrailingZeros());
// prints "0.51", loses information
// What I want is "0.510000"
Is there a way, either through SQL Server or JDBC to get the scale/precision of the original BigDecimal?
有没有办法,通过SQL Server或JDBC来获得原始BigDecimal的规模/精度?
Please don't tell me I would have to make a separate column to store this information...
请不要告诉我,我必须单独列出来存储这些信息......
I guess an alternative would be to just insert/select the BigDecimals as Strings then parse it from the String like new BigDecimal(val). But I would prefer storing as a decimal.
我想另一种方法是只需插入/选择BigDecimals作为字符串然后从字符串中解析它就像新的BigDecimal(val)一样。但我更喜欢存储为小数。
Thanks.
1 个解决方案
#1
1
The following code demonstrates the sql_variant data type in SQL Server. (And how to display data type information about an expression.) Since the solution needs to apply to anything capable of holding bits it won't address all of your issues, but it may be of some value.
以下代码演示SQL Server中的sql_variant数据类型。 (以及如何显示有关表达式的数据类型信息。)由于解决方案需要应用于任何能够保留位的内容,因此它不会解决您的所有问题,但它可能具有一定的价值。
I suspect that the only solution that will work across "all" platforms is a string, possibly containing XML with an explicit precision.
我怀疑在“所有”平台上工作的唯一解决方案是一个字符串,可能包含具有明确精度的XML。
declare @Samples as Table ( Value sql_variant );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 2 ) ) );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 3 ) ) );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 4 ) ) );
insert into @Samples ( Value ) values
-- NB: Both values are inserted with scale 5 when they are included in a single INSERT.
( Cast( 42 as Decimal( 10, 1 ) ) ),
( Cast( 42 as Decimal( 10, 5 ) ) );
insert into @Samples ( Value ) values
( Cast( 1.618 as Decimal( 10, 6 ) ) );
insert into @Samples ( Value ) values
( 'Edgar' );
select Value, SQL_Variant_Property( Value, 'BaseType' ) as BaseType,
SQL_Variant_Property( Value, 'Precision' ) as Precision,
SQL_Variant_Property( Value, 'Scale' ) as Scale
from @Samples;
(Then there was the steel mill where they wanted a dot between the inches and sixteenths, e.g. 23 3/4 inches was displayed as "23.12". Handling "36.1" and "36.10" as differing by 9/16 on input and output? Oy.)
(然后是钢厂他们想要一英寸到十六分之间的点,例如23 3/4英寸显示为“23.12”。输入和输出处理“36.1”和“36.10”的差异为9/16? Oy公司。)
#1
1
The following code demonstrates the sql_variant data type in SQL Server. (And how to display data type information about an expression.) Since the solution needs to apply to anything capable of holding bits it won't address all of your issues, but it may be of some value.
以下代码演示SQL Server中的sql_variant数据类型。 (以及如何显示有关表达式的数据类型信息。)由于解决方案需要应用于任何能够保留位的内容,因此它不会解决您的所有问题,但它可能具有一定的价值。
I suspect that the only solution that will work across "all" platforms is a string, possibly containing XML with an explicit precision.
我怀疑在“所有”平台上工作的唯一解决方案是一个字符串,可能包含具有明确精度的XML。
declare @Samples as Table ( Value sql_variant );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 2 ) ) );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 3 ) ) );
insert into @Samples ( Value ) values
( Cast( 3.14 as Decimal( 10, 4 ) ) );
insert into @Samples ( Value ) values
-- NB: Both values are inserted with scale 5 when they are included in a single INSERT.
( Cast( 42 as Decimal( 10, 1 ) ) ),
( Cast( 42 as Decimal( 10, 5 ) ) );
insert into @Samples ( Value ) values
( Cast( 1.618 as Decimal( 10, 6 ) ) );
insert into @Samples ( Value ) values
( 'Edgar' );
select Value, SQL_Variant_Property( Value, 'BaseType' ) as BaseType,
SQL_Variant_Property( Value, 'Precision' ) as Precision,
SQL_Variant_Property( Value, 'Scale' ) as Scale
from @Samples;
(Then there was the steel mill where they wanted a dot between the inches and sixteenths, e.g. 23 3/4 inches was displayed as "23.12". Handling "36.1" and "36.10" as differing by 9/16 on input and output? Oy.)
(然后是钢厂他们想要一英寸到十六分之间的点,例如23 3/4英寸显示为“23.12”。输入和输出处理“36.1”和“36.10”的差异为9/16? Oy公司。)