Working With SQL JSTL Tag

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:

RemoveEmp.jsp: