[ Team LiB ] Previous Section Next Section

Recipe 21.11 Using Transactions with JSPs


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


Use the sql:transaction JSTL tag.


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" %>
      <TITLE>Using a Transaction with a JSP</TITLE>
<body bgcolor="white">
          <h2>View Athlete Data</h2>
    insert into athlete values(2, 'Rachel Perry','rlpbwp1996',
    <sql:query var="resultObj">
    select * from athlete


<c:forEach items="${resultObj.rows}" var="row">
  <c:forEach items="${row}" var="column">
     <td align="right">
       <b><c:out value="${column.key}" /></b>
         <c:out value="${column.value}" />


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... --%> 


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

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

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Chapter 23 on the JSTL and its sql tag library; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on Tomcat; Recipe 21.4-Recipe 21.6 on using DataSources with servlets and JSPs on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.10 on using transactions in servlets; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section