JSTL SQL Tag sql:param Example

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


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

The <sql:param> tag is used to set the parameters to the SQL query. We use this tag to set the placeholders and data to match the placeholder position to insert the data into the database or to update the database records. This tag is used as a nested tag inside the <sql:query> to provide the parameters to the query.

 

JSTL <sql:param> Syntax:

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

 

Attributes:

Name

Required

Type

Description

value

False

java.lang.String

The value of the parameter.

 

JSTL <sql:param> Example:

The following example takes input from the user and inserts the entered citizen information into the database using the combination of <sql:query> and <sql:param> tags. The list of records are displayed in tabular manner once the new record has been inserted.

<%@ 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"%>
<!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:param&gt; Demo</title>
</head>
<body>
    <h1>&lt;sql:param&gt; Demo</h1>
    <form name="newCitizenForm"
        action="${pageContext.request.contextPath}/tag-types/sql/param.jsp"
        method="POST">
        <table border="0">
            <tr>
                <td>* Enter SSN:</td>
                <td><input type="text" name="ssn" />
            </tr>
            <tr>
                <td>* Enter First Name:</td>
                <td><input type="text" name="firstName" />
            </tr>
            <tr>
                <td>* Enter Last Name:</td>
                <td><input type="text" name="lastName" />
            </tr>
            <tr>
                <td>* Enter Address:</td>
                <td><textarea rows="10" cols="5" name="address"></textarea>
            </tr>
            <tr>
                <td>* Enter 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.firstName 
                                   && not empty param.lastName 
                                   && not empty param.address 
                                   && not empty param.tel}">        
        <sql:update dataSource="${myDS}" var="newCitizen">
            INSERT INTO citizens 
                           (ssn,first_name, last_name, address, telephone) VALUES (?, ?, ?, ?,?)
            <sql:param value="${param.ssn}" />
            <sql:param value="${param.firstName}" />
            <sql:param value="${param.lastName}" />
            <sql:param value="${param.address}" />
            <sql:param value="${param.tel}" />
        </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-param

 

Recommended Usage of JSTL <sql:param> tag:

The <sql:param> tag is used in conjunction with <sql:query> tag to supply any parameters as part of the SQL query to execute. Useful in scenarios to insert data, update data and while executing select query with where conditions.

 

Other JSTL SQL Tags:

dateParam  |  query  |  setDataSource  |  transaction  |  update


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