如何在 Real (SQlite) 列中存储 BigDecimal (Java) 值？

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

提问by B. Clay Shannon

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

我仍然有 BigDecimal 的大问题（眼泪的踪迹从这里开始，一直到这里为止。

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

现在我遇到了相反的问题 - 从 POJO 中的 BigDecimal 到 SQLite 表中的 REAL。

The POJO is defined as:

POJO 定义为：

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):

这段代码试图让数据成形，以便能够发布到 SQLite 表（其中相应的列是数据类型 REAL，SQLite 唯一的实数/浮点数据类型）：

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:

对于上面的 COLUMN_COST 行（“MARGIN”和“LISTPRICE”列有相同的问题），当我尝试这样做时，我得到“错误：不兼容的类型：BigDecimal 无法转换为 Double”：

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[])"

……我明白了，”错误：没有找到适合 put(String,BigDecimal) 方法 ContentValues.put(String,String) 不适用（参数不匹配；BigDecimal 无法转换为 String）方法 ContentValues.put(String,Byte) 不适用（参数不匹配；BigDecimal 不能转换为 Byte）方法 ContentValues.put(String,Short) 不适用（参数不匹配；BigDecimal 不能转换为 Short）方法 ContentValues.put(String,Integer) 不适用（参数不匹配；BigDecimal 不能转换为整数）方法 ContentValues.put(String,Long) 不适用（参数不匹配；BigDecimal 不能转换为 Long）方法 ContentValues.put(String,Float) 不适用（参数不匹配；BigDecimal 不能转换为 Float） ) 方法 ContentValues.put(String,Double) 不适用（参数不匹配；BigDecimal 无法转换为 Double）方法 ContentValues.put(String,Boolean) 不适用（参数不匹配；BigDecimal 无法转换为 Boolean）方法 ContentValues.put(String,byte[]) 不适用（参数不匹配；BigDecimal 不能转换为字节[])”

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

那么如何操作 BigDecimal 值以便 ContentValues 实例接受它呢？

UPDATE

更新

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

我可能不得不暂时通过忽略这些 val 并将 DDL 更改为：

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

采纳答案by CommonsWare

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

那么如何操作 BigDecimal 值以便 ContentValues 实例接受它呢？

Well, you can call doubleValue()on the BigDecimalto 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 TEXTcolumn. Or, you can store the unscaledValue()and scale()in two INTEGERcolumns.

好吧，您可以调用doubleValue()将BigDecimal其降级为 a double，它可以进入ContentValues（在将其自动装箱为 a 之后Double）。或者，您可以将其字符串表示形式存储在TEXT列中。或者，您可以将unscaledValue()和存储scale()在两INTEGER列中。

But SQLite's closest match is REAL

但 SQLite 最接近的匹配是 REAL

No, it is not.

不它不是。

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

您似乎对在 SQLite 中存储定价数据感兴趣。对于搜索storing prices in sqlite上的一个主要的搜索引擎变成了：

• 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

• SQLite 如何使用整数存储价格值
• 在 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 INTEGERcolumn, 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).

共识是将值存储为一INTEGER列，以最小的单个货币单位定价（例如，以美元为单位的货币价值的美分）或其他适当的东西（例如，如果这是价格的最精细粒度，则为十分之一美分）。

Your POJO would then hold intvalues to match.

然后，您的 POJO 将保存int要匹配的值。

回答by PearsonArtPhoto

There's two possibilities:

有两种可能：

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

1. 将其存储为String.
2. 将其存储为 Blob。BigDecimal 是可序列化的，所以这应该有效。

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?

第一个应该很简单，第二个我会指出一个关于类似主题的很好的问题，如何在 Sqlite 中将图像存储为 blob 以及如何检索它？

回答by J.W.L

I use the SQLite Integer type and convert like so:

我使用 SQLite Integer 类型并像这样转换：

BigDecimal bd = new BigDecimal("1234.5678");

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

Now save packedInt to SQLite Integer field.

现在将packedInt 保存到SQLite Integer 字段。

To go back to BigDecimal:

回到 BigDecimal：

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

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