java 如何判断 JDBC 执行改变的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17544782/
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
How to tell number of rows changed from JDBC execution
提问by peterh
I'm uncertain as to how to get the number of rows affected from a SQL execution.
我不确定如何获得受 SQL 执行影响的行数。
I do like this:
我喜欢这样：
boolean isResultSet = statement.execute(arbitrarySQLCommand);
and I can get the number of rows affected from the getUpdateCount()method. That is all fine. The problem I have is when update count is zero. This can either mean:
我可以获得受该getUpdateCount()方法影响的行数。这一切都很好。我遇到的问题是更新计数为零时。这可能意味着：
It was a DML statement but it didn't affect any rows. Zero rows affected is a valid response. I just means that some condition was not met.
It was a non-DML statement (DDL statement most likely) .. which by definition does not change rows so therefore update count is always zero (duh!). Or to put it another way: The concept of update countis meaningless for such statements.
这是一个 DML 语句，但它不影响任何行。受影响的零行是有效响应。我只是意味着不满足某些条件。
这是一个非 DML 语句（最有可能是 DDL 语句）……根据定义，它不会更改行，因此更新计数始终为零（废话！）。或者换一种说法：更新计数的概念对于此类语句毫无意义。
What I would like is to be able to distinguish between situation 1 and 2 above. How?
我想要的是能够区分上面的情况 1 和 2。如何？
I'm not interested in statements that produce output so I could also use executeUpdate()but as I see it the return value from that method has the same flaw:
我对产生输出的语句不感兴趣，所以我也可以使用executeUpdate()但正如我所见，该方法的返回值具有相同的缺陷：
Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing
返回：
(1) SQL 数据操作语言 (DML) 语句的行数或 (2) 0 对于不返回任何内容的 SQL 语句
Arghhh!
I wish it was:
啊！
我希望它是：
Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) -1 for SQL statements that return nothing
返回：
(1) SQL 数据操作语言 (DML) 语句的行数或 (2) -1 用于不返回任何内容的 SQL 语句
(note: I do not know the contents of arbitrarySQLCommandbeforehand)
（注：我不知道arbitrarySQLCommand事先的内容）
Final chosen solution
最终选择的解决方案
There just doesn't seem to be a true JDBC-like solution to the problem. In my mind the designers of JDBC has made a serious mistake on the getUpdateCountby using the value 0 (zero) to signify a statement that doesn't (by definition) affect rows because zero rows affected is also a perfectly valid value for the outcome of a DML statement.
似乎没有一个真正的类似 JDBC 的解决方案来解决这个问题。在我看来，JDBC 的设计者犯了一个严重的错误getUpdateCount，使用值 0（零）来表示不（根据定义）影响行的语句，因为受影响的零行也是结果的完全有效值DML 语句。
The only possible solution seems to be to do some kind of pattern matching on the SQL statement to figure out if it is a DML statement (INSERT,UPDATE,DELETE) or another type of SQL statement. Something like this:
唯一可能的解决方案似乎是对 SQL 语句进行某种模式匹配，以确定它是 DML 语句（INSERT、UPDATE、DELETE）还是其他类型的 SQL 语句。像这样的东西：
- Extract first word from
arbitrarySQLCommand. A word is terminated by either a whitespace or a EOL line char. - If that word (ignoring
case) is either INSERT, UPDATE or DELETE then it is a DML statement
and the output from
getUpdateCount()is relevant, otherwise the output fromgetUpdateCount()is irrelevant.
- 从 中提取第一个单词
arbitrarySQLCommand。一个单词以空格或 EOL 行字符结尾。 - 如果该词（忽略大小写）是 INSERT、UPDATE 或 DELETE，则它是 DML 语句并且 from 的输出
getUpdateCount()是相关的，否则 from 的输出getUpdateCount()是不相关的。
Ugly and error prone. But the only possible solution that came out of this SO question. :-(
丑陋且容易出错。但是从这个 SO 问题中得出的唯一可能的解决方案。:-(
采纳答案by sanbhat
The best you can do is checking the SQL statement
你能做的最好的事情就是检查 SQL 语句
Set<String> dmlCommands = new HashSet<String>() {
{
add("UPDATE"); add("INSERT"); add("DELETE"); //Add more DML commands ....
}
};
int updateCount = statement.getUpdateCount();
for(String dml : dmlCommands) {
if(arbitrarySQLCommand.toUpperCase().contains(dml) && updateCount == 0) {
updateCount = -1;
break;
}
}
回答by True Soft
Maybe my answer does not help you with what you asked exactly, but I got here when I searched how to get the affectedrow count after running a DML, not foundrow count - with MySQL. And I write here what I found to help others.
也许我的回答并不能帮助你解决你所问的问题，但是当我搜索如何在运行 DML 后获取受影响的行数时，我来到这里，没有找到行数 - 使用 MySQL。我在这里写下我的发现以帮助他人。
My problem was that when I run a statement that does not do any change in the table, for example
我的问题是，当我运行一个不会对表进行任何更改的语句时，例如
UPDATE table SET field=1 WHERE field=1
method Statement.executeUpdate(...)returned the number of found rows, not the number of changed/affected rows. So I couldn't tell if the query modified something in the table.
方法Statement.executeUpdate(...)返回找到的行数，而不是更改/受影响的行数。所以我无法判断查询是否修改了表中的某些内容。
To change this, an option should be provided in the url when the connection is created ( withDriverManager.getConnection()), like this:
要更改此设置，应在创建连接时在 url 中提供一个选项（ with DriverManager.getConnection()），如下所示：
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true
I found it here:
我在这里找到了：
http://mybatis-user.963551.n3.nabble.com/Return-number-of-changed-rows-tp3888464p3903155.html
http://mybatis-user.963551.n3.nabble.com/Return-number-of-changed-rows-tp3888464p3903155.html
And MySQL documentation:
和 MySQL 文档：
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html
