How can I store a BigDecimal (Java) value in a Real (SQlite) column?

Question

I'm still having big problems with BigDecimal (the trail of tears started here, and continued to here so far.

Now I've got the opposite problem - going from BigDecimal in the POJO to REAL in the SQLite table.

The POJO is defined as:

public class DeliveryItem {
    private int _id;
    private String _invoiceNumber;
    private String _UPC_PLU;
    private String _vendorItemId;
    private int _packSize;
    private String _description;
    private BigDecimal _cost;
    private BigDecimal _margin;
    private BigDecimal _listPrice;
    private int _departmentNumber;
    private String _subdepartment;
    private String _quantity;

    public void setID(int id) {
        this._id = id;
    }
    . . .

This code, attempting to get the data in shape to be able to posted to the SQLite table (where the corresponding columns are data type REAL, SQLite's only real/float data type):

public long addDeliveryItem(DeliveryItem delItem) {
    long IdAdded = 0;
    ContentValues values = new ContentValues();
    values.put(COLUMN_INVOICENUM, delItem.get_invoiceNumber());
    values.put(COLUMN_UPCPLU, delItem.get_UPC_PLU());
    values.put(COLUMN_VENDORITEMID, delItem.get_vendorItemId());
    values.put(COLUMN_PACKSIZE, delItem.get_packSize());
    values.put(COLUMN_DESCRIPTION, delItem.get_description());
    //values.put(COLUMN_COST, delItem.get_cost());
    //values.put(COLUMN_COST, new BigDecimal(delItem.get_cost()));
    values.put(COLUMN_COST, (Double) delItem.get_cost());
    values.put(COLUMN_MARGIN, delItem.get_margin());
    values.put(COLUMN_LISTPRICE, delItem.get_listPrice());
    values.put(COLUMN_DEPTNUM, delItem.get_departmentNumber());
    values.put(COLUMN_SUBDEPT, delItem.get_subdepartment());
    values.put(COLUMN_QTY, delItem.get_quantity());

    SQLiteDatabase db = this.getWritableDatabase();

    if (db != null) {
        IdAdded = db.insert(TABLE_DELIVERYITEMS, null, values);
    }
    if (db != null) {
        db.close();
    }
    return IdAdded;
}

For the COLUMN_COST line above (the "MARGIN" and "LISTPRICE" columns have the same problem), I get, "error: incompatible types: BigDecimal cannot be converted to Double" when I try this:

values.put(COLUMN_COST, (Double) delItem.get_cost());

...and for the commented out code (both lines), namely this:

values.put(COLUMN_COST, delItem.get_cost());

...and this:

values.put(COLUMN_COST, new BigDecimal(delItem.get_cost()));

...I get:

error: no suitable method found for put(String,BigDecimal)
method ContentValues.put(String,String) is not applicable
(argument mismatch; BigDecimal cannot be converted to String)
method ContentValues.put(String,Byte) is not applicable
(argument mismatch; BigDecimal cannot be converted to Byte)
method ContentValues.put(String,Short) is not applicable
(argument mismatch; BigDecimal cannot be converted to Short)
method ContentValues.put(String,Integer) is not applicable
(argument mismatch; BigDecimal cannot be converted to Integer)
method ContentValues.put(String,Long) is not applicable
(argument mismatch; BigDecimal cannot be converted to Long)
method ContentValues.put(String,Float) is not applicable
(argument mismatch; BigDecimal cannot be converted to Float)
method ContentValues.put(String,Double) is not applicable
(argument mismatch; BigDecimal cannot be converted to Double)
method ContentValues.put(String,Boolean) is not applicable
(argument mismatch; BigDecimal cannot be converted to Boolean)
method ContentValues.put(String,byte[]) is not applicable
(argument mismatch; BigDecimal cannot be converted to byte[])

So how can I manipulate the BigDecimal value in the so that it will be accepted by the ContentValues instance?

UPDATE

I may have to temporarily fudge it by just ignoring those vals and changing the DDL to:

//+ COLUMN_COST + " REAL,"  + COLUMN_MARGIN + " REAL," + COLUMN_LISTPRICE + " REAL,"
+ COLUMN_COST + " REAL DEFAULT 0,"  + COLUMN_MARGIN + " REAL DEFAULT 0," + COLUMN_LISTPRICE + " REAL DEFAULT 0,"

Solution 1

So how can I manipulate the BigDecimal value in the so that it will be accepted by the ContentValues instance?

Well, you can call doubleValue() on the BigDecimal to downgrade it to a double, which can go in the ContentValues (after autoboxing it to a Double). Or, you can store its string representation in a TEXT column. Or, you can store the unscaledValue() and scale() in two INTEGER columns.

But SQLite's closest match is REAL

No, it is not.

You seem to be interested in storing pricing data in SQLite. A search for storing prices in sqlite on a major search engine turns up:

• SQLite how to use integer for storing price value
• Storing currency values in SQLite3
• https://dba.stackexchange.com/questions/15729/storing-prices-in-sqlite-what-data-type-to-use

The consensus is to store the value as an INTEGER column, priced in the smallest individual currency unit (e.g., cents for currency values in US dollars), or something else as appropriate (e.g., tenths of a cent if that's the finest granularity of the prices).

Your POJO would then hold int values to match.

Solution 2

I use the SQLite Integer type and convert like so:

BigDecimal bd = new BigDecimal("1234.5678");

int packedInt = bd.scaleByPowerOfTen(4).intValue(); // packedInt now = 12345678

Now save packedInt to SQLite Integer field.

To go back to BigDecimal:

BigDecimal bd = new BigDecimal(packedInt); // bd = 12345678

bd = bd.scaleByPowerOfTen(-4);             // now bd = 1234.5678

Solution 3

There's two possibilities:

1. Store it as a String.
2. Store it as a Blob. BigDecimal is Serializable, so this should work.

The first should be straightforward, for the second I'd point you to an excellent question on a similar subject, how to store Image as blob in Sqlite & how to retrieve it?

Comments

[B. Clay Shannon-B. Crow Raven]

I like the storing as int idea; that seems the simplest and most "trustworthy" way.

[mrek]

Storing it as blob is not working. When you store it as a String, how do you retrieve it?