CODEWITHSUNDEEP
javajdbcautocommitexecutequery
eatSleepCode
eatSleepCode

Reputation: 4637

After an Exception, closing the Connection appears to commit the transaction even though AutoCommit is false

setAutoCommit is false and exception is thrown before closing the connection, but still its committing the transaction. Isn't this is strange behavior?

public static void insertEmployee(){

        String query1 = "INSERT INTO EMPLOYEE VALUES(80, 'from code')";
        String query2 = "INSERT INTO EMPLOYEE VALUES(81, 'from code')";
        Connection connection = null;
        Statement statement = null;
        try {
            connection =   DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            statement.executeUpdate(query1);
            ResultSet resultSet = statement.executeQuery(query2);
            while(resultSet.next()) //this code throws the exception kept like this intentionally
            {
                int empNo = resultSet.getInt("EMPLOYEE_ID");
                String eName = resultSet.getString("EMPLOYEE_NAME");
                System.out.println("eName = " + eName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Upvotes: 3

Views: 2329

Answers (1)

Mureinik
Mureinik

Reputation: 311723

setting auto-commit to false means that a statement's changes won't be committed right after it's executed. It does not [necessarily] affect, however, the behavior of close(), which may choose to either commit or rollback uncommitted data. As the documentation states:

It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.

In other words, regardless of the auto commit flag, you should always explicitly commit() or rollback() a Connection object before close()ing it:

try {
    // DML operations here

    // Explicitly commit if we got this far
    connection.commit();
} catch (SQLException e) {
    // If an exception occurred, explicitly rollback:
    connection.rollback();

    // Log somehow
    e.printStackTrace();
 } finally {
    // Close resources 
 }

Upvotes: 5

Related Questions