JSTL SQL Tag sql:transaction Example

Details
Written by  
Last Updated on 31 August 2019   |   Print  Email


In this post, I will help you understand how to use the <sql:transaction> tag in the JSTL SQL tags library with code example.

You know, the <sql:transaction> tag executes the SQL queries configured using <sql:query> or <sql:update> in transactional context. The benefit of executing queries in transactional context is that, in case any problem occurs during execution any changes made to the database will be rolled back. On the other hand if the query execution is successful, then the changes are committed.

 

JSTL <sql:transaction> Syntax:

<sql:transaction dataSource="<string>" isolation="<string>"/>

 

Attributes:

Name

Required

Description

dataSource

False

Associated data source configured to access the database.

isolation

False

Specifies the isolation level of the transaction. Possible values are READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE. If not specified, the isolation level specified during data source configuration will be used.

 

JSTL <sql:transaction> Example:

The following example updates the user’s address and telephone based on user’s ssn value. This update is being performed under transactional context.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>  
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
  "http://www.w3.org/TR/html4/loose.dtd">
<html> 
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>&lt;sql:transaction&gt; Demo</title>
  </head>
  <body>
    <h1>&lt;sql:transaction&gt; Demo</h1>
    <form name="updateCitizenForm"
        action="${pageContext.request.contextPath}/tag-types/sql/transaction.jsp"
        method="POST">
        <table border="0">
            <tr>
                <td>* Enter SSN:</td>
                <td><input type="text" name="ssn" />
            </tr>            
            <tr>
                <td>* Enter New Address:</td>
                <td><textarea rows="10" cols="5" name="address"></textarea>
            </tr>
            <tr>
                <td>* Enter New Tel:</td>
                <td><input type="text" name="tel" />
            </tr>
            <tr>
                <td><input type="submit" value="Submit"/></td>
            </tr>
        </table>
        
    </form>
    <br/><br/>
    <sql:setDataSource var="myDS" driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/test" user="root" password="" />
        
    <sql:transaction dataSource="${myDS}">                
    <c:choose>
    <c:when test="${not empty param.ssn 
            && not empty param.address 
            && not empty param.tel}">        
        <sql:update var="newCitizen">
            UPDATE citizens SET address=?, telephone=? where ssn=?
            <sql:param value="${param.address}" />
            <sql:param value="${param.tel}" />
            <sql:param value="${param.ssn}" />
        </sql:update>
    </c:when>
    <c:otherwise>
        <font color="#cc0000">Please enter mandatory information.</font>
    </c:otherwise>
    </c:choose>
    </sql:transaction>
    
    <br/><br/>
    <sql:query dataSource="${myDS}" var="citizens">
        SELECT * from citizens;
    </sql:query>
    <table border="1">
        <c:forEach var="row" items="${citizens.rows}">
            <tr>
                <td><c:out value="${row.ssn}" /></td>
                <td><c:out value="${row.first_name}" /></td>
                <td><c:out value="${row.last_name}" /></td>
                <td><c:out value="${row.address}" /></td>
                <td><c:out value="${row.telephone}" /></td>
            </tr>
        </c:forEach>
    </table>
   </body>
</html>

 

Database (MySQL) Table

The following snippet is the MySQL database sample table used for the above example.

CREATE TABLE IF NOT EXISTS citizens (
  ssn VARCHAR(10),   
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  address VARCHAR(255),
  telephone VARCHAR(20)        
) engine=InnoDB DEFAULT CHARSET=utf8;

 

Output:

sql-transaction

 

Recommended Usage of <sql:transaction> tag:

The main benefit of transactions is to avoid inconsistency of the data in the data base tables. Any operation which can potentially modify or delete database records needs to be performed under transactional context. Transactions ensure that successful executions will be committed and any failures will be rolled back thus maintaining data integrity.

The <sql:transaction> tag is good fit for executing INSERT, UPDATE or DELETE query actions under transactional support. Since these queries can modify or delete data in the databases, this tag provides necessary support to either commit or rollback based on the result of the query execution.

 

Other JSTL SQL Tags:

dateParam  |  param  |  query  |  setDataSource  |  update


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.



Add comment