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?

The example code was a bit off, in reality I'm calculating some amounts to bar, they should be OK without quotes, right? — Janne
– Janne, Commented Jul 18, 2011 at 14:35

dogbane · Accepted Answer · 2011-07-18 14:46:37Z

6

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)

answered Jul 18, 2011 at 14:46

dogbane

276k77 gold badges407 silver badges415 bronze badges

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

Comments

RMT · Accepted Answer · 2011-07-18 14:30:04Z

2

What you can do is use batch statements, basically before you execute your query call the addBatch(string query); method, then when all your queries are in the batch just call the executeBatch() method. That will perform them all for you in the order you added the mto the batch.

also: the error in your syntax is you need put in quotes 'Y' or 'X'

answered Jul 18, 2011 at 14:30

RMT

7,0904 gold badges28 silver badges39 bronze badges

6 Comments

Nicolae Albu Over a year ago

addBatch() and executeBatch are from java.sql.Statement, but the poster uses Hibernate.

Nicolae Albu Over a year ago

If I understand correctly, he referred to JDBC as a probable cause of the problem (for example, the thrown exception belongs to the JDBC connector), not as if he would accept a solution in JDBC. Janne could clear it out if JDBC is acceptable.

Janne Over a year ago

Nicolae is right, this isn't really the answer I am looking for. I still want to use Hibernate's SQLQuery

RMT Over a year ago

@Janne, im sorry I guess I didn't read the question as well as I thought, but still if you are using JDBC this is a good solution so I will not remove it because, others could learn from it. Plus it's a viable solution.

RMT Over a year ago

@Janne I was looking at the HIBERNATE api, and there is something very similar to what I had suggested(batch) docs.jboss.org/hibernate/core/3.2/api/org/hibernate/jdbc/…

|

Raffael · Accepted Answer · 2011-07-18 14:28:58Z

1

well you have to at least embrace X and Y with single quotes, I'd say.

answered Jul 18, 2011 at 14:28

Raffael

20.1k16 gold badges93 silver badges169 bronze badges

Collectives™ on Stack Overflow

Executing multiple native queries in one go

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related