oracle.jdbc.autoCommitSpecCompliant VS setAutoCommit(false)

Question

We have legacy application using plain JDBC to connect with an Oracle database. Recently, that database has been upgraded to Oracle 19c.

For that we are using below dependency now.

<dependency>
    <groupId>com.oracle.ojdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.3.0.0</version>
</dependency>

After upgrade, the application started giving errors due to obvious reasons like Result not closed before Callable, during rollback() etc.

I found below two approaches that are working.

System.setProperty ("oracle.jdbc.autoCommitSpecCompliant", "false"); 

or

connection.setAutoCommit(false);

I want to know what is the difference between above two solutions, and which one is better.

Ref link -
https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_AUTO_COMMIT_SPEC_COMPLIANT

Answer 1

From the documentation you linked to, changing oracle.jdbc.autoCommitSpecCompliant:

Alters the auto-commit behavior of the driver. By default the driver complies with JDBC specification. User can choose to alter the behavior by changing the value of this flag.

Whereas connection.setAutoCommit(false);:

Sets this connection's auto-commit mode to the given state.

This later call only changes a single connection's auto-commit state and does not change any other connections' auto-commit state; the former changes the default auto-commit behaviour of all future connections.

Which one is better.

Neither, they have different use cases and you should use whichever is most appropriate to your use case.

If you have multiple connections that all need to not auto-commit as their default behaviour then you could set the value on the driver. (Do not do this if you have connections with a mix of auto-commit states, especially if some applications are relying on the default state when auto-commit is true.)

If you want to handle the connections individually then set the state on each connection.