Categories: JSTL

JSTL SQL Transaction<sql:transaction> Tag Example

The <sql:transaction> tag is used to group <sql:query> and <sql:update> into transactions. You can put as many <sql:query> and <sql:update> as statements inside <sql:transaction> to make them a single transaction.

It ensures that the database modifications performed by the nested actions are either committed or rolled back if an exception is thrown by any nested action.

JSTL Transaction Tag provides the capability to run SQL statement in a group. You can run multiple SQL statements at a time. It is called one single transaction. In this transaction either all statments run successfully or all fail if one statement does not run successfully.

Syntax
<sql:transaction dataSource=”<string>” isolation=”<string>”/>

JSTL Transaction Tag has following attribute.

1. dataSource Attribute: Specifies the datasource for the transaction.

2. isolation Attribute: Specifies the level of isolation for the transaction.

JSTL SQL Transaction Tag Example:-

<%@ 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("2013/10/23");
int studentId = 1111;
%>

<sql:transaction dataSource="${snapshot}">
   <sql:update var="count">
      UPDATE Students SET firstName = 'Sandhya' WHERE Id = 2222
   </sql:update>
   <sql:update var="count">
      UPDATE Students SET fistName = 'Sweetu' WHERE Id = 3333
   </sql:update>
   <sql:update var="count">
     INSERT INTO Students 
     VALUES (4444,'Neha', 'Verma', '2013/10/23');
   </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>

Now try to access above JSP, which should display the following result:

Emp ID First Name Last Name DOB
1111 Dinesh Rajput 2013/10/23
2222 Anu Rajput 2013/10/23
2222 Sweetu Rajput 2013/10/23
2222 Sandhya Bansal 2013/10/23
<<Previous <<   || Index ||   >>Next >>

Previous
Next
Dinesh Rajput

Dinesh Rajput is the chief editor of a website Dineshonjava, a technical blog dedicated to the Spring and Java technologies. It has a series of articles related to Java technologies. Dinesh has been a Spring enthusiast since 2008 and is a Pivotal Certified Spring Professional, an author of a book Spring 5 Design Pattern, and a blogger. He has more than 10 years of experience with different aspects of Spring and Java design and development. His core expertise lies in the latest version of Spring Framework, Spring Boot, Spring Security, creating REST APIs, Microservice Architecture, Reactive Pattern, Spring AOP, Design Patterns, Struts, Hibernate, Web Services, Spring Batch, Cassandra, MongoDB, and Web Application Design and Architecture. He is currently working as a technology manager at a leading product and web development company. He worked as a developer and tech lead at the Bennett, Coleman & Co. Ltd and was the first developer in his previous company, Paytm. Dinesh is passionate about the latest Java technologies and loves to write technical blogs related to it. He is a very active member of the Java and Spring community on different forums. When it comes to the Spring Framework and Java, Dinesh tops the list!

Share
Published by
Dinesh Rajput

Recent Posts

Strategy Design Patterns using Lambda

Strategy Design Patterns We can easily create a strategy design pattern using lambda. To implement…

2 years ago

Decorator Pattern using Lambda

Decorator Pattern A decorator pattern allows a user to add new functionality to an existing…

2 years ago

Delegating pattern using lambda

Delegating pattern In software engineering, the delegation pattern is an object-oriented design pattern that allows…

2 years ago

Spring Vs Django- Know The Difference Between The Two

Technology has emerged a lot in the last decade, and now we have artificial intelligence;…

2 years ago

TOP 20 MongoDB INTERVIEW QUESTIONS 2022

Managing a database is becoming increasingly complex now due to the vast amount of data…

2 years ago

Scheduler @Scheduled Annotation Spring Boot

Overview In this article, we will explore Spring Scheduler how we could use it by…

3 years ago