JSTL SQL Tag sql:update Example

Details
Written by  
Last Updated on 31 August 2019   |   Print  Email


This post guides you how to use the <sql:update>  tag in the JSTL SQL tags library with code example.

In JSTL, 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, UPDATEand DELETEstatements.

 

JSTL <sql:update> Syntax:

<sql:update var="<string>" scope="<string>" sql="<string>" dataSource="<string>"/>

 

Attributes:

Name

Required

Description

var

False

Name of the variable which stores the count of the rows affected after the query execution.

scope

False

Scope for var to store.

sql

False

SQL query to execute

dataSource

False

Associated data source of the database.

 

JSTL <sql:update> Example:

The following example code illustrates the usage of <sql:update> tag by updating the address and telephone information of a specified user. User enters ssn value of the table record wish to update, along with new address and telephone information.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>  
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
    
<!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>&lt;sql:update&gt; Demo</title>
  </head>
  <body>
    <h1>&lt;sql:update&gt; Demo</h1>
    <form name="updateCitizenForm"
        action="${pageContext.request.contextPath}/tag-types/sql/update.jsp"
        method="POST">
        <table border="0">
            <tr>
                <td>* Enter SSN:</td>
                <td><input type="text" name="ssn" />
            </tr>            
            <tr>
                <td>* Enter New Address:</td>
                <td><textarea rows="10" cols="5" name="address"></textarea>
            </tr>
            <tr>
                <td>* Enter New Tel:</td>
                <td><input type="text" name="tel" />
            </tr>
            <tr>
                <td><input type="submit" value="Submit"/></td>
            </tr>
        </table>
        
    </form>
    <br/><br/>
    <sql:setDataSource var="myDS" driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/test" user="root" password="" />
        
    <c:choose>
    <c:when test="${not empty param.ssn 
               && not empty param.address 
               && not empty param.tel}">        
        <sql:update dataSource="${myDS}" var="newCitizen">
            UPDATE citizens SET address=?, telephone=? where ssn=?
            <sql:param value="${param.address}" />
            <sql:param value="${param.tel}" />
            <sql:param value="${param.ssn}" />
        </sql:update>
    </c:when>
    <c:otherwise>
        <font color="#cc0000">Please enter mandatory information.</font>
    </c:otherwise>
    </c:choose>
    
    <br/><br/>
    <sql:query dataSource="${myDS}" var="citizens">
        SELECT * from citizens;
    </sql:query>
    <table border="1">
        <c:forEach var="row" items="${citizens.rows}">
            <tr>
                <td><c:out value="${row.ssn}" /></td>
                <td><c:out value="${row.first_name}" /></td>
                <td><c:out value="${row.last_name}" /></td>
                <td><c:out value="${row.address}" /></td>
                <td><c:out value="${row.telephone}" /></td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

 

Database (MySQL) Table

The following snippet is the MySQL database sample table used for the above example.

CREATE TABLE IF NOT EXISTS citizens (
  ssn VARCHAR(10),   
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  address VARCHAR(255),
  telephone VARCHAR(20)        
) engine=InnoDB DEFAULT CHARSET=utf8;

 

Output:

sql-update

 

Recommended Usage of <sql:update> tag:

The <sql:update> tag is used to execute provided INSERT, UPDATE or DELETE SQL queries in its tag body or in its sql attribute.

  

Other JSTL SQL Tags:

dateParam  |  param  |  query  |  setDataSource  |  transaction


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.



Add comment