In 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:
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
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);
BigDeciaml result = new BigDecimal(100.0 / 3.0);