Recipe 21.11 Using Transactions with JSPs

Problem

You want to run SQL statements within.a transaction in a JSP.

Solution

Use the sql:transaction JSTL tag.

Discussion

The JSTL has a sql:transaction tag that executes any nested SQL actions (such as sql:update ) in a transaction.

The sql:transaction tag uses the same java.sql.Connection methods that you would use in a transaction- related servlet (Recipe 21.10): setAutoCommit(false) , commit( ) , and rollback( ) .

Example 21-15 uses a DataSource that is configured in web.xml , so that none of the database-related information appears in the JSP. See Recipe 23.6 for how to configure a DataSource in the deployment descriptor. The INSERT and SELECT SQL statements that are nested inside the sql:transaction tag will both be rolled back if any problems arise within the transaction.

Example 21-15. A JSP executes INSERT and SELECT SQL statements in a transaction

  <%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>  <html> <HEAD>       <TITLE>Using a Transaction with a JSP</TITLE> </HEAD> <body bgcolor="white">           <h2>View Athlete Data</h2>  <sql:transaction>            <sql:update>     insert into athlete values(2, 'Rachel Perry','rlpbwp1996',     '24-Feb-1996','F')     </sql:update>          <sql:query var="resultObj">     select * from athlete     </sql:query> </sql:transaction>  <table> <c:forEach items="${resultObj.rows}" var="row">   <c:forEach items="${row}" var="column">     <tr>      <td align="right">        <b><c:out value="${column.key}" /></b>      </td>      <td>          <c:out value="${column.value}" />      </td></tr>   </c:forEach>  </c:forEach> </table> </body> </html>

After executing SQL within a transaction, the JSP displays the database table's updated values. The content of the sql:update and sql:query tags are traditional SQL statements.

Make sure to include the proper taglib directive to ue the JSTL 1.0 sql tag library:

 <%@ taglib uri= "http://java.sun.com/jstl/sql" prefix="sql" %>

The sql:transaction tag also has an isolation attribute in which you can specify an isolation level for the transaction (see Recipe 21.10). Here is an example:

 <sql:transaction isolation="TRANSACTION_READ_COMMITTED">  <%-- SQL statements and tags here... --%>  </sql:transaction>

Figure 21-8 shows the output of the sqlTrans.jsp file.

Figure 21-8. A JSP displays an updated database table

Problem

Solution

Discussion

Example 21-15. A JSP executes INSERT and SELECT SQL statements in a transaction

Figure 21-8. A JSP displays an updated database table

See Also