-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 14 posts ] 
Author Message
 Post subject: MySQL, Hibernate and BigDecimal issue.
PostPosted: Fri Sep 24, 2004 4:05 pm 
Beginner
Beginner

Joined: Sun Aug 22, 2004 5:32 pm
Posts: 40
I was not able to find any info pertaining to the type of issue I am having.

I am trying to insert a java.math.BigDecimal (JDK 1.4.1) into a MySQL (4.0.21) database. An incorrect value is being inserted into the database for some reason.

Here is my mapping file code:

<property name="oppTcv" type="big_decimal">
<column name="OPP_TCV" not-null="false" unique="false"/>
</property>

<property name="oppCyr" type="big_decimal">
<column name="OPP_CYR" not-null="false" unique="false"/>
</property>

(Nothing special about that.....)

The mapping file creates a decimal(31,30) column in the database. I verified this by running the desc command on the table. This will allow for a very large number.

If I use a number smaller than 99.99 everything works fine, but if the number is larger than 99.99 (ie - 2156.45) Hibernate actually inserts 99.99999999999.......

Before sending the object containing my BigDecimal to the hibernate session, I can access the number with the BigDecimal abs() method and the number is correct . When I do an SQL query on the table after passing it to the hibernate session, the wrong value is in the database.

Has anyone ever seen this before, or know of a way to resolve this issue??? Thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 24, 2004 8:36 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
This is of course nothing to do with Hibernate, it is all at the JDBC level


Top
 Profile  
 
 Post subject: resolve
PostPosted: Fri Sep 24, 2004 10:15 pm 
Beginner
Beginner

Joined: Sun Aug 22, 2004 5:32 pm
Posts: 40
I am not sure I understand why you say it is a JDBC issue. Please explain.

What do I need to do to resolve this issue?


Top
 Profile  
 
 Post subject: change default value for BigDecimal
PostPosted: Sat Oct 09, 2004 9:05 pm 
Beginner
Beginner

Joined: Sun Aug 22, 2004 5:32 pm
Posts: 40
This is in fact an issue with hibernate. When I create the table manually and manually insert the value into the database with at the console, everything works fine.
For some reason hibernate is creating the table incorrectly:

Field Type Null Key Default Extra
--------- -------------- ------- ------ ---------- --------------
ID bigint(20) PRI (null) auto_increment
AMOUNT decimal(31,30) YES (null)
CASH char(1) YES (null)
DATE_TIME datetime YES (null)
PRO_ID bigint(20) YES (null)

If you look at the type AMOUNT row you will see that the type is decimal(31,30). Hibernate is setting this value. How can I change this so hibernate sets a different value??? Thanks.


Top
 Profile  
 
 Post subject: solution
PostPosted: Sat Oct 09, 2004 9:56 pm 
Beginner
Beginner

Joined: Sun Aug 22, 2004 5:32 pm
Posts: 40
Ahhh, the solution is just to change the mapping to contain the following:

<property name="cost" type="big_decimal">
<column sql-type="NUMERIC(19,2)"/>
</property>


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 29, 2004 2:01 pm 
Newbie

Joined: Mon Sep 08, 2003 10:06 am
Posts: 14
Following up on this question, I'm getting a similar problem where I insert a BigDecimal with a value of '100' into a field (created using the schemaexport task supplied with Hibernate).

The field's type in the database is decimal(31,30).

The mapping is:

Code:
        <property
            name="amount"
            type="java.math.BigDecimal"
            update="true"
            insert="true"
            access="property"
            column="q_amount"
        />


When I insert any value >= 100, the database stores it as 99.999999999999999999999999999999.

Both BigDecimal and decimal are fixed point types, so where is my number getting corrupted? Gavin's response below seems to indicate that JDBC turns the number into a floating point number somewhere along in the process, is there a way to work around this problem?

(Obviously you can reduce the number of decimal digits but I'm more interested in figuring out how to get JDBC to transmit the number precisely.)


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jan 02, 2005 4:28 pm 
Newbie

Joined: Mon Sep 08, 2003 10:06 am
Posts: 14
Clearly this is a JDBC issue, I get the same results with the following test:

Code:
        Class.forName("com.mysql.jdbc.Driver");
       
        String url = "jdbc:mysql://localhost/db";
       
        Connection con = DriverManager.getConnection(url, "user", "pass");
       
        BigDecimal num = new BigDecimal("1000");
       
        String statement = "insert into numero values (?)";
       
        PreparedStatement stmt = con.prepareStatement(statement);
       
        stmt.setBigDecimal(1, num);

        stmt.execute();


The value that's inserted is 99.999999999999999999999999999999. Still looking for a solution.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 03, 2005 10:54 am 
Newbie

Joined: Mon Sep 08, 2003 10:06 am
Posts: 14
Never mind this. The solution is to create the column with places behind the decimal. (The MySQL driver correctly inserts the value as a String so as long as the column is specified properly, you're fine.)


Top
 Profile  
 
 Post subject: try this: mapping bigdecimal to varchar using UserType
PostPosted: Thu Jan 13, 2005 4:57 am 
Newbie

Joined: Thu Jan 13, 2005 4:51 am
Posts: 2
i met the same problem and i map BigDecimal to varchar using UserType.
just replace big_decimal with this class's name in hibernate xml.
wish it helps.

Code:
public class MyBigDecimalType implements UserType {
        public int[] sqlTypes() {
             int[] typeList = {
                  Types.VARCHAR
                   };
                   return typeList;
        }
        public Class returnedClass() {       
            return BigDecimal.class;
        }
        public boolean equals(Object x, Object y) throws HibernateException {       
            return ((BigDecimal)x).equals(y);
        }
        public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
                throws HibernateException, SQLException {
             // Start by looking up the value name   
             String name = (String) Hibernate.STRING.nullSafeGet(rs, names[0]);   
             if (name == null) {   
             return null;   
             }   
             // Then find the corresponding enumeration value   
             try {   
             return new BigDecimal(name);   
             }   
             catch (java.util.NoSuchElementException e) {   
            throw new HibernateException("Bad BigDecimal value in UserTyped: " + name, e);   
           }
        }
        public void nullSafeSet(PreparedStatement st, Object value, int index)
                throws HibernateException, SQLException {
            String name = null;   
            if (value != null) {   
              name = value.toString();
            }
            Hibernate.STRING.nullSafeSet(st, name, index);
        }
        public Object deepCopy(Object value) throws HibernateException {
            return value;
        }
        public boolean isMutable() {
            return false;
        }
    }


Top
 Profile  
 
 Post subject: Re: MySQL, Hibernate and BigDecimal issue.
PostPosted: Thu Jan 13, 2005 9:43 am 
Senior
Senior

Joined: Tue Aug 03, 2004 2:11 pm
Posts: 142
Location: Somerset
hacktorious wrote:

The mapping file creates a decimal(31,30) column in the database. I verified this by running the desc command on the table. This will allow for a very large number.
.



This will not allow for a very large number. You have defined a column which can hold no more than 9.99999999999999999999999999999

A DECIMAL of 31,30 means a precision of 31 bytes , of which 30 are the AFTER the decimal point.

It's no wonder you are having problems.

_________________
On the information super B road


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 13, 2005 10:41 am 
Newbie

Joined: Mon Sep 08, 2003 10:06 am
Posts: 14
Yep, unfortunately that's how the schema generator creates them by default (I believe it actually tries to create a field of size 19,255). sql-type is definitely the answer here, although that costs you a bit of portability.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 13, 2005 2:07 pm 
Senior
Senior

Joined: Tue Aug 03, 2004 2:11 pm
Posts: 142
Location: Somerset
rafeco wrote:
Yep, unfortunately that's how the schema generator creates them by default (I believe it actually tries to create a field of size 19,255). sql-type is definitely the answer here, although that costs you a bit of portability.


What happens if you just speficy a length of say 30 for the property in the mapping file ? Will this just create a DECIMAL(30,0) on the schema export ?
Is there anyway hibernate can specify a number of decimal places for a decimal field, other than by using the sql-type (not that it matters much).

_________________
On the information super B road


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 13, 2005 3:33 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
sql-type is the answer.

in HB3, you can specify precision/scale in the mapping document


Top
 Profile  
 
 Post subject: Re: MySQL, Hibernate and BigDecimal issue.
PostPosted: Tue Jun 29, 2010 1:57 pm 
Newbie

Joined: Tue Jun 29, 2010 1:27 pm
Posts: 1
I am facing a similar issue, related to BigDecimal and hibernate mapping.

I have orcale 9i db and column is of type Number(23,8)

I have created the following mapping and when trying to persist 434343434345.29 following value is being persisted in the db
434343434345.28998400

<property name="myField" type="big_decimal">
<column name="myColumn" precision="23" scale="8"/>
</property>

I also tried the following mappings and results were same

<property name="myField" type="big_decimal">
<column name="myColumn" sql-type="NUMERIC(23,8)"/>
</property>

Could someone kindly suggest the resolution. A quick help is required.

Thanks,
-Chetan


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 14 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.