How can I store currency in ROOM DB?

Question

I have entity:

@Entity(tableName = "products")
public class Product{

    @PrimaryKey(autoGenerate = true)
    private Long id;

    @ColumnInfo(name = "amount")
    private BigDecimal amount;

I need store it in Room DB. I Can not store BigDecimal and I create converter:

public static class Converters {
        @TypeConverter
        public BigDecimal fromString(String value) {
            return value == null ? null : new BigDecimal(value);
        }

        @TypeConverter
        public Double amountToString(BigDecimal bigDecimal) {
            if (bigDecimal == null) {
                return null;
            } else {
                return bigDecimal.doubleValue();
            }
        }
    }

and add to column:

@TypeConverters(Converters.class)
    @ColumnInfo(name = "amount")
    private BigDecimal amount;

Now I store currency in double column. And I want method for summ totalcurrency:

@Query("SELECT SUM(amount) FROM products")
LiveData<Double> totalSum();

But I think it is bad way, because I can lose some values when converting.

My question: How can I ctore currency in ROOM DB? and return method

LiveData<BigDecimal> totalSum();

score 13 · Answer 1 · edited Aug 26 '22 at 07:51

Instead of Long vs BigDecimal converters, I'm using between String and BigDecimal.

Warning: With this way, you can't use SUM, TOTAL or other expression with Room DB. Because my purpose for app is using many currencies, so I manually retrieve data and calculate based on current exchange rates.

class BigDecimalTypeConverter {

    @TypeConverter
    fun bigDecimalToString(input: BigDecimal?): String {
        return input?.toPlainString() ?: ""
    }

    @TypeConverter
    fun stringToBigDecimal(input: String?): BigDecimal {
        if (input.isNullOrBlank()) return BigDecimal.valueOf(0.0)
        return input.toBigDecimalOrNull() ?: BigDecimal.valueOf(0.0)
    }

}

if you only have one currency, you can use Double instead of String:

class BigDecimalDoubleTypeConverter {

    @TypeConverter
    fun bigDecimalToDoubleinput: BigDecimal?): Double {
        return input?.toDoubleOrNull() ?: 0.0
    }

    @TypeConverter
    fun stringToBigDecimal(input: Double?): BigDecimal {
        if (input == null) return BigDecimal.ZERO
        return BigDecimal.valueOf(input) ?: BigDecimal.ZERO
    }

}

So you can retrieve SUM, TOTAL,... from Dao:

@Query("SELECT SUM(transactions.amounts) FROM transactions WHERE transactions.timestamp >= :fromDateTimestamp")
fun getTotalAmount(fromDateTimestamp: Long): Double

Use TypeConverter on your database:

@Database(...)
@TypeConverters(BigDecimalTypeConverter::class)
internal abstract class AppDatabase : RoomDatabase() {
   ...
}

in the second solution that you used double, does it preserve the precise amount of the number? I mean many best practices forbid using `Double` as currency type as it has no decimal fractions and instead has binary fractions. — Hossein Farrokhi, Aug 01 '22 at 11:19

score 10 · Answer 2 · answered Dec 26 '17 at 19:40

You can store your value in long. If you have eg. 1.55 when serializing multiply it by 100 and store long in db. When you deserialize create BigDecimal from this long and divide it by 100

 public static class Converters {
        @TypeConverter
        public BigDecimal fromLong(Long value) {
            return value == null ? null : new BigDecimal(value).divide(new BigDecimal(100));
        }

        @TypeConverter
        public Long toLong(BigDecimal bigDecimal) {
            if (bigDecimal == null) {
                return null;
            } else {
                return bigDecimal.multiply(new BigDecimal(100)).longValue();
            }
        }
    }

I like this solution, but unfortunately it doesn't account for countries that have a different number of decimal places in their currency. See this Stack Overflow question (ignore the fact that is pertaining to iPhone development): (https://stackoverflow.com/questions/2701301/various-countrys-currency-decimal-places-width-in-the-iphone-sdk) — shagberg, Sep 04 '18 at 18:28

score 1 · Answer 3 · answered Jan 26 '23 at 18:03

@ip696 Instead of converting BigDecimal into String. You can try converting BigDecimal into Double

@TypeConverter
fun amountToDouble(input: BigDecimal?): Double? {
    return input?.toDouble()
}

@TypeConverter
fun fromDouble(input: Double?): BigDecimal? {
    return input?.toBigDecimal()
}

then the below code will works.

@Query("SELECT SUM(amount) FROM products")
LiveData<Double> totalSum();

additional note: ORDER BY clause will also work as expected.

How can I store currency in ROOM DB?

3 Answers3