JSTL SQL sql:update Tag

The <sql: update> tag executes SQL statement specified in the sql attribute or in the tag body, for example SQL INSERT, UPDATE or DELETE statements.

Attributes of <sql: setupdate>Tag

  • sql: It specifies SQL command that has to be executed.
  • var: It is used to specify variable name used to store result of query.
  • scope: It specifies scope of the variable like page or session or request or application.
  • datasource: It specify data source name or java.sql.DataSource object and also specifies which data source needed to execute given query.

Example of <sql:update> tag

<%@ 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>
<!-- Connect to the database -->
	<sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/test" user="root" password="" />

	<!--  Updating the table by inserting new value to the table -->
	<sql:update dataSource="${ds}" var="count">
   INSERT INTO Employees VALUES (104, 27, 'raju');
</sql:update>

        <!--  Execute the SQL -->
	<sql:query dataSource="${ds}" var="result">
  		 SELECT * from Employees;
        </sql:query>

	<!--  Creating the table -->
	    <table border="1" width="250">
		<tr>
			<th>Emp ID</th>
			<th>Age</th>
			<th>Name</th>
		</tr>
                <!--  Displaying values in the table format -->
		<c:forEach var="row" items="${result.rows}">
			<tr>
				<td><c:out value="${row.id}" /></td>
				<td><c:out value="${row.age}" /></td>
				<td><c:out value="${row.name}" /></td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

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:query dataSource=”${ds}” var=”result”> select * from employees; tag is used to specify SQL statement to be run on the database and stores result of SQL statement in the variable.
  • <sql:update dataSource=”${ds}” var=”count”> INSERT INTO Employees VALUES (104, 27, ‘raju’);</sql:update> tag is used to update table by inserting new values in the database table “employees”.

Steps for Execution

  • Save the file as UpdateExample.jsp in eclipse IDE.
  • Now select the jsp file, right click on mouse and select Run as -> Run on Server.

Output

After successful execution of the program we will get the following result:
sql_update

 

Previous Tutorial : JSTL SQL sql:query Tag :: Next Tutorial : JSTL SQL sql:param Tag

Comments

comments

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.

Speak Your Mind

*