Reputation: 5572
android sqlite BigDecimal: convert from double or text without loss of precision
create table Foo (productName text, price numeric)
How to convert the price into a BigDecimal in android?
new BigDecimal(cursor.getString(1))
or
new BigDecimal(cursor.getDouble(1))
It should not lose precision.
Upvotes: 0
Views: 1264
Answers (1)
Reputation: 56938
In short the only way to not lose precision is to fool SQlite when saving the data for a NUMERIC column.
Assuming the value value 1234567890.1234567890 SQLite will store this be it a string or a double according to numeric affinity as per :-
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.
Datatypes In SQLite Version 3 - 3. Type Affinity
Retrieving the value via getDouble will result in loss of precision, resulting in the Big Decimal being passed a value of 1234567890.1234567165374755859375 (see output below).
Retrieving the value via getString will result in loss of precision resulting in the Big Decimal being passed a value of 1.23457e+09 (see output below).
How to Fool SQLite
A way around this would be to fool SQLITE, for example saving MYNUMB=1234567890.1234567890, will be saved as TEXT and can then be retrieved as the original string, stripped of MYNUMB= and used to set the BIG DECIMAL with 1234567890.1234567890.
The following code may be of interest or use and was used determining the above :-
MainActivity
public class MainActivity extends AppCompatActivity {
DBHelper mydbhlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//Get DBHelper
mydbhlpr = new DBHelper(this);
// Empty table
mydbhlpr.getWritableDatabase().delete(DBHelper.TBNAME,null,null);
// Insert value as a string
ContentValues cv = new ContentValues();
cv.put(DBHelper.PRODUCTNAME_COL,"myproduct");
cv.put(DBHelper.PRICE_COL,"1234567890.1234567890");
cv.put(DBHelper.COMMENTS_COL,"DATA INPUT AS STRING");
mydbhlpr.insertRow(cv);
cv.clear();
// insert value as a double
cv.put(DBHelper.PRODUCTNAME_COL,"myproduct");
cv.put(DBHelper.PRICE_COL,1234567890.1234567890D);
cv.put(DBHelper.COMMENTS_COL,"DATA INPUT AS DOUBLE");
mydbhlpr.insertRow(cv);
// alternative insert method with value as a string
mydbhlpr.getWritableDatabase().execSQL(
"INSERT INTO " + DBHelper.TBNAME + "(" +
DBHelper.PRODUCTNAME_COL + "," +
DBHelper.PRICE_COL + "," +
DBHelper.COMMENTS_COL +
")" +
" VALUES('myproduct','1234567890.1234567890','ALTERNATE INPUT STR')"
);
// alternative insert method with value as numeric (i.e. not in quotes)
mydbhlpr.getWritableDatabase().execSQL(
"INSERT INTO " + DBHelper.TBNAME + "(" +
DBHelper.PRODUCTNAME_COL + "," +
DBHelper.PRICE_COL + "," +
DBHelper.COMMENTS_COL +
")" +
" VALUES('myproduct',1234567890.1234567890,'ALTERNATE INPUT NUM')"
);
// method to force store numeric data (would need to strip MYNUM= before convert)
mydbhlpr.getWritableDatabase().execSQL(
"INSERT INTO " + DBHelper.TBNAME + "(" +
DBHelper.PRODUCTNAME_COL + "," +
DBHelper.PRICE_COL + "," +
DBHelper.COMMENTS_COL +
")" +
" VALUES('myproduct','MYNUM=1234567890.1234567890','FOOLIT INPUT NUM')"
);
mydbhlpr.retrieveData();
}
}
This basically populates the table (see DBHelper) with 5 rows and then calls the retrieveData method to output conversion results to the Log for each row in the table.
DBHelper
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "bigdecimal";
public static final String TBNAME = "bd";
public static final String PRODUCTNAME_COL = "productName";
public static final String PRICE_COL = "price";
public static final String COMMENTS_COL = "comments";
public static final int DBVERSION = 1;
SQLiteDatabase mDB;
DBHelper(Context context) {
super(context,DBNAME,null,DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String tblcrtsql = "CREATE TABLE IF NOT EXISTS " + TBNAME + "(" +
PRODUCTNAME_COL + " TEXT, " +
PRICE_COL + " NUMERIC, " +
COMMENTS_COL + " TEXT" +
")";
db.execSQL(tblcrtsql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public void insertRow(ContentValues cv) {
mDB.insert(TBNAME,null,cv);
}
public void retrieveData() {
String priceasstr;
Double priceasdbl;
BigDecimal bdstrprice, bddblprice;
DecimalFormat df = new DecimalFormat("#.000000000000");
Cursor csr = mDB.query(TBNAME,null,null,null,null,null,null);
while (csr.moveToNext()) {
priceasstr = csr.getString(csr.getColumnIndex(PRICE_COL));
priceasdbl = csr.getDouble(csr.getColumnIndex(PRICE_COL));
try {
bdstrprice = new BigDecimal(priceasstr);
} catch (Exception e) {
Log.d("OOOPS","extracted data not a valid number");
String extracted = priceasstr.substring(6);
bdstrprice = new BigDecimal(extracted);
}
bddblprice = new BigDecimal(priceasdbl);
Log.d("ROWINFO",
"Row = " + Integer.toString(csr.getPosition()) +
". Commentary: " + csr.getString(csr.getColumnIndex(COMMENTS_COL)) +
"\n\tValue extracted as String = " + priceasstr +
"\n\tValue extracted as Double = " + Double.toString(priceasdbl) +
"\n\t formatted from Double = " + df.format(priceasdbl) +
"\n\tBD via getString = " + bdstrprice.toString() +
"\n\tBD via getDouble = " + bddblprice.toString() +
"\n\tBDF via getString = " + df.format(bdstrprice) +
"\n\tBDF via getDouble = " + df.format(bddblprice)
);
}
csr.close();
}
}
Output
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/ROWINFO: Row = 0. Commentary: DATA INPUT AS STRING
Value extracted as String = 1.23457e+09
Value extracted as Double = 1.2345678901234567E9
formatted from Double = 1234567890.123456700000
BD via getString = 1.23457E+9
BD via getDouble = 1234567890.1234567165374755859375
BDF via getString = 1234570000.000000000000
BDF via getDouble = 1234567890.123456716537
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/ROWINFO: Row = 1. Commentary: DATA INPUT AS DOUBLE
Value extracted as String = 1.23457e+09
Value extracted as Double = 1.2345678901234567E9
formatted from Double = 1234567890.123456700000
BD via getString = 1.23457E+9
BD via getDouble = 1234567890.1234567165374755859375
BDF via getString = 1234570000.000000000000
BDF via getDouble = 1234567890.123456716537
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/ROWINFO: Row = 2. Commentary: ALTERNATE INPUT STR
Value extracted as String = 1.23457e+09
Value extracted as Double = 1.2345678901234567E9
formatted from Double = 1234567890.123456700000
BD via getString = 1.23457E+9
BD via getDouble = 1234567890.1234567165374755859375
BDF via getString = 1234570000.000000000000
BDF via getDouble = 1234567890.123456716537
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/ROWINFO: Row = 3. Commentary: ALTERNATE INPUT NUM
Value extracted as String = 1.23457e+09
Value extracted as Double = 1.2345678901234567E9
formatted from Double = 1234567890.123456700000
BD via getString = 1.23457E+9
BD via getDouble = 1234567890.1234567165374755859375
BDF via getString = 1234570000.000000000000
BDF via getDouble = 1234567890.123456716537
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/OOOPS: extracted data not a valid number
10-05 18:41:18.392 4535-4535/mjt.so46593121 D/ROWINFO: Row = 4. Commentary: FOOLIT INPUT NUM
Value extracted as String = MYNUM=1234567890.1234567890
Value extracted as Double = 0.0
formatted from Double = .000000000000
BD via getString = 1234567890.1234567890
BD via getDouble = 0
BDF via getString = 1234567890.123456789000
BDF via getDouble = .000000000000
Upvotes: 2
Related Questions
- How can I store a BigDecimal (Java) value in a Real (SQlite) column?
- Getting Double from Sqlite and display decimals in android
- how to properly deal with bigdecimal values in ormlite?
- How to use BigDecimals without using doubles
- android sqlite REAL type
- How can I write SQLite Real vals to Java BigDecimal values?
- How to use double in Android via SQLite
- sqlite field value rounded off at the time of storing in a double field
- storing double values in SQLite: how to ensure precision?
- Convert a double to a SQL-friendly string on Android?