JDBC Transactions

If the JDBC connection is in automatic commit mode (the default mode), every SQL statement is submitted to the database upon completion. This may be good for simple applications, but for the following three reasons, you may want to turn off automatic commit and manage your own transactions-

• To improve performance.

• Maintain the integrity of the business process.

• Use distributed transactions.

Transactions allow you to control whether and when changes are applied to the database. It treats a single SQL statement or a group of SQL statements as a logical unit, and if any statement fails, the entire transaction will fail.

To enable manual transaction support instead of the JDBC driver's default usageAutomatic commitmode, please use the Connection object'ssetAutoCommit()method. If you pass the boolean value false to setAutoCommit(), the automatic commit feature will be turned off. You can pass the boolean value true to reopen it.

For example, if you have a Connection object named conn, write the following code to disable automatic commit-

conn.setAutoCommit(false);

Commit and rollback

After completing changes and wanting to commit the changes, pleaseCall the method on the connection objectcommit(), as shown below:

conn.commit(　);

Otherwise, to rollback database updates made using a Connection named conn, use the following code-

conn.rollback(　);

The following example demonstrates the usage of commit and rollback objects-

try{
　　　//Assuming a valid connection object conn
　　　conn.setAutoCommit(false);
　　　Statement stmt = conn.createStatement();
　　　
　　　String SQL = "INSERT INTO Employees"　+
　　　　　　　　　　　　　　　　"VALUES (106,　20, 'Rita', 'Tez')";
　　　stmt.executeUpdate(SQL);　　
　　　//Submit a malformed SQL statement that will cause the process to break
　　　String SQL = "INSERTED IN Employees"　+
　　　　　　　　　　　　　　　　"VALUES (107,　22, 'Sita', 'Singh')";
　　　stmt.executeUpdate(SQL);
　　　//　If there are no errors.
　　　conn.commit();
}
　　　//　If there are any errors.
　　　conn.rollback();
}

In this case, the above INSERT statements will not succeed and all changes will be rolled back.

To better understand, let's study "commit example code.

using Savepoint

new JDBC 3.0 Savepoint interface provides other transaction controls. Most modern DBMS support savepoints in their environments, such as Oracle's PL / SQL.

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

The Connection object has two new methods to help you manage savepoints-

• setSavepoint(String savepointName):Define a new savepoint. It also returns a Savepoint object.

• releaseSavepoint(Savepoint savepointName):Delete the savepoint. Please note that it requires a Savepoint object as a parameter. This object is usually generated by the setSavepoint() method.

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

The following example illustrates the usage of the Savepoint object-

try{
　　　//Assuming a valid connection object conn
　　　conn.setAutoCommit(false);
　　　Statement stmt = conn.createStatement();
　　　
　　　//Set a savepoint
　　　Savepoint savepoint1　= conn.setSavepoint("Savepoint1);
　　　String SQL = "INSERT INTO Employees"　+
　　　　　　　　　　　　　　　　"VALUES (106,　20, 'Rita', 'Tez')";
　　　stmt.executeUpdate(SQL);　　
　　　//Submit a malformed SQL statement that will cause the process to break
　　　String SQL = "INSERTED IN Employees"　+
　　　　　　　　　　　　　　　　"VALUES (107,　22, 'Sita', 'Tez')";
　　　stmt.executeUpdate(SQL);
　　　//　If there are no errors, please commit the changes.
　　　conn.commit();
}
　　　//　If there are any errors.
　　　conn.rollback(savepoint1);
}

In this case, the above INSERT statements will not succeed and all changes will be rolled back.

To better understand, let's studySavepoints-Example Code.