一次执行多个本机查询 [英] Executing multiple native queries in one go
问题描述
SQLQuery#executeUpdate()
调用执行多个以分号分隔的SQL更新查询。 我的字符串包含多个更新,如下所示:
String statements =UPDATE Foo SET bar = 1 * 30.00 WHERE baz = 1;
UPDATE Foo SET bar = 2 * 45.50 WHERE baz = 2; ...;
我正在尝试
查询查询= session.createSQLQuery(语句);
query.executeUpdate();
但是继续收到以下错误
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
您的SQL语法有错误;检查与
相对应的手册,在
附近使用正确语法的MySQL服务器版本。UPDATE Foo SET bar = Y WHERE'at line 3
如果手动执行语句
的内容,我不会收到任何错误,所以我假设了多个以分号分隔的查询在Hibernate或JDBC的某处造成麻烦。
仅仅分割语句
字符串并执行 createSQLQuery(语句)更好。每个行都单独执行executeUpdate()
?
另一种方法是使用CASE语句。例如:
UPDATE Foo
SET bar =
CASE baz
当1 THEN' X'
当2当'Y'
结束案例
在(1,2)
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 statements
string and do createSQLQuery(statement).executeUpdate()
individually for each line?
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)
这篇关于一次执行多个本机查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!