JavaServer Pages, Second Edition

Using Transactions

There’s one important database feature we have not discussed yet. In the examples in this chapter, only one SQL statement is needed to complete all database modifications for each request. This statement either succeeds or fails. However, sometimes you need to execute two or more SQL statements in sequence to update the database. A typical example is transferring money between two accounts; one statement removes some amount from the first account, and another statement adds the same amount to the second account. If the first statement is successful, but the second one fails, you have performed a disappearance act your customers aren’t likely to applaud.

The solution to this problem is to group all related SQL statements into what is called a transaction. A transaction is an atomic operation, so if one statement fails, they all fail; otherwise, they all succeed. This is referred to as committing (if it succeeds) or rolling back (if it fails) the transaction. If there’s a problem in the middle of a money transfer, for instance, the database makes sure the money is returned to the first account by rolling back the transaction. If no problems are encountered, the transaction is committed, permanently storing the changes in the database.

There’s a JSTL database action to handle transactions, described in Table 11-9.

Table 11-9. Attributes for JSTL <sql:transaction>

Attribute name	Java type	Dynamic value accepted	Description
dataSource	javax.sql.DataSource ...