9.7 Executing Database Transactions | Core JSTL[c] Mastering the JSP Standard Tag Library

It's often desirable to execute a group of SQL updates or queries atomically; for example, if you transfer funds from bank account A to bank account B, you don't want to deposit the money in account B if the withdrawal from account A failed. Database transactions let you treat any number of SQL updates atomically ”if one of the updates fails, the entire transaction is rolled back. The transaction is committed only if all of the updates are successful.

JSTL supports database transactions with the <sql:transaction> action. That action has the following syntax: ^[15]

^[15] Items in brackets are optional. See "<sql:transaction>" on page 537 for a more complete description of <sql:transaction> syntax.

  <sql:transaction [dataSource] [isolation]>   <sql:query> and <sql:update> actions   <sql:transaction>

The <sql:transaction> action wraps enclosed <sql:update> and <sql:query> actions in a database transaction. As with <sql:query> and <sql:update>, you can specify a data source with <sql:transaction>. But because all queries and updates within a single transaction must be executed against a single data source, it is illegal to specify a data source for <sql:query> or <sql:update> actions within a <sql:transaction> action.

You can also specify a transaction isolation level with the isolation attribute; valid values for that attribute are:

read_uncommitted
read_committed
repeatable_read
serializable

Transaction isolation levels specify how database locks are set during a transaction; for example, read_uncommitted specifies that one user can read uncommitted changes from another user 's transaction. The isolation levels listed above are listed from the least to the most restrictive . Normally, you will not need to set isolation levels for your transactions, because they are set by your database or your system administrator. See JDBC API Tutorial and Reference ^[16] for more information about transaction isolation levels.

^[16] White, Fisher, Cattell, Hamilton, Hapner,. JDBC API Tutorial and Reference , Addison-Wesley, 1999.

Figure 9-11 shows a Web application that lets you transfer money from one account to another. That transfer is implemented with two database updates, one that withdraws money from one account and another that subsequently deposits that money into another account. Those two database updates are encapsulated in a database transaction, like this:

 <c:catch var='transactionException'>     <sql:transaction>       <sql:update>  ... withdraw money from one account ...  </sql:update>       <sql:update>  ... deposit that money in another account ...  </sql:update>    </sql:transaction> </c:catch>

Figure 9-11. Executing Database Transactions with <sql:transaction>.

graphics/09fig11.jpg

The database used by the Web application shown in Figure 9-11 specifies a constraint for account balances , like this:

 ALTER TABLE ACCOUNTS ADD CONSTRAINT  CONSTRAINT_1 CHECK (Balance > 0)

The preceding constraint will not allow an account balance to be less than 0. If you try to transfer money from account A to account B, and account A does not have sufficient funds, that constraint will cause the transaction to fail and the <sql:transaction> action will throw an exception. That exception will be caught by the <c:catch> action listed in preceding code fragment.

Note

Because MySQL does not fully support database constraints, the example in this section uses the Cloudscape database, which supports constraints.

The top two pictures in Figure 9-11 illustrate a successful transaction, and the bottom pictures illustrate a rolled back transaction.

The top pictures show a successful transaction and the bottom pictures show a failed transaction.

The top pictures in Figure 9-11 show a transfer of $1000 from the first customer (William Dupont) to the second customer (Anna Keeney). That transaction is successful because William originally had $1145.97. If you look at the upper-right picture, you will see that William's balance has been reduced by $1000 and Anna's balance has increased by the same amount.

The bottom pictures in Figure 9-11 show a failed transaction. Once again, a transfer of $1000 is attempted from William to Anna, but this time, William does not have that much money, so the transaction fails. The lower-right picture shows the results of that failure ”an error message states that the transaction failed.

The Web application shown in Figure 9-11 consists of two JSP pages, one that displays an HTML table and the form for transferring funds and another that executes the transaction. The former is listed in Listing 9.21.

Listing 9.21 index.jsp (Displaying HTML Table)

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html>    <head>       <title>Executing Database Transactions</title>    </head>    <body>       <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %>       <%@ taglib uri='http://java.sun.com/jstl/sql'  prefix='sql'%>       <%@ taglib uri='http://java.sun.com/jstl/fmt'  prefix='fmt'%>       <%-- Execute a query, limited to 10 rows, that joins the            Customers and Accounts tables --%>  <sql:query var='customers' maxRows='10'>   SELECT CUST_ID, NAME, BALANCE   FROM CUSTOMERS JOIN ACCOUNTS   USING (Cust_ID)   </sql:query>  <%-- Create a table with column names and row data --%>       <p><table align='center' border='1'>       <tr>          <%-- Create the table header --%>          <c:forEach var='column' items='${customers.columnNames}'>             <th><c:out value='${column}'/></th>          </c:forEach>       </tr>       <%-- For each row in the query... --%>       <c:forEach var='row' items='${customers.rowsByIndex}'>          <tr>             <%-- For the data in each row... --%>             <c:forEach var='rowData' items='${row}'                  varStatus='status'>                <c:choose>                   <%-- For the 3rd column, which represents                        account balance... --%>                   <c:when test='${status.count == 3}'>                      <%-- Format the currency according to the                           user's preferred locales --%>                      <td><fmt:  formatNumber  value='${rowData}'                                             type='currency'/></td>                   </c:when>                   <%-- For all other columns... --%>                   <c:otherwise>                      <%-- Put the data, as is, in the table --%>                      <td><c:out value='${rowData}'/></td>                   </c:otherwise>                </c:choose>             </c:forEach>          </tr>       </c:forEach>       </table>       <%-- The form... --%>       <form action='transfer_funds.jsp'>          <i>Transfer this amount:</i>          <select name='amount'>             <%-- For each of the monetary amounts... --%>             <c:forEach var='amt' varStatus='status'                      items='100,1000,10000,100000,1000000'>                <%-- Format the amount according to the user's                     preferred locales --%>                <fmt:  formatNumber  var='money' value='${amt}'                                               type='currency'/>                <%-- Create an HTML option element --%>                <option                   <%-- If the amount previously selected is the                        same as the current option, select it --%>                   <c:if test='${param.amount == amt}'>                      selected                   </c:if>                   <%-- Output the amount and the closing '>' for                        the option element --%>                   <c:out value='value=${amt}'/> >                   <%-- Output the value of the option element                        as formatted currency --%>                   <c:out value='${money}'/>                </option>             </c:forEach>          </select>          <p><i>from:</i>          <select name='fromCustomer'>             <%-- For the "from" customer... --%>             <c:forEach var='row' items='${customers.rows}'>                <%-- Create an HTML option element --%>                <option                   <%-- If the "from" customer previously selected                        is the same as the current customer,                        select it --%>                   <c:if                      test='${param.fromCustomer == row.cust_id}'>                      selected                   </c:if>                   <%-- Output the "from" customer's ID for the                        option element's value and the option's                        closing '>' --%>                   <c:out value='value=${row.cust_id}'/> >                   <%-- Output the "from" customer's name --%>                   <c:out value='${row.name}'/>                </option>             </c:forEach>          </select>          <i>to:</i>          <select name='toCustomer'>             <%-- For the "to" customer... --%>             <c:forEach var='row' items='${customers.rows}'>                <%-- Create an HTML option element --%>                <option                   <%-- If the "to" customer previously selected                        is the same as the current customer,                        select it --%>                   <c:if test='${param.toCustomer == row.cust_id}'>                      selected                   </c:if>                   <%-- Output the "to" customer's ID for the                        option element's value and the option's                        closing '>' --%>                   <c:out value='value=${row.cust_id}'/> >                   <%-- Output the "to" customer's name --%>                   <c:out value='${row.name}'/>                </option>             </c:forEach>          </select>          <p><input type='submit' value='Transfer Funds'>       </form>    </body> </html>

The JSP page in the preceding listing executes a database query that joins the Customers and Accounts tables. Those tables are depicted in Figure 9-2 on page 362. The CUST_ID , NAME , and BALANCE columns are selected in the join, and the result of that query is used to build the table that you see in Figure 9-11 on page 413.

The preceding JSP page is notable for formatting currency stored in the database. That formatting is accomplished with the <fmt:formatNumber> action, which is discussed in "Formatting and Parsing Numbers" on page 310. Also note that the HTML option elements retain their previously selected values.

The form in the preceding JSP page transmits the following request parameters: fromCustomer , toCustomer , and amount . Those parameters are used by the form's action, transfer_funds.jsp , which is listed in Listing 9.22.

Listing 9.22 transfer_funds.jsp

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html>    <head>       <title>Executing Database Transactions</title>    </head>    <body>       <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'%>       <%@ taglib uri='http://java.sun.com/jstl/sql'  prefix='sql'%>       <%@ taglib uri='http://java.sun.com/jstl/fmt'  prefix='fmt'%>       <%-- The transaction is enclosed in a <c:catch>            action. If the transaction fails, the <sql:transaction>            action will throw an exception, which <c:catch> stores            in a scoped variable named transactionException --%>  <c:catch var='transactionException'>  <%-- The transaction... --%>  <sql:transaction>  <%-- Withdraw money from the "from" customer's                  account --%>  <sql:update>  UPDATE ACCOUNTS SET BALANCE = BALANCE -?                WHERE CUST_ID = ?                 <sql:param value='${param.amount}'/>                 <sql:param value='${param.fromCustomer}'/>  </sql:update>  <%-- Deposit the money withdrawn from the "from"                  customer's account in the "to" customer's                  account --%>  <sql:update>  UPDATE ACCOUNTS SET BALANCE = BALANCE + ?                WHERE CUST_ID = ?                <sql:param value='${param.amount}'/>                <sql:param value='${param.toCustomer}'/>  </sql:update>   </sql:transaction>   </c:catch>  <%-- If the transactionException scoped variable is not            empty, the transaction failed --%>       <c:if test='${not empty transactionException}'>          <%-- Display the error message --%>          <font size='4' color='red'>             Transaction Failed! Please make sure that the account             you are withdrawing from has sufficient funds             for the withdrawl.          </font>       </c:if>       <%-- Import the JSP page with the form for transferring            funds --%>       <c:import url='index.jsp'/>    </body> </html>

The preceding JSP page performs a transaction that consists of two updates. The first update withdraws money from fromCustomer 's account, and the second deposits that money in the toCustomer 's account. If either of those updates fails, the transaction is rolled back and the <sql:transaction> action throws an exception.

If <sql:transaction> throws an exception, that exception is caught by the <c:catch> action, which stores it in a scoped variable named transactionException . If the transaction failed, the preceding JSP page displays an error message. Subsequently, the preceding JSP page imports the JSP page that contains the form for transferring funds, in case the user wants to execute another transaction.