MySQL/Java Calculation output has an issue

Question

I have the following JAVA code where I calculate a customer due amount

Double invsubtot = 0.0;
            Double paidtot = 0.0;
            Double duetot = 0.0;
            try {
                ResultSet invoicesubtotal = InfoIMS.DbConnection.searchResult("select invoice_subtotal from invoice where customer_id = '69' and payment_type = 'Credit' and invoice_status = '1' group by invoice_number");
                while (invoicesubtotal.next()) {

                    invsubtot = invsubtot + invoicesubtotal.getDouble(1);

                }
                // customerduejLabel5.setText(String.valueOf(invsubtot));

                ResultSet paidtotal = InfoIMS.DbConnection.searchResult("select sum(payment) from payments where cusid = '69' and ingrtype='1' and payment_status = '0'");
                while (paidtotal.next()) {

                    paidtot = paidtotal.getDouble(1);

                }
                // customerduejLabel5.setText(String.valueOf(paidtot));
                duetot = invsubtot - paidtot;
                customerduejLabel5.setText("Customer Due : " + String.valueOf(duetot));

But the Output result is Customer Due : 6999.999999999999

But the real values of the first & second MySql queries are as follows

1. invoice_subtotal = 13891.30
2. sum(payment) = 6891.30

So it should be giving me the output as Customer Due : 7000 But Why is it giving me 6999.999...? Any way to solve this?

And I am not satisfied with the indirect answers given in Why do I see a double variable initialized to some value like 21.4 as 21.399999618530273? Where it shows why it happens but not how to avoid it clearly

Check out the `BigDecimal` class to get around this problem, it wasn't very clear in the answers to the other question. — Keppil, Jul 21 '14 at 05:54
@Keppil I am not satisfied with the indirect answers given in "Why do I see a double variable initialized to some value like 21.4 as 21.399999618530273?" Where it shows why it happens but not how to avoid it clearly, Please REMOVE the duplication FLAG — Dilukshan Mahendra, Jul 21 '14 at 06:06
You are right, I could have chosen a better question. This question is however an almost exact duplicate of http://stackoverflow.com/questions/5257166/java-floats-and-doubles-how-to-avoid-that-0-0-0-1-0-1-0-9000001. — Keppil, Jul 21 '14 at 06:51
Rule #1: Don't use floating point for money. Rule #2: You could do this entire computation in SQL and avoid the whole problem. — user207421, Jul 21 '14 at 08:38

score 0 · Answer 1 · answered Jul 21 '14 at 08:32

As stated in the answers of the question proposed by Keppi, you could try to use BigDecimal class that avoid rounding errors accumulation and offers true decimal value accuracy. It is the general or most used solution.

As your error is in 10^-10, you can also format what you are writing using for example String.format("%.2f", duetot). It allows you to use float or double in all your operations which can be simpler and faster than BigDecimal. But I strongly advice you not to use that trick but to stick to BigDecimal if you do operations other than add or substract, for example VAT or any other per cent operation.

The last trick, is to only use integers or long and do all operation in cents. But it should really be seen as a memory and CPU optimisation if your application was heavy loaded.

MySQL/Java Calculation output has an issue

1 Answers1