Montag, 27. Juni 2011

java.math.BigDecimal the difference between setScale and round

One pitfall within BigDecimal I guess is the round method within BigDecimal. Lets assume the following code samples:

BigDecimal smallNumber = new BigDecimal("0.01234");
smallNumber.round(new MathContext(2, RoundingMode.HALF_UP)));
Result   = 0.012
smallNumber.setScale(2, RoundingMode.HALF_UP));
Result   = 0.01

BigDecimal number = new BigDecimal("1.01234");
number.round(new MathContext(2, RoundingMode.HALF_UP)));
Result   = 1.0
number.setScale(2, RoundingMode.HALF_UP));
Result   = 1.01

So calling round with a MathContext of 2 doesn't alway round to a precision of 2 digits overall. It depends on the number you're trying to round. Reading the JDK docs you will find

".... In general the rounding modes and precision setting determine how operations return results with a limited number of digits when the exact result has more digits (perhaps infinitely many in the case of division) than the number of digits returned. First, the total number of digits to return is specified by the MathContext's precision setting; this determines the result's precision. The digit count starts from the leftmost nonzero digit of the exact result. The rounding mode determines how any discarded trailing digits affect the returned result. ..."

Oops, that is something you have to keep in mind when using round within BigDecimal. So if you really want to achieve a "real" rounding operation on a BigDecimal you should use setScale.

Mittwoch, 15. Juni 2011

JOIN ON multiple conditions

If you want to write an sql join on more than one condition you have to keep in mind that the from clause could just have one condition. Example:

SELECT  * FROM person p, accounts a
LEFT JOIN preferences  pref ON pref.id = p.pref_id AND pref.name = 'glue'

will fail, but this

SELECT  * FROM person p
LEFT JOIN preferences  pref ON pref.id = p.pref_id AND pref.name = 'glue'
LEFT JOIN accounts a   ON a.id = p.account_id AND a.name = 'snoop'

will work.