What's the correct approach for monetary operations with JPA + MySQL?

Question

I have a case where I need to execute an update in a field doing a monetary operation subtract in this case. The column was created as DECIMAL(19,2) UNSIGNED.

First I get the value:

static final String LOCK_AMOUNT_FOR_UPDATED = "select amount from Sample where uuid = :uuid for update ";

This is the repository:

    @Query(value = LOCK_AMOUNT_FOR_UPDATED, nativeQuery = true)
    Float getAmountForUpdate(@Param("uuid") String uuid);

Here is the first question, instead of returning Float. The recommended is returning a BigDecimal?

Then I check the values and perform an update:

static final String DECREASE_AMOUNT = "update Sample set amount = amount - :amountToSubtract where uuid = :uuid ";

Repository:

    @Modifying
    @Query(value = DECREASE_AMOUNT, nativeQuery = true)
    void decreaseAmount(@Param("amountToSubtract") Float amountNegotiated, @Param("uuid") String uuid);

Doing this when the value is: 1927369.70. I'm getting:

Data truncation: Out of range value for column 'amount' at row 1

The method calling is like this:

repository.decreaseNetAmount(amountNegotiated.floatValue(), uuid);

I noticed that 1927369.70 turned to 1927369.80 when I select the value and when I call .floatValue() in BigDecimal.

What's the correct approach use everything as BigDecimal, even the parameters of nativeQuery?

Answer 1

I would recommend to:

1. Use BigDecimal for the parameters and the entity attribute (if you decide to use BigDecimal).
2. Reconsider why you're using a native query to update a single record. Getting the entity object and changing the attribute's value seems to be the easier approach and might provide better performance due to internal optimizations of your persistence provider.
3. Reconsider the pessimistic lock that you set when fetching the record. This also blocks all read operations on that record (and potentially other records on the same page) until the end of the transaction. An optimistic lock often provides better scalability.

Recommendations 2 and 3 are general best practices. We would need to take a broader look at the entire use case and other parts of the application to make an educated decision