www.rolfje.com

Yesterday a collegue of mine discovered a very ugly problem when storing BigDecimals from a Java 1.5 program into an Oracle 10g database. Not by experience, but by reading the article at http://www.javalobby.org/java/forums/t88158.html. We had not encountered the problem ourselves, but when passing the dredded 12500000 value to the BigDecimal constructor and passing it to the database, we discovered that we too were affected by this bug. So off I went to search a solution.

The root of the problem lies not only in the fact that the BigDecimal.toString() method behaviour has changed in Java 1.5, but also the way BigDecimals are constructed. The Oracle driver relies on a specific formatting of the BigDecimal.toString() method, and can not handle the output of the “new and improved” BigDecimal in Java 1.5. All discussions about not using toString() for passing values and flaming Oracle and/or Sun aside, we have no option but to fix the problem right now. Oracle promised to fix this problem not sooner than in release 11g, so waiting is not an option.

Option 1: Overriding BigDecimal.toString()
In our project, we use Ibatis. This gives us a nice handle to implement our own BigDecimalTypeHandler, where we can choose to do anything to the BigDecimal before storing it into the database, and revert that when retrieving it from the databse. One of the options we thought of was to override the BigDecimal’s toString in an anonymous subclass inside our IbatisTypeHandler, like so:

public void setParameter(PreparedStatement ps, int i,
                        Object parameter, String jdbcType)
                        throws SQLException {
  BigDecimal colVal = new BigDecimal(
              ((BigDecimal)parameter).toPlainString()){
    public String toString(){
      return toPlainString();
    }
  };
  ps.setBigDecimal(i, colVal);
}

The anonymous subclass lets you add or change behaviour to an existing class without cluttering your codebase with a classfile which looks rather strange when places out of context. We tried various ways of locally changing the behaviour of BigDecimal to make it go into and out of the database in a way that our unittest would not know the difference, but at some point gave up. There is not really a good way to do this without compromising in scale or resolution in the conversion.

When using the setParameter described above, you will not get a situation where originalBigDecimal.equals(storedBigDecimal) returns true. In most cases this will not be a very big problem, since originalBigDecimal.subtract(storedBigDecimal) will always return 0.

If somebody thinks of a way to store BigDecimals into the database and retrieving them in a way it is totally transparent to the caller (compared to JDK 1.4 behaviour) please feel free to post it here.

Option 2: Loosing your BigDecimals

Perhaps this option had to be considered when thinking of using BigDecimals in the first place. BigDecimals are very awkward to calculate with, are relatively heavy both performance and memory wise. Very often they get converted to doubles before doing any calculations with them, which eliminates most of the reasons for using BigDecimals anyway.

The easy way to loose only those BigDecimals which get stored in the Database, rewrite the typeHandler you just created into the following:

public void setParameter(PreparedStatement ps, int i,
                        Object parameter, String jdbcType)
                        throws SQLException {
  throw new UnsupportedOperationException(
    "Oracle can not handle Java 5 BigDecimals properly");
}

Now, run all Unittest (you have unittests covering at least 80% of your software, don’t you?) and replace BigDecimals with other appropriate datatypes where you see the UnsupportedOperation popup.