Muhammad Catubig Muhammad Catubig - 5 months ago 9
SQL Question

How to convert BigDecimal to Decimal properly?

I am trying to convert a double value e.g.

12.55
to
BigDecimal
with this code:

BigDecimal unitPrice = new BigDecimal(product.getUnitPrice());


As a result, I got something like this
12.550000000000000710542735760100185871124267578125
then I have to rounded it off with this code:

unitPrice.setScale(2, RoundingMode.HALF_EVEN)


and got a result like this
12.55
which is fine. However, when I'm going to set this as a parameter for inserting to MySQL database with this code:

addProduct.setBigDecimal(4, unitPrice.setScale(2, RoundingMode.HALF_EVEN));


So when I run it, I got this SQL Error:

Data truncation: Out of range value for column 'price_per_unit' at row 2


I know that the problem was in this code:

addProduct.setBigDecimal(4, unitPrice.setScale(2, RoundingMode.HALF_EVEN));


as I have debugged it at this line of code, the value was

12.550000000000000710542735760100185871124267578125


The column's data type in MySQL that I have set was
DECIMAL(3,2)
.

How come it is not working even when I rounded it off? I have tried searching for a solution and cannot find anything relevant. How do you really set the decimal properly? I assume that you know what I'm trying to do. Thanks in advance.

Answer

Decimal (3,2) means the number has a total of three digits, one to the left and two to the right. 12.55 does not fit into this. Was you intention to store three to the left? It would be decimal(5,2)