JDBC : AutoCommit Mode set to False

JDBC : AutoCommit Mode set to False

1. When we establish the connection to the Data Base from JDBC application i.e. the default mode of the connection is in auto commit mode.

2. In this auto commit mode, the execution result of every SQL command that we send to the Data Base is automatically committed/reflected into the physical Data Base.

3. We use the following java code in JDBC application to change the mode of connection into non-auto commit mode. i.e.

con.setAutoCommit(false);

Where setAutoCommit() is the method of Connection interface object that will change the Connection mode into the non auto commit mode so the signature of the method is

public void setAutoCommit(boolean mode);

4. In this non-autocommit mode, the execution result of every SQL command that we send to the DB is temporarily stored into a buffer at the DB engine without committing into DB/reflecting into physical DB.

5. We use the following java code in JDBC application to send commit signal to the DB engine so that it will commit all results of the buffer into the physical DB and then buffer is refreshed or cleared.

con.commit();

Where commit() is the method of Connection interface that will send a commit signal to DB engine. So the signature is

public void commit();

6. We use the following java code in JDBC application to send a rollback signal to DB engine so that it will refresh all the results of the buffer without committing them into the physical DB.

con.rollback();

The following example will describe how to change the connection mode into non auto commit mode.

package com.pack1;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class AutoCommitFalse {

    public static void main(String[] args) throws Exception

    {      

            Class.forName("org.apache.derby.jdbc.ClientDriver");

            String url = "jdbc:derby://localhost:1527/myjdbc";

            Connection  con = DriverManager.getConnection(url, "app","app");

            System.out.println(con.getAutoCommit());           

            con.setAutoCommit(false);           

            System.out.println(con.getAutoCommit());           

            Statement stmt = con.createStatement();

            stmt.executeUpdate("insert into MYEMP values(901,'Mohan1',8001,'USA')");

            stmt.executeUpdate("insert into MYEMP values(902,'Mohan1',8001,'USA')");

            stmt.executeUpdate("insert into MYEMP values(903,'Mohan1',8001,'USA')");

            con.commit();

            stmt.executeUpdate("insert into MYEMP values(904,'Mohan1',8001,'USA')");

            con.rollback();

            stmt.executeUpdate("insert into MYEMP values(905,'Mohan1',8001,'USA')");

            con.commit();            

    }

}

Output:

We can see only 4 records in DB i.e. 901,902,903 and 905 as we committed them into DB (Not 904)