CODEWITHSUNDEEP
hibernate
Shvalb
Shvalb

Reputation: 1933

How to update columns value with new value

I would like to update column value with a new entity value incrementally, for example:

support I have a table USER with COLUMN name BALANCE

for specific user, his balance is 3000.

Now, I would like to set his value to 3500.

I have hibernate "User" Entity that has a 'balance' value of 500.

How can I make the update??

If I would like to make it using pure sql query, I would simple do:

"UPDATE USER set balance = balance+500 where user_id=3"

I would like to avoid calling sql and use hibernate.

Upvotes: 2

Views: 10279

Answers (3)

Jaskaran Singh
Jaskaran Singh

Reputation: 39

You can achieve it using JPA in the following way:

CriteriaUpdate<User> criteria = builder.createCriteriaUpdate(User.class);
Root<User> user = criteria.from(User.class);
criteria.set(user.<Integer>get("balance"), builder.sum(user.<Integer>get("balance"), 500));
criteria.where(builder.equal(user.get("user_id"), 3));
entityManager.createQuery(criteria).executeUpdate();

It will execute following query on database:

UPDATE user SET balance = balance + 500 WHERE user_id = 3

If you want to do it using Hibernate then i will advice you to use locking as using Hibernate you will first select the row, update the balance in Application and then update the row. In between select and update statements if row gets updated with different balance then you will end up writing incorrect data into the database. So, you can use following code to avoid it:

User user = session.get(User.class, 3, LockMode.PESSIMISTIC_WRITE);
user.setBalance(user.getBalance() + 500);
session.saveOrUpdate(user);

It will execute following queries on database:

SELECT * FROM user WHERE user_id = 3 FOR UPDATE #Let's assume current balance is 3000
UPDATE user SET balance = 3500 WHERE user_id=3

Upvotes: 1

rahul maindargi
rahul maindargi

Reputation: 5615

Have you tried below code? Always remeber to mention what all possible solutions you tried before asking question. 

User user=session.get(User.class, 3); // 3 is ID of user. 
user.setBalance((user.getBalance()+500));
session.saveOrUpdate(user);
session.commit();

Upvotes: 6

kostja
kostja

Reputation: 61538

This should work for you:

Query query = session.createQuery("UPDATE User u SET u.balance = :balance WHERE u.id = :userId");
query.setParameter("balance", balance + 500);
query.setParameter("userId", 3);
query.executeUpdate();

Please not that you are updaing the User entity over HQL, not the USER table like you would with SQL. I also assumed that the balance and the id columns are mapped to the balance and the id attributes in the User entity.

Upvotes: 1

Related Questions