Transactions

You can group a set of statements to form a transaction. The transaction can be committed when all has gone well. Or, if an error has occurred in one of them, it can be rolled back as if none of the commands had been issued.

The major reason for grouping commands into transactions is database integrity. For example, suppose we want to transfer money from one bank account to another. Then, it is important that we simultaneously debit one account and credit another. If the system fails before crediting the other account, the debit needs to be undone.

If you group update statements to a transaction, then the transaction either succeeds in its entirety and it can be committed, or it fails somewhere in the middle. In that case, you can carry out a rollback and the database automatically undoes the effect of all updates that occurred since the last committed transaction.

By default, a database connection is in autocommit mode, and each SQL command is committed to the database as soon as it is executed. Once a command is committed, you cannot roll it back.

To check the current autocommit mode setting, call the getAutoCommit method of the Connection class.

You turn off autocommit mode with the command

 conn.setAutoCommit(false); 

Now you create a statement object in the normal way:

 Statement stat = conn.createStatement(); 

Call executeUpdate any number of times:

 stat.executeUpdate(command1); stat.executeUpdate(command2); stat.executeUpdate(command3); . . . 

When all commands have been executed, call the commit method:

 conn.commit(); 

However, if an error occurred, call

 conn.rollback(); 

Then, all commands until the last commit are automatically reversed. You typically issue a rollback when your transaction was interrupted by a SQLException.

Save Points

You can gain finer-grained control over the rollback process by using save points. Creating a save point marks a point to which you can later return without having to return to the start of the transaction. For example,

 Statement stat = conn.createStatement(); // start transaction; rollback() goes here stat.executeUpdate(command1); Savepoint svpt = conn.setSavepoint(); // set savepoint; rollback(svpt) goes here stat.executeUpdate(command2); if (. . .) conn.rollback(svpt); // undo effect of command2 . . . conn.commit(); 

Here, we used an anonymous save point. You can also give the save point a name, such as

 Savepoint svpt = conn.setSavepoint("stage1"); 

When you are done with a save point, you should release it:

 stat.releaseSavepoint(svpt); 

Batch Updates

Suppose a program needs to execute many INSERT statements to populate a database table. In JDBC 2, you can improve the performance of the program by using a batch update. In a batch update, a sequence of commands is collected and submitted as a batch.

NOTE

Use the supportsBatchUpdates method of the DatabaseMetaData class to find out if your database supports this feature.

The commands in a batch can be actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE and DROP TABLE. However, you cannot add SELECT commands to a batch since executing a SELECT statement returns a result set.

To execute a batch, you first create a Statement object in the usual way:

 Statement stat = conn.createStatement(); 

Now, instead of calling executeUpdate, you call the addBatch method:

 String command = "CREATE TABLE . . ." stat.addBatch(command); while (. . .) {    command = "INSERT INTO . . . VALUES (" + . . . + ")";    stat.addBatch(command); } 

Finally, you submit the entire batch:

 int[] counts = stat.executeBatch(); 

The call to executeBatch returns an array of the row counts for all submitted commands. (Recall that an individual call to executeUpdate returns an integer, namely, the count of the rows that are affected by the command.) In our example, the executeBatch method returns an array with first element equal to 0 (because the CREATE TABLE command yields a row count of 0) and all other elements equal to 1 (because each INSERT command affects one row).

For proper error handling in batch mode, you want to treat the batch execution as a single transaction. If a batch fails in the middle, you want to roll back to the state before the beginning of the batch.

First, turn autocommit mode off, then collect the batch, execute it, commit it, and finally restore the original autocommit mode:

 boolean autoCommit = conn.getAutoCommit(); conn.setAutoCommit(false); Statement stat = conn.getStatement(); . . . // keep calling stat.addBatch(. . .); . . . stat.executeBatch(); conn.commit(); conn.setAutoCommit(autoCommit  ); 

NOTE

You can only issue update statements in a batch. If you issue a SELECT query, an exception is thrown.

 java.sql.Connection 1.1 

• void setAutoCommit(boolean b)

  sets the autocommit mode of this connection to b. If autocommit is true, all statements are committed as soon as their execution is completed.

• boolean getAutoCommit()

  gets the autocommit mode of this connection.

• void commit()

  commits all statements that were issued since the last commit.

• void rollback()

  undoes the effect of all statements that were issued since the last commit.

• Savepoint setSavepoint() 1.4

  sets an unnamed save point.

• Savepoint setSavepoint(String name) 1.4

  sets a named save point.

• void rollback(Savepoint svpt) 1.4

  rolls back until the given save point.

• void releaseSavepoint(Savepoint svpt) 1.4

  releases the given save point.

 java.sql.Savepoint 1.4 

• int getSavepointId()

  gets the ID of this unnamed save point, or throws a SQLException if this is a named save point.

• String getSavepointName()

  gets the name of this save point, or throws a SQLException if this is an unnamed save point.

 java.sql.Statement 1.1 

• void addBatch(String command) 1.2

  adds the command to the current batch of commands for this statement.

• int[] executeBatch() 1.2

  executes all commands in the current batch. Returns an array of row counts, containing an element for each command in the batch that denotes the number of rows affected by that command.

 java.sql.DatabaseMetaData 1.1 

• boolean supportsBatchUpdates() 1.2

  returns true if the driver supports batch updates.