getUpdateCount returns total number of rows updates for Oracle, but returns -1 in PostgreSQL and SQL Server, and latest count in H2

Question

I am trying batch update and then get total number of rows affected. For Oracle I get correct output by using getUpdateCount, but this API fails for PostgreSQL, SQL Server and H2. For these databases I need to iterate and add executeBatch output.

PreparedStatement preparedStatement = connection.prepareStatement(getSQL());
//created queries via addBatch()
int[] ints = preparedStatement.executeBatch();
connection.commit();
int updateCount = preparedStatement.getUpdateCount();
Assert.assertEquals(updateCount,3);
Assert.assertNotNull(ints);

I am curious why this API does not give correct output? My drivers are as follows.

<dependency>
    <groupId>com.oracle.ojdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.3.0.0</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.2.0.jre8</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
    <scope>test</scope>
</dependency>

Answer 1

In the case of executeBatch, you shouldn't check getUpdateCount() at all, you should look at the returned array only. The behaviour for getUpdateCount() is only specified in combination with execute() and getMoreResults(). Its behaviour is unspecified by JDBC for any other method of execution, so you cannot rely on any specific behaviour (i.e. it is driver-specific) if you didn't call execute() or getMoreResults() (which you can by specification only call after you've used execute()).

Specifically, the JDBC 4.3 specification only covers getUpdateCount() in section 13.1.2.3 Returning Unknown or Multiple Results, which describes the use of execute, getMoreResults and getUpdateCount() and getResultSet(), and ties the behaviour of those methods specifically to execute.

That said, with singular execution using executeQuery() and executeUpdate() most drivers will usually be pretty consistent as if execute() had been used (though you cannot rely on this!), but with executeBatch() it will highly depend on how the batch execution is implemented and how update counts are retrieved.

For example, if the driver emulates batch support by executing the parameter sets individually, it is highly likely that getUpdateCount() returns the last update count (probably by accident of implementation of the normal execution), while if the DBMS supports server-side batch execution, it is unlikely that you get an update count other than -1, because it would require implementing explicitly summing the update counts of the individual parameter sets, unless maybe the DBMS also reports the total update count of the entire batch in some way.

In short, the behaviours you have observed for Oracle, PostgreSQL, SQL Server and H2 are all correct, because it is unspecified by JDBC.