JDBC Transactions

« Previous Chapter Next Chapter »

If your JDBC Connection is in auto-commit mode, which it is by default, then every SQL statement is committed to the database upon its completion.

That may be fine for simple applications, but there are three reasons why you may want to turn off auto-commit and manage your own transactions:

  1. To increase performance

  2. To maintain the integrity of business processes

  3. To use distributed transactions

Transactions enable you to control if, and when, changes are applied to the database. It treats a single SQL statement or a group of SQL statements as one logical unit, and if any statement fails, the whole transaction fails.

To enable manual- transaction support instead of the auto-commit mode that the JDBC driver uses by default, use the Connection object's setAutoCommit() method. If you pass a boolean false to setAutoCommit( ), you turn off auto-commit. You can pass a boolean true to turn it back on again.

For example, if you have a Connection object named conn, code the following to turn off auto-commit:

Example

conn.setAutoCommit(false);

Commit & Rollback

Once you are done with your changes and you want to commit the changes then call commit() method on connection object as follows:

Example

conn.commit( );

Otherwise, to roll back updates to the database made using the Connection named conn, use the following code:

Example

conn.rollback( );

The Below example illustrates the use of a commit and rollback object:

Example

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Friends  " +
                "VALUES (106, 20, 'Mascrow', 'Chen')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Friends  " +
                "VALUES (107, 22, 'Adim', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error.
   conn.commit();
}catch(SQLException se){
   // If there is any error.
   conn.rollback();
}

In this case none of the abobe INSERT statement would success and everything would be rolled back.

For better understanding, Learn Commit - Example Code.

Using Savepoints:

The new JDBC 4.2 Savepoint interface gives you additional transactional control. Most modern DBMS support savepoints within their environments such as Oracle's PL/SQL.

When you set a savepoint you define a logical rollback point within a transaction. If an error occurs past a savepoint, you can use the rollback method to undo either all the changes or only the changes made after the savepoint.

The Connection object has two new methods that help you manage savepoints:

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

  2. releaseSavepoint(Savepoint savepointName): deletes a savepoint. Note: it requires a Savepoint object as a parameter. This object is usually a savepoint generated by the setSavepoint() method.

There is one rollback ( String savepointName ) method which rolls back work to the specified savepoint.

The Below example illustrates the use of a Savepoint object:

Example

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   //set a Savepoint
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Friends " +
                "VALUES (106, 20, 'Mascrow', 'Chen')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Friends " +
                "VALUES (107, 22, 'Adim', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error, commit the changes.
   conn.commit();
}catch(SQLException se){
   // If there is any error.
   conn.rollback(savepoint1);
}

In this case none of the abobe INSERT statement would success and everything would be rolled back.

For better understanding, Learn Savepoints - Example Code.


« Previous Chapter Next Chapter »

Have Any Suggestion? We Are Waiting To Hear from YOU!

Your Query was successfully sent!