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.

Solution 1

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

Solution 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.

Solution 3

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/

Comments

[duffymo]

If you use the most basic, quantized unit (e.g., yen for Japanese money, cents for US), you can avoid fractions altogether.

[Elie]

Tried using BigDecimal... all my math got screwed up, and now it only saves 0 in all the fields.

[Elie]

actually, they are more likely to have a long underflow.

[Elie]

Oh, and there are no legal issues with this particular application, the numbers get rounded in the end to the next largest integer, but they want it saved exactly. The customers are aware of this.

[Elie]

But with what types? What does numeric(5,2) map to in Java types?