JDBC and Oracle conn.commit and conn.setAutocommit not working properly

Question

I have made a DBManager class as shown below

public class DBManager {


      public static String DRIVER = "oracle.jdbc.driver.OracleDriver";
      public static String URL = "jdbc:oracle:thin:@//localhost:1521/DB";
      public static String USERNAME = "afsweb";
      public static String PASSWORD = "afsweb";
      public static String DOCDBUSERNAME = "docdb";
      public static String DOCDBPASSWORD = "docdb";
      public static int PORT = 1521;

    //static Logger log = Logger.getLogger(ExcelDBManager.class.getName());
    public static Connection getConnection(String url ,String username, String password){
    try {
        Class.forName(DRIVER);
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    Connection con = null;
    try {
        con = DriverManager.getConnection(url,username,password);
        con.setAutoCommit(false);
        } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();

    }
    return con;
}

And i have method truncate rows in table

public static void truncate() throws SQLException{
        conn = DBManager.getConnection(DBManager.URL, DBManager.USERNAME, DBManager.PASSWORD);
        System.out.println(conn.getAutoCommit()  +"");
        Statement pstmnt = null;
        ResultSet rs = null;
        try{    
            pstmnt = conn.createStatement();
            pstmnt.executeQuery("truncate table bd_vehicles_temp_1");
            System.out.println("Query Executed");
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            try{
            if(rs !=null){
                rs.close();
            }
            if(pstmnt != null){
                pstmnt.close();
            }
            if(conn != null){
                conn.close();
            }
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

Now i have not written conn.commit inside my truncate() method. Also i have setAutocommit to false. Even then the changes are reflected in database.

On Executing the above method i get output as

false
Query Executed

Which means my connections autocommit mode is false. Still the changes made by truncate method is reflected in database. What could be the possible reason ?? I am using Oracle Database.

Thanks in Advance !

Answer 1

TRUNCATE is a Data Definition Language (DDL) command which commits implicitly. It wouldn't have committed anything, had you used the DELETE statement instead.

// Deletes ALL Rows; No WHERE Clause
pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_");

The reason TRUNCATE is a DDL statement is that it removes the table data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster but cannot be rolled back.

EDIT : (Why my INSERTs are committing as well?)

That's because you're closing your Connection without calling Connection#rollback().

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 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.

SO, just rollback() your changes before closing your Connection in the finally block

pstmnt = conn.createStatement();

pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_1");
System.out.println("Query Executed");

conn.rollback();
System.out.println("Changes rolled back");

Answer 2

TRUNCATE TABLE basically doesn't allow commit/rollback in the normal way. As per this documentation:

Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.

If you want to do this as part of a transaction, use DML instead - e.g. a normal DELETE FROM ... statement.

Answer 3

Oracle truncate command is DDL and it issues commit implicitly. See http://docs.oracle.com/cd/E17952_01/refman-5.5-en/truncate-table.html

Answer 4

Autocommit behavior depends on the underlying database you use. An unfortunately in your case Oracle's JDBC driver commits on close() by default.