ResultSet::getBigDecimal throws scale exception for joda-money

Question

Attempting to create a Joda-Money Money object from a BigDecimal read from a MySQL database throws an error.

This code:

PreparedStatement p_stmt = ...;
ResultSet results = ...;
Money amount = Money.of(CurrencyUnit.USD, results.getBigDecimal("amount"));

Throws this:

java.lang.ArithmeticException: Scale of amount 1.0000 is greater than the scale of the currency USD
    at org.joda.money.Money.of(Money.java:74)
    at core.DB.getMoney(DB.java:4821)

I have actually solved the error by adding rounding:

Money amount = Money.of(CurrencyUnit.USD, results.getBigDecimal("amount"), RoundingMode.HALF_UP);

But, I am leery of this solution. Is it the best?.

I am using DECIMAL(19,4) to store the money values on the DB as per this SO answer. Honestly, it kind of confused me why the answerer there called for 4 decimal place precision on the DB, but I tend to trust high-valued answers and I assume they know what they're talking about and that I'll regret not following their advice. Yet Joda-Money does not like 4 decimal place precision with US Currency. Maybe DECIMAL(19,4) was an international standard where they need 4 decimal place precision? Not sure..

To make the questions succinct:

1. Is RoundingMode.HALF_UP the ideal solution to solve this error?
2. Should I change the precision on the MySQL DB from DECIMAL(19,4) to DECIMAL(19,2)?
3. Is there a way to change the precision in Joda-Money? If yes: should I?

Answer 1

Joda Money and BigDecimal defines a 'scale' for each currency type.

The scale is the no. of decimal places the currency uses.

So USD has a scale of two (two decimals after the decimal place).

If you try to instantiate an Money instance for a USD currency from a source that has more than two decimal places then you will get a scale error. e.g.

20.99 is fine
20.991 throws an error.

Other currencies allow different numbers of decimal places.

According to the documentation RoundMode.UNNECESSARY will actually throw an exception if rounding is actually required.

e.g: Assuming USD which has a scale of 2 then 2 decimal places are expected.

Using:

Money money = Money.of(CurrencyUnit.USD, value, RoundingMode.UNNECESSARY);

value:

1.20 - works fine
1.200 - works fine as whilst extra digit rounding isn't necessary.
1.201 - throws an exception as rounding is necessary

Rounding and money are always a problem. My approach is to store the correct scale in the db

e.g.: if USD then DECIMAL(19,2)

Use RoundingMode.HALF_UP.

Be careful when doing multiplication and division. Doing multiplication before division will result in less rounding issues. If you round at the time you do the arithmetic you should be OK.

Answer 2

Yes, not a good solution. You are representing money, thus u cannot round up/down and loose a few cents :).

The problem is that the CurrencyUnit.USD has the number of digits to 2, e.g: 1.45 and what comes from the DB is something like 1.45343, perhaps, resulting in a difference in scale. Try to represent the money with BigMoney instead of Money.

BigMoney amount = BigMoney .of(CurrencyUnit.USD, results.getBigDecimal("amount"));

Answer 3

Had same issue and solve it by adding the rounding mode Unnecessary:

    @Test(dataProvider = "moneyValueProvider")
    public void testMoneyFromFloat(String currency, Float value) {
        CurrencyUnit cu = CurrencyUnit.of(currency);
        //Double dVal = Double.valueOf(value.toString());
        BigDecimal bigDecimal = new BigDecimal(value.toString());
        Money money = Money.of(cu, bigDecimal, RoundingMode.UNNECESSARY);
        Assert.assertNotNull(money);
        Assert.assertEquals(bigDecimal.doubleValue(), money.getAmount().doubleValue());
    }

    @DataProvider
    public static Object[][] moneyValueProvider() {
        return new Object[][] {
                    {"GBP", 22.5f},
                    {"GBP", 57.8f},
                    {"JPY", 15000.0f}

    };