oracle BigDecimal 到 SQL NUMBER：检查大于精度的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3772750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BigDecimal to SQL NUMBER: check for value larger than precision
提问by mdrg
In my app, I handle numbers as BigDecimal and store them as NUMBER(15,5). Now I'd need to properly check on Java if the BigDecimal values would fit the column, so that I can generate proper error messages without executing the SQL, catching exceptions and verifying the vendor error code. My database is Oracle 10.3, and such errors cause error 1438.
在我的应用程序中，我将数字作为 BigDecimal 处理并将它们存储为 NUMBER(15,5)。现在我需要在 Java 上正确检查 BigDecimal 值是否适合该列，以便我可以生成正确的错误消息而无需执行 SQL、捕获异常和验证供应商错误代码。我的数据库是 Oracle 10.3，此类错误导致错误 1438。
After some googling, I found no such code for that, so I came up with my own. But I'm really unsatisfied with this code... simple, but at the same time simple enough to doubt its correctness. I tested it with many values, random ones and boundaries, and it seems to work. But as I'm really bad with numbers, I'd like some more robust and well-tested code.
经过一番谷歌搜索，我没有找到这样的代码，所以我想出了自己的代码。但是我对这段代码真的很不满意……很简单，但同时又简单到足以怀疑它的正确性。我用许多值、随机值和边界对其进行了测试，它似乎有效。但由于我对数字很不擅长，我想要一些更健壮和经过良好测试的代码。
//no constants for easier reading
public boolean testBigDecimal(BigDecimal value) {
if (value.scale() > 5)
return false;
else if (value.precision() - value.scale() > 15 - 5)
return false;
else
return true;
}
Edit: Recent tests did not got an exception for numbers out of scale, just got silently rounded, and I'm not sure what is different between not and when I made these first tests. Such rounding is unacceptable because the application is financial, and any rounding/truncation must be explicit (through BigDecimal methods). Exception-is-gone aside, this test method must assure that the number is not too large for the desired precision, even if by non-significant digits. Sorry about the late clarification.
编辑：最近的测试没有出现超出比例的数字的异常，只是默默地四舍五入，我不确定没有和我进行这些第一次测试时有什么不同。这种舍入是不可接受的，因为应用程序是财务应用程序，并且任何舍入/截断都必须是明确的（通过 BigDecimal 方法）。抛开例外不谈，此测试方法必须确保数字对于所需的精度不会太大，即使是非有效数字也是如此。很抱歉迟到的澄清。
Thanks for your time.
谢谢你的时间。
I'm still curious about this question. My code is still running, and I haven't got some "proof" of correctness or fail situation, or some standard code for this kind of test.
这个问题我还是很好奇的。我的代码仍在运行，我还没有得到正确性或失败情况的一些“证明”，或者这种测试的一些标准代码。
So, I'm putting a bounty on it, hopefully getting any of these.
所以，我悬赏它，希望能得到其中的任何一个。
采纳答案by mdrg
Well, since nobody came up with another solution, I'm leaving the code as it is.
好吧，既然没有人想出另一个解决方案，我就保留代码原样。
I couldn't make this precision/scale test fail, and it always matched the regex solution, so maybe both are correct (I tested the boundaries and with over 5M randomly generated values). I'll use the precision/scale solution, as it is over 85% faster, and may it fail I replace it.
我不能让这个精度/比例测试失败，它总是与正则表达式解决方案匹配，所以也许两者都是正确的（我测试了边界和超过 5M 随机生成的值）。我将使用精度/比例解决方案，因为它的速度提高了 85% 以上，并且可能会失败我更换它。
Thanks for your replies Tony.
感谢您的回复托尼。
My previous "answer", still here for history purposes, but I'm looking for a real answer =)
我之前的“答案”，出于历史目的仍然在这里，但我正在寻找一个真正的答案 =)
回答by Tony Ennis
The following regexp would do the trick too:
以下正则表达式也可以解决问题：
public class Big {
private static final Pattern p = Pattern.compile("[0-9]{0,10}(\.[0-9]{0,5}){0,1}");
public static void main(String[] args) {
BigDecimal b = new BigDecimal("123123.12321");
Matcher m = p.matcher(b.toString());
System.out.println(b.toString() + " is valid = " + m.matches());
}
}
This could be another way to test your code or it could bethe code. The regexp requires between 0 and 10 digits optionally followed by a decimal point and 0 to 5 more digits. I didn't know if a sign was needed or not, as I think about it. Tacking something like [+-]{0,1}to the front will do.
这可能是测试代码的另一种方式，也可能是代码。正则表达式需要 0 到 10 位数字，可选地后跟小数点和 0 到 5 位数字。我不知道是否需要一个标志，就像我想的那样。把类似的东西[+-]{0,1}放在前面就行了。
Here is a better class, maybe, and a test class with a partial set of tests.
这是一个更好的类，也许是一个带有部分测试集的测试类。
public class Big {
private static final Pattern p = Pattern.compile("[0-9]{0,10}(\.[0-9]{0,5}){0,1}");
public static boolean isValid(String s) {
BigDecimal b = new BigDecimal(s);
Matcher m = p.matcher(b.toPlainString());
return m.matches();
}
}
package thop;
import junit.framework.TestCase;
/**
* Created by IntelliJ IDEA.
* User: tonyennis
* Date: Sep 22, 2010
* Time: 6:01:15 PM
* To change this template use File | Settings | File Templates.
*/
public class BigTest extends TestCase {
public void testZero1() {
assertTrue(Big.isValid("0"));
}
public void testZero2() {
assertTrue(Big.isValid("0."));
}
public void testZero3() {
assertTrue(Big.isValid("0.0"));
}
public void testZero4() {
assertTrue(Big.isValid(".0"));
}
public void testTooMuchLeftSide() {
assertFalse(Big.isValid("12345678901.0"));
}
public void testMaxLeftSide() {
assertTrue(Big.isValid("1234567890.0"));
}
public void testMaxLeftSide2() {
assertTrue(Big.isValid("000001234567890.0"));
}
public void testTooMuchScale() {
assertFalse(Big.isValid("0.123456"));
}
public void testScientificNotation1() {
assertTrue(Big.isValid("123.45e-1"));
}
public void testScientificNotation2() {
assertTrue(Big.isValid("12e4"));
}
}
回答by Vincent Malgrat
one of the problems with your function is that in some cases it may be too restrictive, consider:
您的函数的问题之一是在某些情况下它可能过于严格，请考虑：
BigDecimal a = new BigDecimal("0.000005"); /* scale 6 */
a = a.multiply(new BigDecimal("2")); /* 0.000010 */
return testBigDecimal(a); /* returns false */
As you can see, the scale is not adjusted down. I can't test right now if something similar happens with high-end precision (1e11/2).
如您所见，比例并未向下调整。如果高端精度 (1e11/2) 发生类似情况，我现在无法测试。
I would suggest a more direct route:
我建议更直接的路线：
public boolean testBigDecimal(BigDecimal value) {
BigDecimal sqlScale = new BigDecimal(100000);
BigDecimal sqlPrecision = new BigDecimal("10000000000");
/* check that value * 1e5 is an integer */
if (value.multiply(sqlScale)
.compareTo(value.multiply(sqlScale)
.setScale(0,BigDecimal.ROUND_UP)) != 0)
return false;
/* check that |value| < 1e10 */
else if (value.abs().compareTo(sqlPrecision) >= 0)
return false;
else
return true;
}
Update
更新
You've asked in a comment if the database would throw an error if we try to insert 0.000010. In fact the database will never throw an error if you try to insert a value with too much precision, it will silently round the inserted value.
您在评论中询问如果我们尝试插入 0.000010，数据库是否会抛出错误。事实上，如果您尝试插入一个太精确的值，数据库永远不会抛出错误，它会默默地舍入插入的值。
The first check is therefore not needed to avoid an Oracle error, I was assuming that you were performing this test to make sure that the value you want to insert is equal to the value you actually inserted. Since 0.000010 and 0.00001 are equal (with BigDecimal.compareTo) shouldn't they both return the same result?
因此不需要第一次检查来避免 Oracle 错误，我假设您正在执行此测试以确保您要插入的值等于您实际插入的值。由于 0.000010 和 0.00001 相等（与BigDecimal.compareTo）它们不应该都返回相同的结果吗？
回答by Tony Ennis
Instead if looping over thousands of random numbers, you could write test cases that stress the 'edges' - the maximum value +.00001, the maximum value, the maximum value - .00001, 0, null, the minimum value -.00001, the minimum value, the minimum value + .00001, and values with 4, 5, and 6 values to the right of the decimal point. There are probably many more.
相反，如果循环数以千计的随机数，您可以编写强调“边缘”的测试用例 - 最大值 +.00001、最大值、最大值 - .00001、0、null、最小值 -.00001、最小值、最小值 + .00001 以及小数点右侧具有 4、5 和 6 个值的值。可能还有更多。
If you have those in junit, you're good.
如果你在 junit 中有那些，那你就很好。
