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
– Raymond Nijland, Commented May 26, 2018 at 7: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
– Tim Biegeleisen, Commented May 26, 2018 at 7: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
– Tim Biegeleisen, Commented May 26, 2018 at 7:16
if you look into the docs docs.jboss.org/hibernate/orm/3.3/reference/en/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
– Raymond Nijland, Commented May 26, 2018 at 7:21
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
– Raymond Nijland, Commented May 26, 2018 at 7:24

taher · Accepted Answer · 2019-10-30 13:39:02Z

0

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)

edited Oct 30, 2019 at 13:39

answered Oct 30, 2019 at 11:44

taher

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to run mysql update batch query in Hibernate?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related