Transaction means handling set of tasks into single execution unit. It provides connection to all data base operations and executes all statements in one transaction. This tag combines both and tags into transactions. We can add and statements into to make single transaction.
Attributes of <sql:transaction> Tag
- dataSource: It specifies datasource for the connection.
- isolation:It specifies level of transaction isolation(READ_COMMITTED, READ_UNCOMMITTED, or SERIALIZABLE).
Example of <sql:transaction> Tag
<%@page import="java.util.Date"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/test" user="root" password="" />
<sql:transaction dataSource="${ds}">
<sql:update var="count">
update student set name='vikrant' where id=204
</sql:update>
<sql:update var="count">
insert into student values(206,'ramesh','1995/09/21');
</sql:update>
</sql:transaction>
<sql:query dataSource="${ds}" var="result">
select * from student;
</sql:query>
<table border="1" width="300">
<tr>
<th>ID</th>
<th>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.name}" /></td>
<td><c:out value="${row.dob}" /></td>
</tr>
</c:forEach>
</table>
</body>
</html>
Details of the Code
- <sql: setDataSource var=”ds” driver=”com.mysql.jdbc.Driver” url=”jdbc: mysql: //localhost/test” user=”root” password=”” /> tag is used to connect to the data base and specify variable name for data source and driver name, url , user name and password of the database.
- <sql:transaction dataSource=”${ds}” > <sql:update var=”count” >update student set name=’vikrant’ where id=204 </sql:update> tag is used to update database table student by setting new name to id=204.
- < sql:update var=”count”> insert into student values(206,’ramesh’,’1995/09/21′) </sql:update> tag is used to update table by inserting new row to database table student.
- <sql:transaction dataSource=”${ds}”> tag is used to run SQL statement in a group. We can run multiple SQL statements at a time by using this tag.
- <sql:query dataSource=”${ds}” var=”result”>select * from student; </sql:query> tag is used to fetch the data from the database table which takes attributes such as ,datasource attribute specifies the datasource, var attribute result of the SQL statement.
- <c:forEach var=”row” items=”${result.rows}”> tag is used to produce database table from SQL query and items attribute specifies collection of items to iterate in the loop.
Steps for Execution
- Save the file as TransactionExample.jsp in eclipse IDE.
- Now select the jsp file, right click on mouse and select Run as -> Run on Server.
Output
Before execution of the program table would be like this:
After successful execution of the program we will get the following result:
Previous Tutorial : JSTL SQL sql:dateParam Tag :: Next Tutorial : JSTL SQL sql:setDataSource Tag
JSTL SQL sql:dateParam Tag