JDBC Tutorial - JDBC Transactions

• « Previous
• Next »

Transactions treats a group of SQL statements as one logical unit, if any statement fails, the whole transaction fails and rolls back.

By default the JDBC Connection is in auto-commit mode, which means every SQL statement is committed to the database when it is completed.

To enable manual-transaction, use the Connection object's setAutoCommit() method.

For example, the following code turns off auto-commit:

conn.setAutoCommit(false);

To commit the changes then call commit() method on connection object as follows:

conn.commit( );

To roll back updates to the database, use the following code:

conn.rollback( );

The following example shows how to use commit and rollback.

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees VALUES (1, 'name')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, 'anotherName')";
   stmt.executeUpdate(SQL);
   conn.commit();
}catch(SQLException se){
   conn.rollback();
}

Using Savepoints

A savepoint defines a rollback point within a transaction.

If an error occurs after a savepoint, we can rollback to undo either all the changes or only the changes made after the savepoint.

The Connection object has two methods to related to savepoints.

setSavepoint(String savepointName) defines a new savepoint. It also returns a Savepoint object.

releaseSavepoint(Savepoint savepointName) deletes a savepoint. It requires a Savepoint object as a parameter, which is generated by the setSavepoint() method.

rollback(String savepointName) method rolls back work to the specified savepoint.

The following example illustrates the use of a Savepoint object:

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Employees VALUES (1, 'name')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, 'new name')";
   stmt.executeUpdate(SQL);
   conn.commit();

}catch(SQLException se){
   conn.rollback(savepoint1);
}

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/*w w w  .  j a v a  2s .co  m*/
public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION,
        DB_USER, DB_PASSWORD);

    PreparedStatement preparedStatementInsert = null;
    PreparedStatement preparedStatementUpdate = null;

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)";

    String updateTableSQL = "UPDATE Person SET USERNAME =? "
        + "WHERE USER_ID = ?";

    java.util.Date today = new java.util.Date();
    dbConnection.setAutoCommit(false);

    preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    preparedStatementInsert.setInt(1, 9);
    preparedStatementInsert.setString(2, "101");
    preparedStatementInsert.setString(3, "system");
    preparedStatementInsert.setTimestamp(4,
        new java.sql.Timestamp(today.getTime()));
    preparedStatementInsert.executeUpdate();

    preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    preparedStatementUpdate.setString(1, "new string");
    preparedStatementUpdate.setInt(2, 999);
    preparedStatementUpdate.executeUpdate();

    dbConnection.commit();
    dbConnection.close();
  }
}

• Next »
• « Previous