JSP Database Connection JSP Standard Tag Library (JSTL)

The <sql:param> tag

The <sql:param> tag is used in conjunction with the <sql:query> tag and <sql:update> tag to provide a placeholder for a value. If it is a null value, the placeholder is set to SQL NULL.

Syntax format

<sql:param value="<string>"/>

Attribute

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

Attribute	Description	Is it necessary	Default value
value	The parameter value to be set	No	Body

Example Demonstration

Firstly, you need to create an Employees table in the TEST database, and then add several records to the table. The specific operation steps are as follows:

　　 Steps1:

Open CMD and change the directory to the installation directory:

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

　　 Steps2:

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

　　 Steps3:

Establish the Employees table in the TEST database:

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:

Finally, create several records in the Employees table:

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>

Now write a JSP file to use the <sql:update> tag to execute SQL DELETE statement to delete id=103records:

<%@ page language="java" contentType="text/html; charset=UTF-8"
　　　　pageEncoding="UTF-8"%>
<%@ 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>

The running result is as follows:

The usage of the <sql:param> tag in SQL UPDATE and SELECT statements is similar to its usage in DELETE.

JSP Standard Tag Library