What is the fastest way to update multiple rows of a table

Question

I have a table named messages containing toUser, message and status columns. I want to update all the statuses of the messages with a specific user.

So, I wrote a query like this,

Session s = DB.getSession();
s.createSQLQuery("UPDATE `message` SET `status`='0' WHERE `toUser`='3'").executeUpdate();
s.close();

But then, I was been told that updating using pure hibernate methods are more faster and efficient (I think it has to do something with the hibernate pool), like shown below.

Session s = DB.getSession();
Transaction tr = s.beginTransaction();
Criteria cr = s.createCriteria(Connection.Pojo.Message.class);
cr.add(Restrictions.eq("toUser", 3));
List<Connection.Pojo.Message> l = cr.list();
for (Connection.Pojo.Message msg : l) {
    msg.setStatus((byte) 0);
    s.update(msg);
}
tr.commit();
s.close();

So, my question is what is the fastest way to update these rows? Please provide a detailed answer if possible.

Thanks in any advice.

I am not sure how Hibernate could be faster than a simple SQL query — jrtapsell, Jan 29 '18 at 10:29
Is this a performance bottleneck in your application? You should be cautious about optimizing prematurely with the cost of code readability and maintainability. If you're using Hibernate, as a rule of thumb you should avoid plain SQL and handle data access type-safely though e.g. the Criteria API. — Mick Mnemonic, Jan 29 '18 at 10:37
@MickMnemonic I used hibernate everywhere, other than this... — Roshana Pitigala, Jan 29 '18 at 10:40

score 3 · Answer 1 · answered Jan 29 '18 at 10:38

I think by "pure Hibernate methods" they meant using an HQL update:

Session s = DB.getSession();
s.createQuery("UPDATE message m SET m.status=:newStatus WHERE toUser=:userId")
    .setInteger("newStatus", 0)
    .setInteger("userId", 3)
    .executeUpdate();
s.close();

This approach provides performance that is equivalent to your SQL solution.

What is the fastest way to update multiple rows of a table

1 Answers1