Java 我可以使用 MySQL Connector/J 执行以分号分隔的多个查询吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18515471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I execute multiple queries separated by semicolon with MySQL Connector/J?
提问by Volodymyr Levytskyi
My jdbc driver for mysql db is of version 5.1.25.
我用于 mysql db 的 jdbc 驱动程序的版本是 5.1.25。
I want to execute sql query like so:
我想像这样执行 sql 查询：
statement.execute("select fullName from user where user_id=1; select fullName from user where user_id=2");
And I always receive exception:
而且我总是收到异常：
Exception in thread "main" 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 'select fullName from user where user_id=2' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
at dbViewer.model.UserConnectionManager.retrieveRoutinesNames1(UserConnectionManager.java:622)
at dbViewer.model.UserConnectionManager.main(UserConnectionManager.java:637)
BUT when I run this same query(separated by semicolon) from command line it works perfectly and outputs two tables as expected.
但是，当我从命令行运行相同的查询（用分号分隔）时，它可以完美运行并按预期输出两个表。
回答by roehrijn
No you can't. What are you expecting to get by calling statement.execute(...)? It returns one ResultSet (... which means one table).
不，你不能。你期望通过调用 statement.execute(...) 得到什么？它返回一个 ResultSet （...这意味着一张表）。
You can just call "select fullName from user where user_id in (1, 2)" to geht back both results.
您可以调用“select fullName from user where user_id in (1, 2)”来获取两个结果。
Having semicolons in JDBC statements is very error prone in general. Some JDBC drivers do not support this (e.g. IBM's JDBC driver for DB2 10.x throws an exception if you close your SQL statement with ";").
在 JDBC 语句中使用分号通常很容易出错。某些 JDBC 驱动程序不支持此功能（例如，如果您用“;”关闭 SQL 语句，IBM 的 DB2 10.x JDBC 驱动程序会抛出异常）。
回答by Mark Rotteveel
Using ;in a query for most databases doesn't work as it is usually not part of the statement syntax itself, but a terminator for command line or script input to separate statements. The command line or script processor sees a semi-colon as the signal that the statement is complete and can be sent to the server.
;在大多数数据库的查询中使用不起作用，因为它通常不是语句语法本身的一部分，而是用于分隔语句的命令行或脚本输入的终止符。命令行或脚本处理器将分号视为语句完成并可以发送到服务器的信号。
Also in JDBC a single statement prepare (or execute) should only be oneactual statement so multiple statements are not allowed and so there is also no need to have a semi-colon, and as for some (most?) databases the semi-colon isn't part of the statement syntax, it is simply a syntax error to have one included.
同样在 JDBC 中，单个语句准备（或执行）应该只是一个实际语句，因此不允许多个语句，因此也不需要分号，对于某些（大多数？）数据库，分号不是语句语法的一部分，包含它只是一个语法错误。
If you want to execute multiple statements, you need to use separate executes. Technically, MySQL does have an option to support multiple executions which can be enabled by a connection property. This behavior is not compliant with the JDBC specification/API and makes your code less portable. See allowMultiQuerieson Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
如果要执行多个语句，则需要使用单独的executes。从技术上讲，MySQL 确实有一个选项来支持可以通过连接属性启用的多个执行。此行为不符合 JDBC 规范/API 并降低您的代码的可移植性。见allowMultiQueries在驱动程序/数据源类名，URL语法和配置属性连接器/ J
回答by Ravinder Reddy
I want to execute sql query like so:
statement.execute("select fullName from user where user_id=1; select fullName from user where user_id=2");
我想像这样执行 sql 查询：
statement.execute("select fullName from user where user_id=1; select fullName from user where user_id=2");
This is possible only when you have set one database connection property to allow multiple queries to execute all at once. And the property name is allowMultiQueries=true. This property has to be set and send along with a database connection request to the server. General syntax is like this:
只有当您设置了一个数据库连接属性以允许同时执行多个查询时，这才是可能的。并且属性名称是allowMultiQueries=true. 此属性必须设置并与数据库连接请求一起发送到服务器。一般语法是这样的：
String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";
This is additional connection property to those if already exists some, like autoReConnect=true, etc.
这是对那些已经存在的附加连接属性，例如autoReConnect=true，等。
Acceptable values for allowMultiQueriesproperty are true, false, yes, and no. Any other value is rejected at runtime with an SQLException.
可接受值allowMultiQueries属性是true，false，yes，和no。任何其他值在运行时都会被拒绝，并带有SQLException.
You have to use execute( String sql )or its other variants to fetch results of the query execution.
您必须使用execute( String sql )或其其他变体来获取查询执行的结果。
multiQuerySqlString = "select fullName from user where user_id=1; ";
multiQuerySqlString += "select fullName from user where user_id=2; ";
// you can multiple types of result sets
multiQuerySqlString += "select last_login from user_logs where user_id=1; ";
boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );
To iterate through and process results you require following steps:
要迭代和处理结果，您需要执行以下步骤：
int rsNumber = 0;
while ( hasMoreResultSets ) {
rsNumber += 1;
Resultset rs = stmt.getResultSet();
// based on the structure of the result set,
// you can handle column values.
if ( rsNumber == 1 ) {
while( rs.next() ) {
// handle your rs here
} // while rs
} // if rs is 1
else if ( rsNumber == 2 ) {
// call a method using this rs.
processMyResultSet( rs ); // example
} // if rs is 2
// ... etc
// check whether there exist more result sets
hasMoreResultSets = stmt.getMoreResults();
} // while results
Refer to:
参考：
- Multiple queries executed in java in single statement
- One of the similar postings on SO, for which I gave an answer.
- 在单个语句中在 Java 中执行的多个查询
- SO上的类似帖子之一，对此我给出了答案。
