JavaBeat

  • Home
  • Java
    • Java 7
    • Java 8
    • Java EE
    • Servlets
  • Spring Framework
    • Spring Tutorials
    • Spring 4 Tutorials
    • Spring Boot
  • JSF Tutorials
  • Most Popular
    • Binary Search Tree Traversal
    • Spring Batch Tutorial
    • AngularJS + Spring MVC
    • Spring Data JPA Tutorial
    • Packaging and Deploying Node.js
  • About Us
    • Join Us (JBC)
  • Privacy
  • Contact Us

JSTL SQL sql:transaction Tag

February 27, 2014 by Krishna Srinivasan Leave a Comment

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

[code lang=”html”]
<%@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>
[/code]

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:
transaction_before
After successful execution of the program we will get the following result:
transaction_after

Previous Tutorial :  JSTL SQL sql:dateParam Tag :: Next Tutorial : JSTL SQL sql:setDataSource Tag

Filed Under: Java EE Tagged With: JSTL Tutorials

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us

  • Facebook
  • Pinterest

As a participant in the Amazon Services LLC Associates Program, this site may earn from qualifying purchases. We may also earn commissions on purchases from other retail websites.

JavaBeat

FEATURED TUTORIALS

Answered: Using Java to Convert Int to String

What is new in Java 6.0 Collections API?

The Java 6.0 Compiler API

Copyright © by JavaBeat · All rights reserved