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>
|
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>
|
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.....
