How to run mysql update batch query in Hibernate?

Question

I've got a mysql (SQL) batch query:

START TRANSACTION;

UPDATE home SET currentSeq = 2 WHERE resId = '6hiuxhqkw4s1bta9a';
UPDATE home SET currentSeq = 4 WHERE resId = 'hpvihvzk0vainpkgg';
UPDATE home SET currentSeq = 6 WHERE resId = 'krfswe6wohjtugmtd';
UPDATE home SET currentSeq = 3 WHERE resId = 'tcybuiuulkju5wjre';
UPDATE home SET currentSeq = 7 WHERE resId = 'sgs4gr4v6aepuwdgq';

COMMIT;

The above Code is working fine while running on MySQl 5.7 terminal However, I'm trying to run the same query from Hibernate and Hibernate is giving an Exception - "Nested Query Not Supported".

My Hibernate Code:

String queryToBeUpdate = "START TRANSACTION;\n" + 
            "\n" + 
            "UPDATE home SET currentSeq = 2 WHERE resId = '6hiuxhqkw4s1bta9a';\n" + 
            "UPDATE home SET currentSeq = 4 WHERE resId = 'hpvihvzk0vainpkgg';\n" + 
            "UPDATE home SET currentSeq = 6 WHERE resId = 'krfswe6wohjtugmtd';\n" + 
            "UPDATE home SET currentSeq = 3 WHERE resId = 'tcybuiuulkju5wjre';\n" + 
            "UPDATE home SET currentSeq = 7 WHERE resId = 'sgs4gr4v6aepuwdgq';\n" + 
            "\n" + 
            "COMMIT;";

Query q =sessionFactory.getCurrentSession().createSQLQuery(queryToBeUpdate);
q.executeUpdate();

It might be the case createSQLQuery function doesn't support multiple SQL statements separated with semicons (`;`) — Raymond Nijland, May 26 '18 at 07:15
I don't know whether or not this can done from Hibernate. It can be done from JDBC, in which case you would turn off auto commit, and then run each update. The `START TRANSACTION` ... `COMMIT` markers are for raw MySQL, not JDBC. — Tim Biegeleisen, May 26 '18 at 07:15
@RaymondNijland I think you're right. We can do this directly from JDBC, but in general it probably isn't a good idea. — Tim Biegeleisen, May 26 '18 at 07:16
if you look into the docs https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html you can see the `createSQLQuery` is creating a ` SQLQuery?` Object.. I see it's possible to do JOINS but i didn't find a possible way to allow multiple SQL statements.. — Raymond Nijland, May 26 '18 at 07:21
@RaymondNijland Thanks for Responding guys , I'm new to hibernate , My task is to update the query in batch using hibernate. I've tried to do a lot of research on google but don't seem to find an appropriate answer ,as most of the examples are about "hibernate batch insert" on internet. — Sumit, May 26 '18 at 07:22
You can rewrite all update queries into one update query by the way , not a hibernate thing but pure SQL code .. Then you only need to execute three queries.. — Raymond Nijland, May 26 '18 at 07:24
@TimBiegeleisen I think you're right when you said that it can't be done from hibernate. I think my problem is somewhat similar like to this :-- https://stackoverflow.com/questions/39033896/hibernate-hql-execute-multiple-update-statements-in-same-query — Sumit, May 26 '18 at 07:44
@Sumit The good news is that usually you don't need to do this (otherwise Hibernate would have supported it). You can always call a stored proc. — Tim Biegeleisen, May 26 '18 at 07:45

taher · Answer 1 · 2019-10-30T13:39:02.167

You can combine an UPDATE with a CASE like this:

String queryToBeUpdate = "UPDATE home SET currentSeq = (CASE 
                          WHEN  resId = '6hiuxhqkw4s1bta9a' THEN '2' 
                          WHEN  resId = 'hpvihvzk0vainpkgg' THEN '4' 
                          WHEN  resId = 'krfswe6wohjtugmtd' THEN '6' 
                          WHEN  resId = 'tcybuiuulkju5wjre' THEN '3' 
                          WHEN  resId = 'sgs4gr4v6aepuwdgq' THEN '7' 
                          END)
                          WHERE resId IN(6hiuxhqkw4s1bta9a,hpvihvzk0vainpkgg,krfswe6wohjtugmtd,tcybuiuulkju5wjre,sgs4gr4v6aepuwdgq) ";
 SQLQuery query = session.createSQLQuery(strq);
 query.executeUpdate(queryToBeUpdate);

(AS BULK UPDATE QUERY)

How to run mysql update batch query in Hibernate?

1 Answers1