JSTL SQL <sql:transaction> Tag

The <sql:transaction> tag is used for transaction management. It is used to group multiple <sql:update> into common transaction. If you group multiple SQL queries in a single transaction, database is hit only once.

It is used for ensuring that the database modifications are performed by the nested actions which can be either rolled back or committed.

Example:

<%
Date DoB = new Date("2000/10/16");
int studentId = 151;
%>
<sql:transaction dataSource="${db}">
   <sql:update var="count">
      UPDATE Student SET First_Name = 'Suraj' WHERE Id = 150
   </sql:update>
   <sql:update var="count">
      UPDATE Student SET Last_Name= 'Saifi' WHERE Id = 153
   </sql:update>
   <sql:update var="count">
     INSERT INTO Student 
     VALUES (154,'Supriya', 'Jaiswal', '1995/10/6');
   </sql:update>
</sql:transaction>

JSTL SQL <sql:transaction> Complete Example

Consider the below information about your MySQL database setup:

We are using the JDBC MySQL driver
We are using the test database on local machine
We are using the "root" as username and "1234" as password to access the test database.

To understand the basic concept, let us create a simple table Student in the test database and creates the few records in that table using command prompts as follows:

Step-1: Open the command prompt and change to the installation directory as follows:

C:\Users\javatpoint>
C:\Users\javatpoint>cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>
C:\Users\javatpoint>
C:\Users\javatpoint>cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>

Step-2: Login to the database using command prompt as shown below:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p
Enter password: ****
mysql>

It will look like this:

Step-3: Create the table Students in test database as shown below:/p>

mysql> use test;
mysql> create table Student
    (
     id int not null,
     First_Name varchar (255),
     Last_Name varchar (255),
     dob date
    );
Query OK, 0 rows affected (0.08 sec)
mysql>

It will look like this:

Step 4: In final step you need to create few data records in Students table as shown below:

mysql> INSERT INTO student VALUES (150, 'Nakul', 'Jain', '1994/07/19' );
Query OK, 1 row affected (0.05 sec)
 
mysql>  INSERT INTO Student VALUES (151, 'Simran', 'Kumar', '1992/03/9');
Query OK, 1 row affected (0.00 sec)
 
mysql>  INSERT INTO Student VALUES (152, 'Ajeet', 'Singhal', '1992/01/23' );
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Student VALUES (153, 'Hamza', 'Hussain', '1992/01/9');
Query OK, 1 row affected (0.00 sec)
 
mysql>

It will look like this:

Let's see the simple JSP example to understand the use of <sql:transaction> tag:

<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ page import="java.util.Date,java.text.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
 
<html>
<head>
<title>sql:transaction Tag</title>
</head>
<body>
 
<sql:setDataSource var="db" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost/test"
     user="root"  password="1234"/>

<%
Date DoB = new Date("2000/10/16");
int studentId = 151;
%>
 
<sql:transaction dataSource="${db}">
   <sql:update var="count">
      UPDATE Student SET First_Name = 'Suraj' WHERE Id = 150
   </sql:update>
   <sql:update var="count">
      UPDATE Student SET Last_Name= 'Saifi' WHERE Id = 153
   </sql:update>
   <sql:update var="count">
     INSERT INTO Student 
     VALUES (154,'Supriya', 'Jaiswal', '1995/10/6');
   </sql:update>
</sql:transaction>
 
<sql:query dataSource="${db}" var="rs">
   SELECT * from Student;
</sql:query>
 
<table border="2" width="100%">
<tr>
   <th>Emp ID</th>
   <th>First Name</th>
   <th>Last Name</th>
   <th>DoB</th>
</tr>
<c:forEach var="table" items="${rs.rows}">
<tr>
   <td><c:out value="${table.id}"/></td>
   <td><c:out value="${table.First_Name}"/></td>
   <td><c:out value="${table.Last_Name}"/></td>
   <td><c:out value="${table.dob}"/></td>
</tr>
</c:forEach>
</table>
 
</body>
</html>

Output: