Muhammad Catubig Muhammad Catubig - 1 year ago 52
SQL Question

How to convert BigDecimal to Decimal properly?

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

with this code:

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

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

unitPrice.setScale(2, RoundingMode.HALF_EVEN)

and got a result like this
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


The column's data type in MySQL that I have set was

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 Source

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)