JSTL - SQL <sql:dateParam> Tag



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

Attribute

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

Attribute Description Required Default
Value Value of the date parameter to set (java.util.Date) No Body
type DATE (date only), TIME (time only), or TIMESTAMP (date and time) No TIMESTAMP

Example

To start with basic concept, let us create a simple table Students table in the TEST database and create a 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 Students
   (
      id int not null,
      first varchar (255),
      last varchar (255),
      dob date
   );
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 Students 
   VALUES (100, 'Zara', 'Ali', '2002/05/16');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO Students 
   VALUES (101, 'Mahnaz', 'Fatma', '1978/11/28');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Students 
   VALUES (102, 'Zaid', 'Khan', '1980/10/10');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO Students 
   VALUES (103, 'Sumit', 'Mittal', '1971/05/08');
Query OK, 1 row affected (0.00 sec)
 
mysql>

Let us now write a JSP which will make use of the <sql:update> tag along with <sql:param> tag and the <sql:dataParam> tag to execute an SQL UPDATE statement to update the date of birth for Zara −

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ page import = "java.util.Date,java.text.*" %>
<%@ 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:dataParam Tag</title>
   </head>

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

      <%
         Date DoB = new Date("2001/12/16");
         int studentId = 100;
      %>
 
      <sql:update dataSource = "${snapshot}" var = "count">
         UPDATE Students SET dob = ? WHERE Id = ?
         <sql:dateParam value = "<%=DoB%>" type = "DATE" />
         <sql:param value = "<%=studentId%>" />
      </sql:update>
 
      <sql:query dataSource = "${snapshot}" var = "result">
         SELECT * from Students;
      </sql:query>
 
      <table border = "1" width="100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>DoB</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.dob}"/></td>
            </tr>
         </c:forEach>
      </table>
 
   </body>
</html>

Access the above JSP, the following result will be displayed. The dob from 2002/05/16 to 2001/12/16 for the record with ID = 100 −

+-------------+----------------+-----------------+-----------------+
|    Emp ID   |    First Name  |     Last Name   |       DoB       |
+-------------+----------------+-----------------+-----------------+
|     100     |    Zara        |     Ali         |   2001-12-16    |
|     101     |    Mahnaz      |     Fatma       |   1978-11-28    |
|     102     |    Zaid        |     Khan        |   1980-10-10    |
|     103     |    Sumit       |     Mittal      |   1971-05-08    |
+-------------+----------------+-----------------+-----------------+
jsp_standard_tag_library.htm
Advertisements