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:
- The first statement makes a debit to the sender account.
- The second statement makes a corresponding credit to the receiver account.
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();
}
}
}
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
Next tutorial : JavaPerspective.com > Advanced Tutorials > 2. JDBC - database access > 2.8. Metadata