BigDecimal and JDBC since Java 5.0

java logoIn one of my projects I came across an interesting problem with BigDecimal values. The project uses a persistence framework to persist Java objects into an Oracle 9i database with the Oracle JDBC driver 10.2.0.3.0. I could also reproduce this problem with Hibernate on a Microsoft SQL Express 2005 database using the Microsoft JDBC driver 1.2.

The problem was that sometimes when I tried to store objects containing BigDecimal values the following exception was thrown and the object could not be persisted:

[2008-01-02 23:02:36] [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 8114, SQLState: S0005
[2008-01-02 23:02:36] [main] ERROR org.hibernate.util.JDBCExceptionReporter - Error converting data type nvarchar to decimal.

Converting nvarchar to decimal? I am trying to insert a number into the database and the JDBC driver tells me he is not able to convert a nvarchar (text) to a decimal?

After doing some search I found out that this problem might have something to do with a change made in Java 5.0. I found this blog post and this article writing about similar errors. The implementation of BigDecimal.toString() changed in Java 5.0 and there is a new method toPlainString() doing what toString() did before.

The following code example

System.out.println(new Double(12500000).toString());
final BigDecimal value = new BigDecimal(new Double(12500000).toString());
System.out.println(value.toString());
System.out.println(new BigDecimal(value.toPlainString()).toString());

executed with Java 5.0 results in the following console output:

1.25E7
1.25E+7
12500000

Whereas executed with Java 1.4 it generates another output:

1.25E7
12500000

Some JDBC driver use the toString() method to get the value of a BigDecimal and if it has to many decimal positions you may run into this problem.

In my case the solution was to do all the calculations with BigDecimal values and given MathContext and RoundingMode instead of creating BigDecimal values out of Double values from calculations.

BigDecimal value = new BigDecimal("100");
BigDeciaml result = value.divide(new BigDecimal("3"), 2,
                      BigDecimal.ROUND_HALF_UP);

instead of

BigDeciaml result = new BigDecimal(100.0 / 3.0);

Leave a Reply