JSTL - SQL <sql:param> Tag


Advertisements

The <sql:param> tag used as a nested action for the <sql:query> tag and the <sql:update> tag to supply a value for a value placeholder. If a null value is provided, the value is set to SQL NULL for the placeholder.

Attribute

The <sql:param> tag has the following attributes −

Attribute Description Required Default
Value Value of the parameter to set No Body

Example

To start with the basic concept, let us create an Employees table in the TEST database and create few records in that table as follows −

Step 1

Open a Command Prompt and change to the installation directory as follows −

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2

Login to the database as follows −

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3

Create the Employee table in the TEST database as follows −

mysql> use TEST;
mysql> create table Employees
   (
      id int not null,
      age int not null,
      first varchar (255),
      last varchar (255)
   );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

We will now create a few records in the Employee table as follows −

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
 
mysql>

Let us now write a JSP which will make use of the <sql:update> tag to execute an SQL DELETE statement to delete one record with id = 103 from the table as follows −

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>

<html>
   <head>
      <title>JSTL sql:param Tag</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root"  password = "pass123"/>

      <c:set var = "empId" value = "103"/>

      <sql:update dataSource = "${snapshot}" var = "count">
         DELETE FROM Employees WHERE Id = ?
         <sql:param value = "${empId}" />
      </sql:update>

      <sql:query dataSource = "${snapshot}" 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>

Access the above JSP, the following result will be displayed −

+-------------+----------------+-----------------+-----------------+
|    Emp ID   |    First Name  |     Last Name   |       Age       |
+-------------+----------------+-----------------+-----------------+
|     100     |    Zara        |     Ali         |       18        |
|     101     |    Mahnaz      |     Fatma       |       25        |
|     102     |    Zaid        |     Khan        |       30        |
+-------------+----------------+-----------------+-----------------+

You can try the <sql:param> tag with the SQL UPDATE and the SELECT statements as well in the same way as we have used it with the DELETE statement.

jsp_standard_tag_library.htm

Useful Video Courses


Video

JSP, Servlet, JSLT + Hibernate: A complete guide

108 Lectures 11 hours

Chaand Sheikh

Video

Full Stack Java developer - Java + JSP + Restful WS + Spring

Best Seller

517 Lectures 57 hours

Chaand Sheikh

Video

Servlets and JSP Tutorial For Beginners!

41 Lectures 4.5 hours

Karthikeya T

Video

JSP and Servlets - The Complete Course

42 Lectures 5.5 hours

TELCOMA Global

Video

JSP and Servlets Bootcamp: Web Applications for Beginners

15 Lectures 3 hours

TELCOMA Global

Video

JSP (Java Server Pages) Certification Training

44 Lectures 15 hours

Uplatz

Advertisements