Hibernate and currency precision

Question

I have a hibernate mapping as follows:

<hibernate-mapping>
 <class name="kochman.elie.data.types.InvoiceTVO" table="INVOICE">
  <id name="id" column="ID">
   <generator class="increment"/>
  </id>
  <property name="date" column="INVOICE_DATE"/>
  <property name="customerId" column="CUSTOMER_ID"/>
  <property name="schoolId" column="SCHOOL_ID"/>
  <property name="bookFee" column="BOOK_FEE"/>
  <property name="adminFee" column="ADMIN_FEE"/>
  <property name="totalFee" column="TOTAL_FEE"/>
 </class>
</hibernate-mapping>

where InvoiceTVO has variables defined as:

private int id;
private Date date;
private int customerId;
private int schoolId;
private float bookFee;
private float adminFee;
private float totalFee;

When I do an insert on this table, I get the following error:

Hibernate: insert into INVOICE (INVOICE_DATE, CUSTOMER_ID, SCHOOL_ID, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, ID) values (?, ?, ?, ?, ?, ?, ?)
50156 [AWT-EventQueue-0] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 22001
50156 [AWT-EventQueue-0] ERROR org.hibernate.util.JDBCExceptionReporter - Data truncation: Data truncated for column 'ADMIN_FEE' at row 1
50156 [AWT-EventQueue-0] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session

I tried changing the type of adminFee to double, and that didn't work either. The problem is, Hibernate did actually perform the insert properly, and future select statements work to return the current set of values, but this error prevents me from doing further processing on this invoice.

The fields bookFee, adminFee, and totalFee are all supposed to be currencies. They are defined in the MySQL database as decimal(5,2).

Any help in how to resolve this would be greatly appreciated.

Answer 1

Have you tried assigning the type?

<property name="adminFee" column="ADMIN_FEE" type="float"/>

It seems the problem is with the precision of the data. Sometime when you have 1.26666666666... the value is truncated.

You can also try to round it before attempting the insert.

See: http://bugs.mysql.com/bug.php?id=7829

Also, see if this helps: http://www.ibm.com/developerworks/java/library/j-jtp0114/

Answer 2

Using float or double for money is absolutely inacceptable and may even be illegal (as in, breaking laws or at least regulations). That needs to be fixed, then the error will disappear. float has limited precision and cannot accurately represent most decimal fractions at all.

For money, always, ALWAYS use BigDecimal.

Answer 3

I would use an integer type (int, long). Never any funny issues with those as long you can avoid overflows.

Answer 4

I ended up not using float, BigDecimal, or double. I went back to the client, and they agreed to drop the pennies, since the customer is always charged the rounded figure anyways, so there's no need to store the pennies. However, I will still need to figure out how to store the currencies properly, since this will likely come up again.

Answer 5

A custom Hibernate mapping and a class definition would be a good way to go.