Geek Answers Handbook

Hibernation and currency accuracy

I have a hibernation 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 insert into 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 adminFee type to double, and that didn't work either. The problem is that Hibernate actually inserted the insert correctly, and future selection operators work to return the current set of values, but this error prevents me from further processing this account.

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

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

+4
source share
5 answers

I would use an integer type (int, long). There are never any funny problems with how much you can avoid overflow.

+5
source

Using a float or double for money is completely unacceptable and can even be illegal (like breaking laws or at least rules). This must be fixed, then the error will disappear. float has limited precision and cannot accurately represent most decimals in general.

For money always, ALWAYS use BigDecimal.

+12
source

Have you tried to assign a type?

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

The problem seems to be related to the accuracy of the data. When you have 1.2666666666666 ... the value is truncated.

You can also try to get around it before trying to insert.

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

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

+1
source

I ended up not using float, BigDecimal or double. I returned to the client, and they agreed to refuse a penny, since the client is always charged with a rounded figure, so there is no need to store a penny. However, I still need to figure out how to properly store currencies, as this is likely to reappear.

0
source

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

0
source

More articles:


All Articles