How to determine the number of rows modified from JDBC execution

Question

I'm not sure how to get the number of rows that affected SQL execution.

I like the following:

boolean isResultSet = statement.execute(arbitrarySQLCommand); 

and I can get the number of rows affected by the getUpdateCount() method. Everything is good. I have a problem when the number of updates is zero. It could mean:

• It was a DML statement, but it did not affect any lines. Fixed zero line level. I just want to say that some condition was not met.

• It was a non-DML operator (most likely a DDL operator) .. which, by definition, does not change lines, so the update counter is always zero (duh!). Or put it another way: the concept of counting updates is meaningless for such statements.

I would like to be able to distinguish between situations 1 and 2 above . How?

I'm not interested in statements that produce output, so I could also use executeUpdate () , but as I can see, this return value from this method has the same drawback:

Return:

either (1) the number of rows for SQL Data Manipulation Language (DML) statements, or (2) 0 for SQL statements that return nothing

Arghhh!
I would like to:

Return:

either (1) the number of rows 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 in advance)

Final decision chosen

It just doesn't seem like a true solution to the JDBC problem. In my opinion, JDBC designers made a serious mistake in getUpdateCount , using the value 0 (zero) to indicate an operator that does not (by definition) affect the strings, because the null strings affected are also a valid value for the result of the DML statement.

The only possible solution seems to be to do some pattern matching in the SQL statement to find out if it is a DML statement (INSERT, UPDATE, DELETE) or another type of SQL statement. Something like that:

• Extract the first word from arbitrarySQLCommand . The word is terminated by either a space or an EOL char line.
• If this word (ignoring case) is either INSERT, UPDATE, or DELETE, then this is a DML statement and exit from getUpdateCount() matters, otherwise the output from getUpdateCount() does not matter.

Ugliness and penchant for mistakes. But the only possible solution that came out of this SO question: - (

Answer 1

The best you can do is check 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; } } 

Answer 2

Maybe my answer will not help you in what you asked for exactly, but I came here when I was looking for how to get the number of rows affected after running DML, rather than 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 change the tables, for example

 UPDATE table SET field=1 WHERE field=1 

the Statement.executeUpdate(...) method returned the number of rows found, not the number of rows affected / affected. Therefore, I could not say if the query changed something in the table.

To change this, the option must be specified in the URL when creating the connection (using DriverManager.getConnection() ), for example:

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 the MySQL documentation:

http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html