Dinesh on Java

Categories: JSTL

JSTL <sql:update>SQL UPDATE Tag Example

JSTL Update Tag provides capability to write insert, update or delete statements directly from JSP.

The <sql:update> tag executes the provided SQL query through its body or through its sql attribute. Execution SQL queries using <sql:update> tag does not return any data. This tag can execute INSERT, UPDATE and DELETE statements.

Syntax-
<sql:update var=”<string>” scope=”<string>” sql=”<string>” dataSource=”<string>”/>

JSTL <sql:Update> Tag has following attributes.

1. datasource Attribute: Specifies the datasource. Make sure that you already created datasource using serDataSource tag before writing query.

2. sql Attribute: Specifies the SQL statement to run on database.

3. var Attribute: Store the result of SQL statement.

JSTL SQL UPDATE Tag Example:
Now let us write a JSP which will make use of <sql:update> to execute a SQL INSERT statement to create one record in the table as follows:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>

<!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 Tags - setDataSource Example</title>
</head>
<body>

 <sql:setDataSource driver="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/database_name"
            var="localSource" 
            user="database_user"  
            password="database_password"/>
<sql:update dataSource="${snapshot}" var="count">
   INSERT INTO Employees VALUES (333, 27, 'Anamika', 'Rajput');
</sql:update>
 <sql:query dataSource="${localSource}" var="result">
SELECT * from Employees;
</sql:query>
 
<table border="1" width="100%">
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.first}"/></td>
<td><c:out value="${row.last}"/></td>
<td><c:out value="${row.age}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>

Now try to access above JSP, which should display the following result:

Emp ID	First Name	Last Name	Age
1111	Dinesh	Rajput	27
2222	Sweety	Rajput	23
3333	Anamika	Rajput	23

<<Previous << || Index || >>Next >>

Dinesh Rajput

Dinesh Rajput is the chief editor of a website Dineshonjava, a technical blog dedicated to the Spring and Java technologies. It has a series of articles related to Java technologies. Dinesh has been a Spring enthusiast since 2008 and is a Pivotal Certified Spring Professional, an author of a book Spring 5 Design Pattern, and a blogger. He has more than 10 years of experience with different aspects of Spring and Java design and development. His core expertise lies in the latest version of Spring Framework, Spring Boot, Spring Security, creating REST APIs, Microservice Architecture, Reactive Pattern, Spring AOP, Design Patterns, Struts, Hibernate, Web Services, Spring Batch, Cassandra, MongoDB, and Web Application Design and Architecture. He is currently working as a technology manager at a leading product and web development company. He worked as a developer and tech lead at the Bennett, Coleman & Co. Ltd and was the first developer in his previous company, Paytm. Dinesh is passionate about the latest Java technologies and loves to write technical blogs related to it. He is a very active member of the Java and Spring community on different forums. When it comes to the Spring Framework and Java, Dinesh tops the list!