Accounting with EJB3, JPA-QL, and Databases

Just an update post on what I have been working on. I have been working on EJB3.

I started learning/using EJB3 back in Oct '05 and deployed an application based on jboss EJB3-embedded (actually, it was just the hibernate/JPA persistence side of it).

New project has allowed me to jump back into EJB3 and glad to see there is still some momentum. The persistence side (JPA) is working very well for me. The concept of using java objects through O/R mapping to deal with database structures is wonderful (and significantly improved since my days of working with EJB1.1). The new JPA-QL query enchancements over the old EJB-QL are great.

However, one problem still isn't solved completely - working with Monetary figures. I'm working on an application that needs to do financial calculations. Naturally, I'm trying to use java-side types of BigDecimal so I can set the precision/scale to only have two numbers after the decimal. On the application side, this works great. Store the data as database fieldtypes of Decimal with the same precision/scale also works great.

That's when I ran into big problems. Doing calculations on those values through a query was not working correctly. When asking the java object its value was returning values of not the correct scale.

JPA-QL statement:

• "select node from Balances node where (node.totalPaid - node.tax - node.price != 0)"

would give incorrect results. Why incorrect results? Floating point optimizations.

Floating point optimizations - computer hardware and underlying operating systems will take shortcuts to improve the Floating Point calculation performance. These are also called single-precision Floating Point calculations. The results of using these kinds of calculations can turn something simple like '58.11' to actually be '58.10997856788934...'. Big Problem.

Solutions:

• Hardware/OS enable of double-precision floating point calculation (usually requiring Xeon-type processors). Specifically, on the database server that is running these queries (and on the application server would not hurt).

• Application-level IEEE-754 Floating Point calculations - i.e., get all the record results from the database and have the application itself do controlled calculations (in Java, strictfp keyword or use BigDecimal java.math functions).

• Carefully choose/configure your database server on how it handles Decimal-field calculations.

I went with the 3rd option once I understood where my problem was coming from. I was developing on Mysql 4.1 which, even though it is storing the Decimal field with the correct precision, running queries with those fields ran with the Floating Point problem. Mysql > 5.0.3 corrected this problem, as well as testing on Postgresql 8.1.1 also corrected this problem.

As for asking for a specific field value, I was still having problems. In EJB3, you can set a BigDecimal with this annoation for persistence:

• @javax.persistence.Column(precision=8, scale=2)

As of Jboss EJB3 RC9, this only helps when saving the data to the database (i.e. the value stored in the database is correct), but not when displaying a retrieved value. This goes into how the underlying JDBC driver works with the persistence engine and how JDBC does things like 'getObject' that may return something different than expected.

Solutions:

• Carefully choose JDBC driver/persistence engine for BigDecimal support (i.e. getObject returns double or BigDecimal, instead of float).

• Handle the returned data on your getter (re-set the BigDecimal scale and have any UI display the BigDecimal as a formatted string).

As you can see, financial applications you need to be very careful about the Floating Point Optimization problem, and requires careful diligence across the entire stack - hardware, OS, database, and application.


6/20/2011 update: http://www.codeproject.com/Tips/50340/MSSQL-doesn-t-completely-support-IEEE754-floating-.aspx