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