COMMIT OR conn.setAutoCommit(true)

Question

I have noticed some programmer using COMMIT other using conn.setAutoCommit(true); to end the transaction or roll back so what are the benefits of using one instead of the other?

Where is the main difference?

conn.setAutoCommit(true);

over

statement.executeQuery(query);
statement.commit();

Answer 1

You should in general use Connection.commit() and not Connection.setAutoCommit(true) to commit a transaction, unless you want to switch from using transaction to the 'transaction per statement' model of autoCommit.

That said, calling Connection.setAutoCommit(true) while in a transaction will commit the transaction (if the driver is compliant with section 10.1.1 of the JDBC 4.1 spec). But you should really only ever do that if you mean to stay in autoCommit after that, as enabling / disabling autoCommit on a connection may have higher overhead on a connection than simply committing (eg because it needs to switch between transaction managers, do additional checks, etc).

You should also use Connection.commit() and not use the native SQL command COMMIT. As detailed in the documentation of connection:

Note: When configuring a Connection, JDBC applications should use the appropritate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available.

The thing is that commands like commit() and setAutoCommit(boolean) may do more work in the back ground, like closing ResultSets and closing or resetting Statements. Using the SQL command COMMIT will bypass this and potentially bring your driver / connection into an incorrect state.

Answer 2

The usage of conn.setAutoCommit(); applies to the connection and gives you the possibility to execute X queries in a single transaction, or use one single transaction per execute

As the API describes:

 void setAutoCommit(boolean autoCommit)
                    throws SQLException

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode

For a simple case:

conn.setAutoCommit(false); 
statement.executeQuery(query); 
statement.commit();

will be the same as:

conn.setAutoCommit(true); 
statement.executeQuery(query);