我可以用MySQL Connector / J执行多个以分号分隔的查询吗？ [英] Can I execute multiple queries separated by semicolon with MySQL Connector/J?

问题描述

我的mysql db的jdbc驱动程序是版本5.1.25。

My jdbc driver for mysql db is of version 5.1.25.

我想像这样执行sql查询：

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

我总是收到异常：

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.

同样在JDBC中，单个语句准备（或者执行）应该只是一个实际语句，因此不允许多个语句，因此也不需要使用分号，而对于某些（大多数？）数据库，分号不是这是语句语法的一部分，只包含一个语法错误。

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.

如果要执行多个语句，则需要使用单独的执行。从技术上讲，MySQL确实有一个选项可以支持多个执行，这些执行可以通过连接属性启用。此行为不符合JDBC规范/ API，并使您的代码不那么可移植。请参阅 allowMultiQueries rel =noreferrer>连接器/ J的驱动程序/数据源类名，URL语法和配置属性

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

这篇关于我可以用MySQL Connector / J执行多个以分号分隔的查询吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！