SQLite how to use integer for storing price value

Question

I am using real type to save price value in SQLite database but it does not return correctly value. If I save a few values like 1.01 it will return 4.029999999999999. I have read that best way to save prices in SQLite is to use integer type

But I don't know how to use it. How to store and retrieve prices in this case? Here code how I retrieve price now:

    Cursor cursor = null;
    double totalPrice = 0;
    try {
        cursor = mDB.rawQuery("select " + Expense.PRICE + " from " + Expense.TABLE_NAME + " where " + " (strftime("
                + dateType + ", " + Expense.DATE + "))" + "= ? ", new String[] { currentDate });

    } catch (Exception e) {
        Log.e(TAG, "error" + e.toString());
        e.printStackTrace();
    }
    for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
        totalPrice += cursor.getDouble(cursor.getColumnIndex(Expense.PRICE));
    }

Answer 1

Effective Java book describes, in Item 48, that one should not use float or double if exact answers is required.

I think you should use long value to store the price value, but store the values in "cents" (or equivalent as per currency of choice) instead of "dollars". This way your math around prices should work just fine with no loss due to floating-point arithmetic.

Answer 2

It is actually quite simple when you see it.

I don't know what currency you are in, but assume US $.

You want to store $1.01. This is the same as 101 cents. You just multiply by 100.

So before storing the number, multiply by 100. When displaying the number, divide by 100.

---

Just to add to that answer: I also think you should store the numbers in memory as cents/integers too. So for all your workings, calculations etc, use that integer format.

If a user enters a $ amount, convert it straight away into integer (multiply by 100). And only change back to $ when you need to display it.

In other words, not just when going to and from the database.