Header javaperspective.com
JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.7. Transactions

2.7. Transactions
Last updated: 1 January 2013.

This tutorial discusses transactions with JDBC. All the classes and interfaces used in the code samples of this page belong to the package java.sql.

There are situations where you need to execute a series of SQL statements in which none must fail. For example, during a money transfer from one bank account to another, two consecutive SQL statements are issued:In such a case, both statements must succeed. Otherwise, there is data inconsistency.



A transaction uses a single connection to execute a series of SQL statements as a whole rather than executing them as individual statements. When a new connection is created or borrowed from a connection pool, it is by default in auto-commit mode. In that case, each SQL statement is immediately committed after its execution. If you want to bundle multiple statements into a single transaction, you have to disable the auto-commit mode by calling the method setAutoCommit like this:

connection.setAutoCommit(false);

Once you have disabled the auto-commit mode, the SQL statements bundled into the transaction are not commited after their execution unless you call the method commit explicitly. Also, if an exception occurs during the transaction, you can undo the changes potentially made in the database by calling the method rollback.

As an example, suppose that you are using an Oracle database in which there is a table named ACCOUNTS containing, among others, the columns ACCOUNT_ID (the primary key of the table) and BALANCE (the current account balance). The method transfer shown below uses a transaction to transfer money from one bank account to another. Two SQL statements are bundled into the transaction. The method commit is called after the execution of the second statement and the method rollback is called if an exception occurs during the transaction.

public void transfer(int senderId, int receiverId, float senderAccountBalance, float receiverAccountBalance, float transferAmount) {

   
Connection connection = null;
    PreparedStatement statement =
null;

   
try{
         
// Create a database connection
         
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "yourName", "yourPassword");

         
// Disable the auto-commit mode
         
connection.setAutoCommit(false);

         
// Create a PreparedStatement
         
statement = connection.prepareStatement("UPDATE ACCOUNTS SET BALANCE = ? WHERE ACCOUNT_ID = ?");

         
// Execute the first statement (make a debit to the sender account)
         
statement.setFloat(1, senderAccountBalance - transferAmount);
          statement.setInt
(2, senderId);
          statement.executeUpdate
();

         
// Execute the second statement (make a corresponding credit to the receiver account)
         
statement.setFloat(1, receiverAccountBalance + transferAmount);
          statement.setInt
(2, receiverId);
          statement.executeUpdate
();

         
// Call the method commit
         
connection.commit();

         
// Enable the auto-commit mode (back to default)
         
connection.setAutoCommit(true);
   
}
   
catch(SQLException e){
         
// Call the method rollback if an exception occurs during the transaction
         
if(connection != null){
               
try{
                     
connection.rollback();
               
}
               
catch(SQLException ex){
                     
ex.printStackTrace();
               
}
          }
    }
   
finally{
         
try{
               
if(statement != null)
                     
statement.close();
               
if(connection != null)
                     
connection.close();
         
}
         
catch(SQLException ex){
               
ex.printStackTrace();
         
}
    }

}


Savepoints

The method rollback undoes all the changes potentially made in the database during a transaction. However, under certain circumstances, you may want to undo only a part of the changes made in the database. To achieve that, just call the method setSavepoint to create a Savepoint object somewhere within the transaction as follows:

Savepoint savepoint = connection.setSavepoint();

Then, to undo the changes made after the Savepoint object you defined earlier, call the method rollback(Savepoint savepoint) like this:

connection.rollback(savepoint);

Note that you can release a Savepoint object and all subsequent Savepoint objects by calling the method releaseSavepoint(Savepoint savepoint).


You are here :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.7. Transactions
Next tutorial :  JavaPerspective.com  >   Advanced Tutorials  >   2. JDBC - database access  >   2.8. Metadata

Copyright © 2013. JavaPerspective.com. All rights reserved.  ( Terms | Contact | About ) 
Java is a trademark of Oracle Corporation
Image 1 Image 2 Image 3 Image 4 Image 5 Image 6 Image 7