JSTL XML Tags
PART-4 - JSTL
&
SQL-TAGS
 Tutorial
Home |
Part
1 | Part 2 | Part
3 | Part 4
In this fourth & last part of the tutorial on JSTL,the
author deals with the 'sql' tags in JSTL and shows how they greatly simplify simple
database operations like 'select' queries. In another demo, common database operations
like 'add','modify' , 'delete' & 'verify'also are dealt with.
The Struts community has ordained that JSP should be strictly a
'view-technology', in the Model-View-Controller Architecture. According to
Struts philosophy, JSP should not deal with Data-Accesss and such data
access should
be done by 'Model' components only.( read 'beans'). JSTL , however, provides for
sql tags, inspired by ColdFusion! ( please see a very short tutorial on
DB-Operations using ColdFusion' available in this issue as a separate lesson.
and compare JSTL code and CF code!).And , a few months back, the editor of 'Java
Lobby' magazine was all admiration for the absolutely nice features of these sql
tags, whatever, 'struts' may say! Just as EJB may be 'overkill', except for
really big Enterprise applications, Struts also may be an unnecessary
complication for small and medium level projects. In such cases, it is much more
direct to provide for data access by the JSP itself, but using JSTL 'sql'
tags.We take up these 'sql' tags in this part of the tutorial.
Let us begin with 'sql.htm'. It
just provides a simple form with just a textarea & submit button. Normally,
queries by MIS department will be very complex and so we have provided a
textarea for the 'select' query.After filling up the query, it is submitted and
the corresponding query.jsp is invoked.
// query.htm
<html>
<body>
<form method=post action="query.jsp">
<textarea name='area1' rows=10
cols=30>
</textarea>
<input type=submit>
</form>
</body>
</html>
------------------------------------
query.jsp is given below. In the standard
jdbc code,we begin by asking for the availability of the driver. "jdbc.odbc.JdbcOdbcDriver". And then, we specify the URL of
the database as 'jdbc:odbc:telephone'.
Similarly, in JSTL also, we begin with
<sql:setDataSource
tag.
It has attributes for 'driver' and
'url'. We will refer to the database as 'db'.
The next step is to collect the query typed in area1 by the
user.
<c:set var="s" value="${param.area1}" />
is used for this purpose.We also check up whether the query
typed by the user has indeed been correctly received.
<c:out value="${s}" />
<br>
Next, the '<sql:query' tag, takes three attributes., such as,
symbolic name:
var="query1"
datasource="${db}
sql="${s}
The query result is then displayed in table form.It should
be possible to follow the code now.In our example, we are having an Access
db, with table1, having two fields, (name, place). registered with ODBC.
// query.jsp
============
<%@ taglib prefix="c"
%>uri="http://java.sun.com/jstl/core"
<%@ taglib prefix="sql" %>
uri="http://java.sun.com/jstl/sql"
<html>
<body>
<sql:setDataSource
var="db"
driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:dbdemo" />
<c:set var='s' value="${param.area1}" />
<c:out value="${s}"
/>
<br>
<sql:query var="query1"
dataSource="${db}"
sql="${s}" />
</sql:query>
<table border="1">
<c:forEach
var="row"
items="${query1.rows}" >
<tr>
<td> <c:out value="${row.name}" /></td>
<td> <c:out value="${row.place}" /></td>
</tr>
</c:forEach>
</table>
</body>
</html>
===============================================
In the second example,(dbeditor.htm & dbeditor.jsp) we
provide a combo, with options such as:
add, modify, remove and verify.
In JSTL , we have a separate sql tag known as
'<sql:update' for
'add', 'modify' and 'remove' operations.
When we want to verify, we must use' <sql:query' tag, because, resultset will be returned.
------------------
dbeditor.htm
<html>
<body bgcolor=lightgreen>
<form method=post action="dbeditor.jsp">
<input type=text name='text1'>name<br>
<input type=text name='text2'>number<br>
<input type=text name='text3'>
criterion<br>
<select name=combo1>
<option value="add">add
<option value="delete">delete
<option value="modify">modify
<option value="verify">find
</select>
<br>
<input type=submit>
</body>
</html>
-------------------------------------------------
dbeditor.jsp
<%@ taglib
prefix="c" uri="http://java.sun.com/jstl/core" %>
<%@ taglib
prefix="c" uri="http://java.sun.com/jstl/sql" %>
<html>
<body>
<sql:setDataSource
var="db"
driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:dbdemo" />
<c:set var='a' value='${param.text1}' />
<c:set var='b'
value='${param.text2}' />
<c:set var='c'
value='${param.text3}' />
<c:set var='d'
value='${param.combo1}' />
----------------------------------------------
<c:if test="${d == 'add'}" >
<sql:update var="query1" dataSource="${db}"
sql="insert into table1 values${a}','${b}')"
>
</sql:update>
<c:out value="record
added"/>
</c:if>
---------------------------------------------
<c:if
test="${d == 'delete'}" >
<sql:update var="query1" dataSource="${db}"
sql="delete from table1 where name='${a}'"
>
</sql:update>
<c:out value="record
deleted"/>
</c:if>
-------------------------------------------
<c:if test="${d == 'modify'}" >
<sql:update var="query1" dataSource="${db}"
sql="update table1 set table1.name='${a}',
table1.place='${b}'
where
table1.name='${c}'"
>
<--sql should be typed in a single line -->
</sql:update>
<c:out value="record modified"/>
</c:if>
------------------------------------------
<c:if test="${d == 'verify'}" >
<sql:query var="query1"
dataSource="${db}"
sql="select * from table1 where name='${a}'"
>
</sql:query>
<table border="1">
<c:forEach var="row"
tems="${query1.rows}" >
<c:set var="n"
value="OK" />
<tr>
<td> <c:out value="${row.name}" /></td>
<td> <c:out value="${row.place}" /></td>
</tr>
</c:forEach>
<c:if test="${n != 'OK'}" >
<c:out value="No such Records" />
</c:if>
</table>
</c:if>
</html>
<body>
====================
These are,
afterall, the
essentials. All else, are only, ornamental. Thus, it will be seen that the JCP
has done a great job in creating really fine standard tags, for simplifying
routine tasks. But, Java coders, have to take note that , their job as JSP coders, will be
slowly eroded
by page-authors using JSTL!
Suggested Reference Books
1) Pro
JSP by
SIMON BROWN &
OTHERS
(A-Press)
(Excellent material)
2) JavaServer Pages by
Hans Bergsten ( Third Edition)
(O'Reilly
Press/SPD)
3) EARLY ADOPTER JSP Standard Tag Library
by
JAYSON FALKNER &
OTHERS
Wrox Press
4) JSTL in Action by
SHAWN BAYERN
(Manning/
DreamTech).
Tutorial
Home |
Part
1 | Part 2 | Part
3 | Part 4
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
