postgresql - What is the intended way to increment or decrement a counter value, using Spring 4 data jpa with repositories? -

given following scenario:

• a shop sells products a, b , c.
• the stock amounts := 100, b := 100, c := 100
• a shopping cart contains 50 a, , 80 b

we have business service providing checkout(shoppingcart cart) method. method shall 2 things transactional:

1. persists shopping cart
2. reduce shop stock according amount of products

so in our example, after checkout, want shop stock := 50, b := 20 , c := 100.

we use 2 repository methods so:

• cartrepository.save(shoppingcart cart)
• and, each contained product shopstockrepository.incrementstock(productid, -1 * amount of product)

incrementstock implemented custom update query using @modifying , @query annotations. query uses update set statement decreases shop stocks related item.

unfortunately turns out, database correctly updated shop item object not refreshed.

using integrations tests, found out, if inject entitymanager checkout service , advise it, refresh each product manually, seems works. correct way handle kind of incremental update???

an alternative, think of, read product, update value , save back, wouldn't lead race conditions? have implement sort of locking?

hope can help, we're struggling days :-/

marius

what doing correct, if want value updated next time fetch entity need add @modifying(clearautomatically = true).

for example

public interface usersrepository extends jparepository<users, string> {      @modifying(clearautomatically = true)     @query("update #{#entityname} u set u.sent = u.sent + :sentincrement, u.receipts = u.receipts + :receiptsincrement u.id = :id")     int incrementsentandreceipts(@param("id") string id,                                   @param("sentincrement") int sentincrement,                                   @param("receiptsincrement") int receiptsincrement); } 

the downside every time execute query, first level cache cleared , you'll force hibernate go database refresh data.