17

Does Java Connection.close rollback into a finally block?.

I know .Net SqlConnection.close does it.

With this I could make try/finally blocks without catch...

Example:

try {
    conn.setAutoCommit(false);
    ResultSet rs = executeQuery(conn, ...);
    ....
    executeNonQuery(conn, ...);
    ....

    conn.commit();
} finally {
   conn.close();
}
Antonio
  • 321
  • 2
  • 4
  • 9

6 Answers6

30

According to the javadoc, you should try to either commit or roll back before calling the close method. The results otherwise are implementation-defined.

Joel
  • 3,435
  • 2
  • 23
  • 33
11

In any database system I've worked with, there is no harm in doing a rollback right after the commit, so if you commit in the try block, and rollback in the finally, things get committed, whereas if an exception or early return causes the commit to be missed, the rollback will rollback the transaction. So the safe thing to do is

try {
    conn.setAutoCommit(false);
    ResultSet rs = executeQuery(conn, ...);
    ....
    executeNonQuery(conn, ...);
    ....

    conn.commit();
} finally {
   conn.rollback();
   conn.close();
}
Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • 1
    SQL Server will complain about this if I'm not mistaken –  Feb 28 '13 at 12:21
  • What is the use of `commit()` if I always `rollback()` it at the end? – Saleh Rezq Jul 05 '22 at 17:29
  • 1
    @SalehRezq if you don’t commit, the rollback rolls back everything you’ve done. The commit is there to save the changes and unless there’s an exception, the rollback is a no-op. – Paul Tomblin Jul 06 '22 at 20:09
6

The behavior is completely different between different databases. Examples:

Oracle

The transaction is committed when closing the connection with an open transaction (as @Mr. Shiny and New 安宇 stated.

SQL Server

Calling the close method in the middle of a transaction causes the transaction to be rolled back.

close Method (SQLServerConnection)

lbergnehr
  • 1,578
  • 11
  • 15
6

Oracle's JDBC driver commits on close() by default. You should not rely on this behaviour if you intend to write multi-platform JDBC code.

Mr. Shiny and New 安宇
  • 13,822
  • 6
  • 44
  • 64
0

For MySQL JDBC, the implementation rolls back the connection if closed without a call to commit or rollback methods.

Adnan Memon
  • 443
  • 5
  • 10
-1

It is useless to rollback in finally block. After you commit, and commit is successful, why to roll back? So if i were you, i would rollback in catch block.

jb.
  • 23,300
  • 18
  • 98
  • 136
Mean
  • 1
  • 2
    It's not useless. If an exception occurs before the commit() but after a successful execution in the current transaction, then you most likely want to rollback that transaction before exiting the block with access to the connection. – Kirby Jan 24 '14 at 19:49