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();
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.
