Use of tag of jstl

Here in this section we are going to create application that how to group queries and update operations by using tag of jstl.

Here in this section we are going to create application that how to group queries and update operations by using tag of jstl.

Use of <sql:transaction> tag of jstl

Here in this section we are going to create application that how to group queries and update operations by using <sql:transaction> tag of jstl. To execute query and update database first create a data source.

<sql:setDataSource> : This tag is used to create data source for specified driver, user name and password of database, url etc.

Attributes of the tag <sql:setDataSource>

dataSource	Optional attribute for explicitly specifying their connection factory, it may be a instance `javax.sql.DataSource` interface.
driver	driver class name that is use to create connection.
url	url associated with given database.
user	It specifies user name of the database.
password	It specifies password of the database.
var	var is a variable that is used to store created data source.
Scope	Define the scope for declared variable like page or request or session or application.

After creating connection to database, now we are explaining how to use <sql:transaction> tag.

<sql:transaction> : This tag provides facility to execute group query and update operations on database, operations within a transaction can either all succeed or all fail.

Attributes of the tag <sql:transaction>

dataSource	It specifies that for which data source you want to execute given query.
isolation	It represents transaction isolation level.

Before run this code first create a database named 'mahendra' and table named 'employee_master' in same database by the sql query given below:

Query to create table:

CREATE TABLE `employee_master` (             
         `emp_id` int(10) NOT NULL auto_increment,  
         `firstName` varchar(20) default NULL,      
         `lastname` varchar(20) default NULL,       
         `salary` double default NULL,              
         `tonus` double default NULL,               
          PRIMARY KEY  (`emp_id`)                    
        )

Structure of the table:

insert_data_SqlJstlTag.jsp

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql"%>
<html>
<HEAD>
<TITLE>Using a Transaction with a JSP</TITLE>
</HEAD>
<body bgcolor="white">
<h2>View table Data</h2>
<form method="post">
<table>
<tr>
<td>Enter First Name</td>
<td><input type="text" name="fname"></td>
</tr>
<tr>
<td>Enter Last Name</td>
<td><input type="text" name="lname"></td>
</tr>
<tr>
<td>Enter Salary</td>
<td><input type="text" name="salary"></td>
</tr>
<tr>
<td>Enter Total Bonus</td>
<td><input type="text" name="bonus"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="submit"></td>
</tr>
</table>
</form>
<c:if test="${pageContext.request.method=='POST'}">
<c:catch var="exception">
<sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mahendra" user="root"
password="root" />
<sql:transaction dataSource="${dataSource}">
<sql:update>
INSERT INTO employee_master (firstname,lastname,salary,tonus) 
VALUES (?, ?, ?, ?)
<sql:param value="${param.fname}" />
<sql:param value="${param.lname}" />
<sql:param value="${param.salary}" />
<sql:param value="${param.bonus}" />
</sql:update>
<sql:query var="resultObj">
select * from employee_master
</sql:query>
</sql:transaction>
<table border="1">
<c:forEach items="${resultObj.rows}" var="row">
<tr>
<c:forEach items="${row}" var="column">
<td><c:out value="${column.value}" /></td>
</c:forEach>
</tr>
</c:forEach>
</table>
</c:catch>
<c:if test="${exception!=null}">
<b>Exception : </b>
<c:out value="${exception}" />
</c:if>
</c:if>
</body>
</html>

Steps to run this example :

1: Download the zip file of code and unzip this file, you will get a folder named 'transaction_SqlJstlTag'.
2: Paste this folder in 'Apache Tomcat 6.0.16-->webapps' or generally in directory 'C:\apache-tomcat-6.0.16\webapps'.
3: Start tomcat server by click on startup.bat file in 'C:\apache-tomcat-6.0.16\bin'.
4: Open browser and type url 'http://localhost:8080/transaction_SqlJstlTag/transaction_SqlJstlTag.jsp' or click on this link.

Output of the program:

When user enter all information in the text boxes and click on submit button, response will be.....

Download Source Code

Ads