JSP Database Connection JSP Standard Tag Library (JSTL)

<sql:transaction> Tag

The <sql:transaction> tag is used to encapsulate <sql:query> tags and <sql:update> tags within a transaction. A large number of <sql:query> and <sql:update> operations can be loaded into <sql:transaction>, making them a single transaction.

It ensures that modifications to the database are either committed or rolled back.

Syntax Format

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

Attribute

The <sql:transaction> tag has the following attributes:

Attribute	Description	Is Necessary	Default Value
dataSource	The database used (overriding the default value)	No	Default Database
isolation	Transaction isolation level (READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE)	No	Database Default

Example Demonstration

Firstly, you need to create an Employees table in the TEST database, and then add several records to the table. The specific operation steps are as follows:

　　 Step1:

Open CMD, change the directory to the installation directory:

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

　　 Step2:

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

Step3:

Create the Students table in the TEST database:

mysql> use TEST;
mysql> create table Students
　　　　(
　　　　　id int not null,
　　　　　first varchar (255),
　　　　　last varchar (255),
　　　　　dob date
　　　　);
Query OK, 0 rows affected (0.08　sec)
mysql>

　　 Create data records:

Finally, create several records in the Students table:

mysql> INSERT INTO Students　
　　　　　　　　　　VALUES (100, 'Zara', 'Ali', '2002/05/16);
Query OK,　1　row affected (0.05　sec)
　
mysql> INSERT INTO Students　
　　　　　　　　　　VALUES (101, 'Mahnaz', 'Fatma', '1978/11/28);
Query OK,　1　row affected (0.00 sec)
　
mysql> INSERT INTO Students　
　　　　　　　　　　VALUES (102, 'Zaid', 'Khan', '1980/10/10);
Query OK,　1　row affected (0.00 sec)
　
mysql> INSERT INTO Students　
　　　　　　　　　　VALUES (103, 'Sumit', 'Mittal', '1971/05/08);
Query OK,　1　row affected (0.00 sec)
　
mysql>

Now write a JSP file to execute SQL UPDATE statements using <sql:update> and <sql:transaction>. The code inside <sql:transaction> is either all executed or none at all:

<%@ page language="java" contentType="text/html; charset=UTF-8"
　　　　pageEncoding="UTF-8"%>
<%@ 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>JSTL sql:transaction Tag</title>
</head>
<body>
　
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver">
　　　　　url="jdbc:mysql://localhost/TEST"
　　　　　user="root"　　password="cohondob"/>
<%
Date DoB = new Date("2001/12/16");
int studentId =　100;
%>
<sql:transaction dataSource="${snapshot}">
　　　<sql:update var="count">
　　　　　　UPDATE Students SET last = 'Ali' WHERE Id =　102
　　　</sql:update>
　　　<sql:update var="count">
　　　　　　UPDATE Students SET last = 'Shah' WHERE Id =　103
　　　</sql:update>
　　　<sql:update var="count">
　　　　　INSERT INTO Students　
　　　　　VALUES (104,'Nuha', 'Ali',2010/05/26);
　　　</sql:update>
</sql:transaction>
<sql:query dataSource="${snapshot}" var="result">
　　　SELECT　*　from Students;
</sql:query>
　
<table border="1" width="100%">
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>DoB</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.first}"/></td>
<td><c:out value="${row.last}"/></td>
<td><c:out value="${row.dob}"/></td>
</tr>
</c:forEach>
</table>
　
</body>
</html>

The running result is as follows:

JSP Standard Tag Library