Describing the JSTL SQL Tags
The JSTL SQL tag library is used to access the relational database used in the JSP pages. The SQL tags are used for the rapid prototyping and developing web applications. The SQL tag libraries can be
accessed in a JSP page by importing the following tag library in the JSP pages.
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql/"
%>
Types of JSTL Tag:
| Tag Name |
Description |
| query |
Executes a query specified in a JSP |
| update |
updates an sql statement |
| param |
specifies a parameter in the sql
statement |
| dateParam |
sets parameter in the sql statement |
| setDataSource |
specifies a data source that is to be
accessed |
The <sql:query> Tag: This Tag is used to
execute the query specified in the sql attribute or in the tag body. Then the
result of the query is set to the variable specified in the var attribute.
Syntax for
<sql:query> tag of JSTL:
<sql:query
attributes>[body content]</sql:query>
E.g:
<sql:query var = "users" dataSource="${dataSource}">
select column_uid,column_pwd,column_accesses,column_firstfrom emp
</sql:query>
The<sql:update>Tag:
The <sql:update> tag executes a sql statement
specified in the sql attribute or in the tag body. The result of the query is
set to the variable specified in the var attribute.
Syntax for
<sql:update> tag of JSTL:
<sql:update attribute>[body content]</sql:update>
E.g:
<sql:update dataSource="${requestScope.dataSource}"
sql="delete from sqltag
where EMPNO=?"var="count"><sql:param
value="${param.EMP
</sql:update>
The <sql:param>Tag: The <sql:param> tag
is used to set a parameter in the sql statement.
Syntax for
<sql:param> tag of JSTL:
<sql:param
attribute>[parameter value]</sql:param>
E.g: <sql:param value="${param.EMPNO}"/>
The<sql:dateParam>Tag: It is used to set
date parameter in sql statement.
Syntax for <sql:dateParam> tag of JSTL:
<sql:dateParam attributes/>
E.g: <sql:dateParam value="<%=DoB%>"
type="DATE" />
The<sql:setDataSource>Tag: This tag
binds a datasource to the specified variable.
Syntax for
<sql:dateParam> tag of JSTL:
<sql:setDataSource attributes/>
E.g:
<sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:forum"/>
Working with Sql Tag:
In the following example, I describe the all the
sql tag of JSTL:
GetEmpDetails.jsp:
<%@ taglib uri="http://java.sun.com/jsp/jstl/core"
prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql"
prefix="sql" %>
<sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:sqltag" scope="request"/>
<sql:query sql="select EMPNO,DEPTNO,ENAME, SAL from sqltag" var="result"
scope="page"
dataSource="${requestScope.dataSource}"/>
<html>
<body bgcolor="cyan">
<table border="1">
<tr>
<c:forEach items="${pageScope.result.columnNames}"
var="colname">
<th><c:out value="${colname}"/></th>
</c:forEach>
<th> </th>
</tr>
<c:forEach items="${pageScope.result.rows}"
var="rows">
<tr><td>
<c:out value="${rows.EMPNO}"/>
</td><td>
<c:out value="${rows.DEPTNO}"/>
</td><td>
<c:out value="${rows.ENAME}"/>
</td><td>
<c:out value="${rows.SAL}"/>
</td><td>
<a href="RemoveEmp.jsp?EMPNO=<c:out value="${rows.EMPNO}"/>">
Remove</a>
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
RemoveEmp.jsp:
<%@ taglib uri="http://java.sun.com/jsp/jstl/core"
prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql"
prefix="sql" %>
<sql:setDataSource var="dataSource" driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:sqltag" scope="request"/>
<sql:update dataSource="${requestScope.dataSource}"
sql="delete from sqltag
where EMPNO=?" var="count">
<sql:param value="${param.EMPNO}"/>
</sql:update>
<c:if test="${count eq 1}">
<b>Employee Removed</b>
</c:if>
<c:if test="${count ne 1}">
<b>Problem in removing Employee</b>
</c:if>
</br>
<a href="GetEmpDetails.jsp">
View Employees
</a>
OUtPUT:
GetEmpDetails.jsp:
![Getemp.gif]()
RemoveEmp.jsp:
![remove.gif]()