Home Tutorials Java Jsp Jstl Sql JSTL sql transaction tag

JSTL sql transaction tag

Posted in : Java Posted on : April 23, 2012 at 8:14 PM Comments : [ 0 ]

In this tutorial you will learn about the JSTL sql tag transaction.

JSTL sql transaction tag

In this tutorial you will learn about the JSTL sql tag transaction.

<sql:transaction> tag of JSTL sql tag library specifies the execution of all the statements within a transaction by allowing to group the query and update operations on database.

Attributes of <sql:transaction> :

dataSource : This is an optional attribute and is used to access the database.
isolation : This is an optional transaction isolation level attribute.

Example :

An example is being given here will demonstrate you about the JSTL <sql:transaction> tag. In this example at first I have created a table student_info1(roll_no, name, date_of_birth) and inserted some records in their respective fields. Then created a JSP page where at first created a data source to access te database using the tag <sql:setDataSource> then displayed the name and date_of_birth of the table that are entered earlier. Then designed a form where taken text boxes to take the student information from the user then checked for the values entered by the user are not null and formats the date into a specific format. Further used the <sql:transaction> tag to execute all the statements within a transaction and used the <sql:update> tag to use the SQL insert query and also used the <sql:param> tag to provide the value as a parameter and <sql:dateParam> to provide the date value as a parameter. In the next lines write the code for displaying all records of student_info1 table.

Table student_info1

CREATE TABLE `student_info1` ( 
`roll_no` int(11) NOT NULL, 
`name` varchar(15) default NULL, 
`date_of_birth` date default NULL, 
PRIMARY KEY (`roll_no`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

JstlSqlTransaction.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.Date, java.text.Format, java.text.SimpleDateFormat" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!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>JSTL sql:transaction Example</title>
</head>
<body>
<sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://192.168.10.13/data" user="root" password="root"/>
<sql:query var="rs" dataSource="${ds}">
select name, date_of_birth from student_info1
</sql:query>
<form>
List of Student Name :
<table border="1">
<c:forEach var="rowValue" items="${rs.rows}">
<tr>
<td>${rowValue.name}</td>
<td>${rowValue.date_of_birth}</td>
</tr>
</c:forEach>
</table>
<p>Enter student roll no :
<input type="text" name="roll"/></p>
<p>Enter student name :
<input type="text" name="name"/></p>
<p>Enter student date of birth 
<input type="text" name="dt"/>
<br>(yyyy-mm-dd)</p>
<input type="submit" value="submit"/>
</form>
<%
if(request.getParameter("name")!= null && request.getParameter("dt") != null)
{
String strDate = request.getParameter("dt");
SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date utilDate = formater.parse(strDate);
java.sql.Date sqlDate = new Date(utilDate.getTime());
%>
<sql:transaction dataSource="${ds}">
<sql:update var="ins">
insert into student_info1 values (?, ?, ?)
<sql:param value="${param.roll}"/>
<sql:param value="${param.name}" />
<sql:dateParam value="<%=sqlDate %>" type="DATE" />
</sql:update>
</sql:transaction>

<sql:query var="rs" dataSource="${ds}">
select * from student_info1
</sql:query>
<table border="1">
<tr>
<td><b>roll_no</b></td>
<td><b>name</b></td>
<td><b>date_of_birth</b></td>
</tr>
<c:forEach var="rowValue" items="${rs.rows}">
<tr>
<td>${rowValue.roll_no}</td>
<td>${rowValue.name}</td>
<td>${rowValue.date_of_birth}</td>
</tr>
</c:forEach>
</table>
<%
}
%>
</body>
</html>

How to run this example

Here I am using an IDE Eclipse so I am giving the process of executing this example in perspective of Eclipse. Before executing this example you will have needed to add the following jar files :

jstl.jar
standard.jar

After adding of these jar files you may execute your program in the following ways :

Select JstlSqlTransaction.jsp file of your project in Project Explorer -> RightClick -> Run As -> Run On Server -> Choose your server -> Finish.
On the Eclipse Editor go to your JstlSqlTransaction.jsp -> RightClick -> Run As -> Run On Server -> Choose your server -> Finish.
Go to Run button look at the toolbar in green color and click -> Choose your server -> Finish.
A simplest way to execute the example in Eclipse is to use the CTRL+F11 key -> Run On Server -> Choose your server -> Finish

NOTE : In all of the above execution processes you may start the server first and stop the server each time after the execution if not, each time you will may prompted to a dialog box to Restart the server in Eclipse.

Output :

1. student_info1 table