A query that works in management studio and in the
String query = "IF NOT EXISTS (SELECT * FROM animals WHERE animal_name ='" + a +"') INSERT INTO " + table + " (animal_name, animal_desc, species_id) VALUES ('" + a + "', '" + b + "', " + c + ")";
int result = statement.executeUpdate(query);
IF NOT EXISTS (SELECT * FROM animals WHERE animal_name = 'a')
INSERT INTO animals(animal_name, animal_desc, species_id) VALUES ('a', 'a', 1)
As the statement executed is not actually DML (eg
EXECUTE), but a piece of T-SQL which contains DML, I suspect it is not treated as an update-query.
Section 220.127.116.11 of the JDBC 4.1 specification states something (rather hard to interpret btw):
When the method
executereturns true, the method
getResultSetis called to retrieve the ResultSet object. When
executereturns false, the method
getUpdateCountreturns an int. If this number is greater than or equal to zero, it indicates the update count returned by the statement. If it is -1, it indicates that there are no more results.
Given this information, I guess that
executeUpdate() internally does an
execute(), and then - as
execute() will return
false - it will return the value of
getUpdateCount(), which in this case - in accordance with the JDBC spec - will return
This is further corroborated by the fact 1) that the Javadoc for
Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing
And 2) that the Javadoc for Statement.getUpdateCount() specifies:
the current result as an update count; -1 if the current result is a ResultSet object or there are no more results
Just to clarify: given the Javadoc for
executeUpdate() the behavior is probably wrong, but it can be explained.
Also as I commented elsewhere, the -1 might just indicate: maybe something was changed, but we simply don't know, or we can't give an accurate number of changes (eg because in this example it is a piece of T-SQL that is executed).