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

Question

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);

Answer 1

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

Answer 2

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