Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes Working with numbers Java/MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Working with numbers Java/MySQL" Watch "Working with numbers Java/MySQL" New topic

Working with numbers Java/MySQL

denis sorn
Ranch Hand

Joined: Apr 30, 2008
Posts: 33

I am developing application which should help with gas storage optimization. Storages are implemented as tables in MySQL database, which among others contain injRateAvailable, withdRateAvailable and StorageAvailable (available Volume) columns which again contain values per Timestamp.

Now all those values are 'double' in MySQL and I perform all calculations and comparisons with doubles in Java, and because I was a little lazy I haven't converted all that to BigDecimal when I found out that working with floating point numbers can/will lead to problems in some cases.

That worked so for a while, but now I am encountering problems. I deleted all deals, and StorageAvailable should be back to it's maximum. It was on some places over, because that shows me if some deals are endangered (lack of gas or in case of negative values lack of free volume) in the future.

But as I expected and ignored problems have started to occur. As you see MySQL shows 71280 for StorageAvailable, but query asked for greater then 7180.

If I remove one more null it will return an empty set.

Now I am not sure even if I should change from double to BigDecimal in java, I'm thinking if it would be enough to do some rounding, because I think I don't need so great precision and I think double rounded to three or even two decimal places should be enough. This, considering the lost of performance because of working with BigDecimal and DECIMAL in MySQL, is why I am thinking about rounding. Though I am not sure how big that lost would be, maybe marginal...

Now if anyone could give me a suggestion or an advice on would it be enough to 'convert' calculations in Java to BigDecimal or I should change double to decimal in MySQL also, or could I even end up with rounding?

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

You should use BigDecimal instead of double. Talking about performance is irrelevant because using double is functionally wrong. Do you want to be the one responsible for intentionally making a decision that produces incorrect output.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
denis sorn
Ranch Hand

Joined: Apr 30, 2008
Posts: 33
Hi Jeanne,

thanks for reply! I am switching to BigDecimal and DECIMAL in MySQL.
I agree. Here's the link:
subject: Working with numbers Java/MySQL
It's not a secret anymore!