Java 无法使用 executeQuery() 发出数据操作语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6454627/
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 not issue data manipulation statements with executeQuery()
提问by Mediator
I use com.mysql.jdbc.Driver
我用 com.mysql.jdbc.Driver
I need insert and get id. My query:
我需要插入并获取 id。我的查询：
INSERT INTO Sessions(id_user) VALUES(1);
SELECT LAST_INSERT_ID() FROM Sessions LIMIT 1;
error -
错误 -
Can not issue data manipulation statements with executeQuery()
无法使用 executeQuery() 发出数据操作语句
How insert and get id?
如何插入并获取id？
采纳答案by Mediator
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet generatedKeys = null;
try {
connection = m_Connection;
preparedStatement = (PreparedStatement) connection.prepareStatement(qString, Statement.RETURN_GENERATED_KEYS);
// ...
int affectedRows = preparedStatement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating user failed, no rows affected.");
}
generatedKeys = preparedStatement.getGeneratedKeys();
int id = -1;
if (generatedKeys.next()) {
id = generatedKeys.getInt(1);
id = -1;
} else {
throw new SQLException("Creating user failed, no generated key obtained.");
}
} finally {
}
回答by Johan
For non-select SQL statements you use ExecuteNonQuery();
对于您使用的非选择 SQL 语句 ExecuteNonQuery();
To get the last inserted id, you can do this SQL statement.
要获取最后插入的 id，可以执行此 SQL 语句。
SELECT LAST_INSERT_ID() AS last_id
Although there's probably an java wrapper for that select statement.
尽管该 select 语句可能有一个 java 包装器。
Links:
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://wiki.bibalex.org/JavaDoc/org/bibalex/daf/handlers/dbhandler/DBConnection.html
链接：
http: //dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://wiki.bibalex.org/JavaDoc/org/bibalex/daf/handlers/dbhandler/DBConnection .html
回答by Vineet Reynolds
You will need to use the executeUpdate() method to execute the INSERT statement, while you'll need to use the executeQuery() method to execute the SELECT statement. This is due to the requirements imposed by the JDBC specification on their usages:
您将需要使用 executeUpdate() 方法来执行 INSERT 语句，而您需要使用 executeQuery() 方法来执行 SELECT 语句。这是由于 JDBC 规范对其使用的要求：
From the Java API documentation for Statement.executeQuery():
从Statement.executeQuery()的 Java API 文档中：
Executes the given SQL statement, which returns a single ResultSet object.
Parameters:
sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement
执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。
参数：
sql - 要发送到数据库的 SQL 语句，通常是静态 SQL SELECT 语句
and from the Java API documentation for Statement.executeUpdate():
以及来自Statement.executeUpdate()的 Java API 文档：
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Parameters:
sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
执行给定的 SQL 语句，它可以是 INSERT、UPDATE 或 DELETE 语句，也可以是不返回任何内容的 SQL 语句，例如 SQL DDL 语句。
参数：
sql - SQL 数据操作语言 (DML) 语句，例如 INSERT、UPDATE 或 DELETE；或不返回任何内容的 SQL 语句，例如 DDL 语句。
Your code (pseudo-code posted here) should appear as:
您的代码（此处发布的伪代码）应显示为：
statement.executeUpdate("INSERT INTO Sessions(id_user) VALUES(1)"); // DML operation
statement.executeQuery("SELECT LAST_INSERT_ID()"); // SELECT operation
And of course, the MySQL documentation demonstrates how to perform the same activity for AUTO_INCREMENT columns, which is apparently what you need.
当然，MySQL 文档演示了如何为 AUTO_INCREMENT 列执行相同的活动，这显然是您所需要的。
If you need to execute both of them together in the same transaction, by submitting the statements in one string with a semi-colon separating them like the following:
如果您需要在同一事务中同时执行这两个语句，请提交一个字符串中的语句，并用分号分隔它们，如下所示：
statement.execute("INSERT INTO Sessions(id_user) VALUES(1); SELECT LAST_INSERT_ID() FROM Sessions LIMIT 1;");
then you'll need to use the execute() method. Note, that this depends on the support offered by the Database and the JDBC driver for batching statements together in a single execute(). This is supported in Sybase and MSSQL Server, but I do not think it is supported in MySQL.
那么你需要使用 execute() 方法。请注意，这取决于数据库和 JDBC 驱动程序对在单个 execute() 中批处理语句的支持。这在 Sybase 和 MSSQL Server 中受支持，但我认为在 MySQL 中不支持。
回答by Viraj Nalawade
may be you are using executeQuery() but to manipulate data you actually need executeUpdate() rather than executeQuery()
可能您正在使用 executeQuery() 但要操作数据您实际上需要 executeUpdate() 而不是 executeQuery()
