24.8 Implementing Transaction Control
The JDBC protocol allows the application to control database transactions. A transaction is a set of statements that is executed as a logical unit. Either all changes due to the execution of statements in the transaction are committed to the database, or none of the changes made in the transaction are committed. A commit results in the changes made in the transaction being made permanent in the database. A commit thereby ends a transaction. A rollback results in undoing all changes made in the transaction—that is, restoring the database state to what it was before the transaction commenced. A savepoint defines a logical rollback point within a transaction. A rollback can be used to undo all changes made since a savepoint was set up in the transaction—that is, restoring the database state to what it was before the save-point was set up.
By default, JDBC connections are in auto-commit mode, which means that commit occurs automatically when the statement processing completes successfully. To change this behavior, the following method call can be used:
connection.setAutoCommit(false);
Once auto-commit is turned off, transactions can be controlled using the rollback() and commit() methods of the Connection interface. Each transaction can include any number of insert, update, and delete actions and must end in either a commit that would make all pending transaction changes permanent, or a rollback if the pending transaction changes should be discarded. Some databases also support save-points, which provides an ability to partially roll back a transaction, discarding only those pending changes that occurred in the transaction after the savepoint was set up. Whether a given database supports savepoints can be determined by calling the DatabaseMetaData.supportsSavepoints() method. It is important to remember that normal closing of a connection implicitly causes the transaction to commit. However, the transaction will be rolled back if the program terminates with an uncaught exception.
If an exception is intercepted, the Java runtime considers this exception to be successfully handled and therefore resumes normal execution after the catch block. It is worth reciting the mantra that the JDBC interfaces, including the Connection interface, implement AutoCloseable, which means that all statements and connections are closed implicitly following the execution of the try-with-resources statement. This implies that if the transaction was not explicitly rolled back inside the exception handler, then by default, the transaction will be committed.
Following selected methods of the java.sql.Connection interface can be used to control transactions:
void setAutoCommit(boolean autoCommit)
Enables or disables auto-commit mode of this connection. The value true enables auto-commit mode and false disables it.
Savepoint setSavepoint()
Savepoint setSavepoint(String name)
Create an unnamed or a named savepoint in the current transaction and returns the new Savepoint object that represents it, respectively.
void rollback()
void rollback(Savepoint savepoint)
Undo all changes made in the current transaction or all changes made since the named savepoint was set up in the current transaction, respectively.
void commit()
Makes all changes made since the previous commit/rollback permanent.
Example 24.8 Controlling Transactions
package dbDemo;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
public class Transactions {
public static void main(String[] args) {
final String jdbcURL = “jdbc:derby:musicDB”;
try (var connection = DriverManager.getConnection(jdbcURL)) {
// SQL statements:
final String insSql = “insert into compositions VALUES(?, ?, ?)”;
final String updSql = “update compositions set title = ? where title = ?”;
final String delSql = “delete from compositions where duration = ?”;
// Create statements:
try (var insStatement = connection.prepareStatement(insSql);
var updStatement = connection.prepareStatement(updSql);
var delStatement = connection.prepareStatement(delSql);) {
connection.setAutoCommit(false); // (1) Auto-commit disabled.
insStatement.setInt(3, 150); // (2) Insert a new row.
insStatement.setString(2, “Java Jazz”);
insStatement.setString(1, “ushm91736991”);
int insResult = insStatement.executeUpdate();
System.out.println(“INSERT: ” + insResult);
updStatement.setString(1, “Java Jive”); // (3) Update an existing row.
updStatement.setString(2, “Rage”);
int updResult = updStatement.executeUpdate();
System.out.println(“UPDATE: ” + updResult);
Savepoint savePoint = connection.setSavepoint(); // (4) Set a savepoint.
delStatement.setInt(1, 178); // (5) Delete a row.
int delResult = delStatement.executeUpdate();
System.out.println(“DELETE: ” + delResult);
connection.rollback(savePoint); // (6) Roll back to safepoint.
connection.commit(); // (7) Commits only (2) and (3).
} catch (SQLException e) {
connection.rollback(); // (8) Roll back any changes.
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Output from the program:
INSERT: 1
UPDATE: 1
DELETE: 1
Example 24.8 illustrates how transactions can be controlled with commit and rollback operations. The numbered comments below correspond to the numbered code lines in Example 24.8.
The following rows are in the compositions table at the start.
[ushm91736697, Vacation, 231]
[ushm91736698, Rage, 308]
[ushm91736699, Why Don’t, 178]
- Auto-commit is disabled. A new transaction starts.
- A new row is inserted:
[ushm91736991, Java Jazz, 150}
3. The title “Rage” of an existing row:
[ushm91736698, Rage, 308]
is updated to “Java Jive”:
[ushm91736698, Java Jive, 308]
- A savepoint is set.
- The row with the duration 178 is deleted:
[ushm91736699, Why Don’t, 178]
- A rollback is performed, rolling back the deletion of the row at (5) that was performed since the last savepoint was set up at (4).
- A commit is performed, committing the insertion at (2) and the update at (3), leaving the database in the following state and ending the transaction that started at (1):
[ushm91736697, Vacation, 231]
[ushm91736698, Java Jive, 308]
[ushm91736699, Why Don’t, 178]
[ushm91736991, Java Jazz, 150]
8. In case a SQLException is thrown in the inner try-with-resources statement, all changes are rolled back.
