mysql两个分号,我可以用MySQL Connector / J执行多个以分号分隔的查询吗？

My jdbc driver for mysql db is of version 5.1.25.

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");

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.

解决方案

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 one actual 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.

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 allowMultiQueries on Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J