CODEWITHSUNDEEP
javadatabaseoracle-databasejdbc
Kisslaay
Kisslaay

Reputation: 41

What is use of Connection.setAutoCommit(false); when using Oracle database?

I was setting con.setAutoCommit(false); as soon as I create connection so that nothing goes in DB uncommitted. But it turns out if you close the connection all transaction will be committed, no matter what your setAutoCommit() status is.

    Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.7.5:xxxx:xxx", "xxx", "xxx");

        con.setAutoCommit(false);

        st = con.createStatement();
        String sql = "INSERT INTO emp (eid, name, dob, address) VALUES (?, ?, ?, ?)";
        PreparedStatement statement = con.prepareStatement(sql);
        statement.setInt(1, 8 );
        statement.setString(2, "kkk" );
        statement.setDate(3, date );
        statement.setString(4, "pppp" );
        pst = con.prepareStatement(sql);
        int rowsInserted = statement.executeUpdate();

        //con.commit ();

        System.out.println ("rowsInserted "+rowsInserted);

        con.close ();

Even after commenting con.commit (); row is being inserted when connection closes so I was wondering what is the use of con.commit (); ?

Another answer says it's vendor specific:

If a Connection is closed without an explicit commit or a rollback; JDBC does not mandate anything in particular here and hence the behaviour is dependent on the database vendor. In case of Oracle, an implict commit is issued.

It does not make sense.Thanks.

Upvotes: 2

Views: 6000

Answers (3)

Jean de Lavarene
Jean de Lavarene

Reputation: 3763

Logging off of Oracle commits any pending transaction. This happens whether you use sqlplus or the JDBC driver through conn.close(). Note that it's not the driver that issues the commit it's the server. During logoff the server commits pending changes. From your Java program you can always call conn.rollback() before calling conn.close() if you want to make sure that pending changes are not committed.

You asked what is the use of conn.commit(). It's used to explicitly commit a transaction at a specific point of your business logic. For example if you cache connections in a connection pool, you can disable auto-commit and commit pending changes before releasing the connection back to the pool. Some people prefer enabling auto-commit mode (which is the default in JDBC) and not worry about explicitly committing or rolling back changes. It depends on your business logic. You can ask yourself: will I ever need to rollback a DML execution? If the answer is yes then you should disable auto-commit and explicitly commit transactions.

Upvotes: 2

N00b Pr0grammer
N00b Pr0grammer

Reputation: 4647

Oracle documentation provides a very good explanation of when and why this should be used. Please go through the same!

If your JDBC Connection is in auto-commit mode pro grammatically or by default, (this is by default, fyi), then every SQL statement is committed to the database upon its completion.

You can refer to this question for more detailed explanation on the same topic.

Upvotes: 1

atokpas
atokpas

Reputation: 3351

con.commit() is an explicit commit and you can call it whenever you have to commit the transaction. In your case there is no explicit commit or rollback though you have set AutoCommit to false. The Oracle Database commits all the transactions of a session which exits the connection gracefully. If the session terminates abnormally then it rolls back the transactions.

Upvotes: 1

Related Questions