I have a table that contains mamy columns and I want to update the one or few columns of the row without effecting remaining columns I can write query:
update table as t set t.a=:a set t.b=:b where t.id=1
But seen I dont know which columns will be selected to update, and I think it is not a good idea to write every query for every scenarios. Well, I have to write query for every scenarios, but I am looking for a better way to update the table dynamically. I am thinking criteria would be a good choice. But the problem is that I have no idea how to write criteria update specific column. My code now can update the column but it would set other column to null or empty.
What would be the good way to update specific columns without changing other columns?
Hibernate supports two basic ways how to update tables' columns.
The first is natural, via loading entity into session
, changing it in run-time, flush (udpate) the changes back to DB. This is a standard, ORM style.
The second is mostly oriented on very efficient SQL UPDATE statement. It is documented here as:
cite from doc:
... However, Hibernate provides methods for bulk SQL-style DML statement execution that is performed through the Hibernate Query Language...
It does not provide API for criteria query, but it does work with HQL == with our domain model.
We can create a WHERE clause on top of our mapped entities, and ask for update only of a few columns, we selected. There are some limitations (JOIN is not supported) but they can be solved by subqueries...
This is a snippet from doc:
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlUpdate = "update Customer c set c.name = :newName where c.name = :oldName";
// or String hqlUpdate = "update Customer set name = :newName where name = :oldName";
int updatedEntities = session.createQuery( hqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
tx.commit();
session.close();
Also check this Q&Q: Hibernate execute update with criteria