How to tell number of rows changed from JDBC execution

Question

I'm uncertain as to how to get the number of rows affected from a SQL execution.

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:

1. 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.

2. 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 count is meaningless for such statements.

What I would like is to be able to distinguish between situation 1 and 2 above. How?

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:

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

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

(note: I do not know the contents of arbitrarySQLCommand beforehand)

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 getUpdateCount by 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.

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:

1. Extract first word from arbitrarySQLCommand. A word is terminated by either a whitespace or a EOL line char.
2. 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 from getUpdateCount() is irrelevant.

Ugly and error prone. But the only possible solution that came out of this SO question. :-(

Answer 1

Maybe my answer does not help you with what you asked exactly, but I got here when I searched how to get the affected row count after running a DML, not found row count - with MySQL. And I write here what I found to help others.

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.

To change this, an option should be provided in the url when the connection is created ( withDriverManager.getConnection()), like this:

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

And MySQL documentation:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

Answer 2

The best you can do is checking the SQL statement

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;
    }
}