Executing multiple native queries in one go

Question

I'm wondering if it is possible to execute several semicolon-separated SQL update queries with one SQLQuery#executeUpdate() call in Hibernate (3.2).

I have string containing multiple updates like this:

String statements = "UPDATE Foo SET bar=1*30.00 WHERE baz=1; 
                     UPDATE Foo SET bar=2*45.50 WHERE baz=2;...";

I'm trying to do

 Query query = session.createSQLQuery(statements);
 query.executeUpdate();

But keep getting the following error

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near 
'UPDATE Foo SET bar=Y WHERE ' at line 3

If I execute the contents of the statements by hand I get no errors so I'm assuming the multiple semicolon-separated queries are causing trouble somewhere in Hibernate or JDBC.

Is it better to just split the statementsstring and do createSQLQuery(statement).executeUpdate() individually for each line?

Answer 1

An alternative is to use a CASE statement. For example:

UPDATE Foo
    SET bar = 
    CASE baz
       WHEN 1 THEN 'X'
       WHEN 2 THEN 'Y'
    END CASE
WHERE baz in (1,2)