CODEWITHSUNDEEP
javamysqldecimalbigdecimal
Ankur
Ankur

Reputation: 51100

When I insert 78.9 into Mysql (using JDBC) it gets rounded up to 79? Is this normal

When I insert 78.9 into Mysql (using JDBC) it gets rounded up to 79? Is this normal ... if so how can I stop this from happening.

More details:

Column name: num Data type: decimal(12,0) * The above item was copied from phpMyAdmin

Query is

stmt.executeUpdate("INSERT INTO triples(sub_id, pro_id, num) VALUES("+subId+","+proId+",78.9)");

Ideally I would use a variable instead of the hard-coded 78.9

Such as 

        BigDecimal obj = new BigDecimal(78.9);

Upvotes: 2

Views: 1709

Answers (2)

Martin
Martin

Reputation: 10563

You need to set the datatype of the column you are inserting into as 

float(x,y)

or

decimal(x,y)

where x is the total number of digits and y is the total number of decimals.

e.g. float(5,2)  -> 325.46
     decimal(10,5) -> 42579.12345

Upvotes: 1

AHungerArtist
AHungerArtist

Reputation: 9579

It's normal because using the 0 in decimal(12, 0) essentially says there should be nothing after the decimal, so it rounds up. You need to specify the scale to get what you're looking for. For example, decimal(12,5) would allow 7 numbers to the left of the decimal and five to the right. 

The declaration syntax for a DECIMAL column is DECIMAL(M,D). 
The ranges of values for the arguments in MySQL 5.1 are as follows:

M is the maximum number of digits (the precision). 
It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

D is the number of digits to the right of the decimal point (the scale). 
It has a range of 0 to 30 and must be no larger than M.

See: http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Upvotes: 5

Related Questions