Using Local Transactions

A transaction is a group of SQL statements that represents one unit of work. A transaction executes all of its statements or none at all. JDBC handles both local and distributed transactions. Today we're covering only local transactions; distributed transactions are deferred to Day 16, when you'll study the concepts of the JTA (Java Transaction API).

A local transaction belongs only to the current process, and deals with only a single resource manager that handles a DataSource. An RDBMS is an example of a resource manager., A distributed transaction, on the other hand, manages multiple DataSources across multiple processes. All database operations mentioned earlier today that use a Statement object are implicitly transactional with auto-commit. That means the DBMS commits the transaction as soon as any execute(), executeUpdate(), or executeBatch() is done.

Local transactions are managed by the Connection object, and not by the EJB container. To change this implicit behavior, JDBC provides the method setAutoCommit(false) to set the transaction mode on the Connection object. The commit() and rollback() methods also are used by the Connection object to control the transaction's behavior. An example of a local transaction is as follows:

// Create a Statement from Connection and its set transaction mode
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
try {
  stmt.executeUpdate("UPDATE DAY09_STUDENT set first_name='Laura' where student_id ='5'");
  // assume something wrong happen here.....
  stmt.executeUpdate("UPDATE DAY09_STUDENT set last_name='Smith' where student_id ='5'");
  conn.commit();
} catch (SQLException ex) {
  conn.rollback();
  stmt.close();
}

Disabling auto-commit using setAutoCommit(false) is required when a batch of SQL statements must execute together, or the EJB container is managing the transaction. Controlling transactions can increase performance because you commit a batch of SQL statements instead doing so one at a time. JDBC architects realized this fact and have built the concept of batch updates into the JDBC API. Batch updates will be covered in the next section.

Another concept related to local transactions is handling concurrency by setting the transaction isolation level, which will be covered during your study of transactions on Day 16.